UtterAccess.com
We have detected that you are using an unsupported web browser. We recommend you to upgrade your web browser to get the most from UtterAccess forums.
Why should and how can I upgrade?
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

2 Pages V   1 2 >  
Reply to this topicStart new topic
> Change One Field Based Upon Another, Office 2010    
 
   
TheWookie
post Apr 26 2012, 05:51 AM
Post #1

UtterAccess Addict
Posts: 102
From: Birmingham, UK



Hi all,
I have an Access 2010 Table which contains about 20 fields, mostly text data type. One of the fields has the heading "Status", and the default value for this is "Open". There is another heading called "Issue Closer" which by default is blank. What I would like is when somebody enters data into the "Issue Closer" Field it automatically changes the "Status" field from "Open" to "Closed"
The data entered into "Issue Closer" will be a name but it doesnt have to be specific, e.g. if "Issue closer" is null then "Status" = "Open" and if "Issue" closer has a text string in it then "Status" = "Closed"
Can anyone advise how I should go about this, or even if it's possible? I read a bit about calculated fields but that seemed more to do with adding up values and performing mathmatical functions.
Go to the top of the page
 
+
Bob G
post Apr 26 2012, 05:53 AM
Post #2

UtterAccess VIP
Posts: 10,461
From: CT



how are your users updated that field? from within a form ?
Go to the top of the page
 
+
BruceM
post Apr 26 2012, 06:48 AM
Post #3

UtterAccess VIP
Posts: 3,572
From: Downeast Maine



If the Field1 value is entirely dependent on the Field2 value, Field1 is redundant. A calculated field in a query can provide the necessary Open or Closed value.
difficulty with one field's value depending on another field's value is that it is difficult to maintain, especially if you import data or otherwise enter data by means other than the user interface (e.g. an Update or Insert query).
Bob already asked about another important point.
Go to the top of the page
 
+
TheWookie
post Apr 26 2012, 06:49 AM
Post #4

UtterAccess Addict
Posts: 102
From: Birmingham, UK



Sort of, the database is split and users are entering data via a FE form, thats how the record is started, and but the "Issue Closer" field is entered via a query running within another seperate form and it is only used by administrators such as myself.
The "Status" value is entirely dependent upon the "Issue Closer" value.
I will need to import data from Excel to begin with but once it's running I will not need to import further data.
"Status" value is a default value, nobody will be entering data into this value from a UI and I can automate it they wont need to do it from a query either.
Go to the top of the page
 
+
BruceM
post Apr 26 2012, 07:16 AM
Post #5

UtterAccess VIP
Posts: 3,572
From: Downeast Maine



Then "Status" needs to be a calculated field in a query (or it could be an epression in a control, but the query option is probably the most efficient).
It should not be in the table at all, based on what you have described.
Go to the top of the page
 
+
icemonster
post Apr 26 2012, 11:29 AM
Post #6

UtterAccess Guru
Posts: 598



Use a query for this but if your a stickler for data inserted (though not recommended since what you describe is best as a calculated query) use an insert statement with a where clause based on the record in tblA
Go to the top of the page
 
+
BruceM
post Apr 26 2012, 11:45 AM
Post #7

UtterAccess VIP
Posts: 3,572
From: Downeast Maine



The trouble with writing to a field whose value is entirely dependent on another field is not so much the initial data insertion as when the value of the "parent" field is changed over the lifetime of the record. Every possible scenario must be accounted for, to no overall advantage.
Go to the top of the page
 
+
TheWookie
post Apr 27 2012, 07:18 AM
Post #8

UtterAccess Addict
Posts: 102
From: Birmingham, UK



Sounds a lot simpler to leave it alone and hope people remember to change it manually.
would have thought it would be a simple thing to put in a table, if text appears in this box then then change the value of the other one.
Odont understand quite the relationship between query and table? The data I'm writing is stored in a table, and a query is like a search?, you run a query like you run a search and it displays the data that your looking for. The status field (Open or Closed) is part of the data I want to store so it should be in the table shouldnt it, but you say it should be in a query, which is a kinda search tool? how can I save data to a search?
One of the critera for my query is the Open/Closed status field, I run a query to show all the records with status set to Open, and another one for all those with status set to closed, so the Open/Close data should be in the table in order to be able to run the query?
I'm a bit lost I'm afraid.
Go to the top of the page
 
+
PaulBrand
post Apr 27 2012, 07:24 AM
Post #9

UtterAccess Ruler
Posts: 1,594
From: Oxford UK



I think you could use this syntax in a form, using the control AfterUpdate event:
!--c1-->
CODE
If CtlName <> "" or Not IsNull(CtlName) Then OtherCtl = "Closed"
End If
Go to the top of the page
 
+
BruceM
post Apr 27 2012, 10:00 AM
Post #10

UtterAccess VIP
Posts: 3,572
From: Downeast Maine



Sounds worrisome to me.
It's a simple enough thing to put in a form, but a table should be for storage and some related business such as requiring data in a field and other validation tasks. But the point several of us have been trying to make is why you would want to store data that is entirely dependent on another field. If you store date of birth in a table you can either calculate a person's age, or store their age and manually update the records from time to time. I expect you would opt for the former. This is no different.
A table is for storage. A query is a view of the data, and can also be used to perform calculations. For instance, perhaps in a People table you store FirstName and LastName in separate fields. There is no need also to store FullName. In a query you can add a field that is not in the table:
FullName: [FirstName] & " " & [LastName]
You could also do:
LastFirst: [LastName] & ", " & [FirstName]
If there is a MiddleInitial field you can add that, or not, as needed. You have a lot of flexibility. Another advantage is that you can sort data by LastName, then FirstName, while displaying FullName or LastFirst. This can be useful when selecting names from a drop-down box, for instance.
A query can also be used to limit the records. Perhaps you want to see just the records for the past month or the past year (assuming there is a date field in the records). Criteria can be combined, so that you find all records
FOr you can combine records from several tables. This is especially helpful for a report, where users can only view the data. Some queries, because of the nature of how tables are joined or records are filtered, do not permit data entry, so queries used as the basis for interactive forms must take this into account.
In any case, just as you can base a form or report on a table, you can base it on a query that is based on the table. Unlike a table, the query can be used to limit the number of records according to criteria of your choice, sort records, calculate dates, designate Status as Open or Closed, or whatever you need.
Go to the top of the page
 
+
icemonster
post Apr 27 2012, 10:04 AM
Post #11

UtterAccess Guru
Posts: 598



well i guess the problem you're facing right now is you don't have enough knowledge with ms access. but that's what we are here for to guide to properly. what you really like to do is set a query for it and have that query be the record source for the table. basically it's saying like this
If tbla field 1 is 5 then tblb field 2 is 6. the 6 in part would better be a calculated field but if you have the need to store it then you'd need to go into vba for that.
Go to the top of the page
 
+
TheWookie
post Apr 30 2012, 07:32 AM
Post #12

UtterAccess Addict
Posts: 102
From: Birmingham, UK



It is worrisome to leave it and hope people remember.
o in my table the "Status" field is "open" by default and, at the moment, manually changed to "Closed" when data is entered into the "Closer" field. What you suggest is that in a query I can calculate the "Status" (age) field based upon the "Closer" (date of birth) field. So if there's data in the "Closer" field then "Status" would be calculated as "Closed"
My project is a technical Issue reporting and tracking system, the idea is for it to work like this
1)User fills out a form to raise a new issue, the form generates a ref number, emails the details to the tech support team, and enters the issue details into the table, at this point the issue is considered "Open"
2)Somebody from tech support team (5 people) deals with the issue
3)Once the issue is dealt with we close it off. Currently there are two saved queries, one of which performs a query for records with an "Open" status, tech support member then manually edits that record to add in the resolution details and the "Closer" field (Then remembering to change the status from open to closed)
Presumably instead of running a query for records with an "Open" status I could run one for records with no data in the "Closer" field (since it's the same thing) and then calculate the "Status" field.
I think I'm getting it, so your right I wouldnt actually need a status field at all in my table since the ony way we view the table is via a query.
Go to the top of the page
 
+
BruceM
post Apr 30 2012, 08:21 AM
Post #13

UtterAccess VIP
Posts: 3,572
From: Downeast Maine



Exactly!
ou've got it.
No status field. Just use the calculated field as the control source for a text box in a form or report.
One thing about which you need to take a little care is how you define no data. Null ("unknown", essentially) is not the same as a zero-length string (""), so to cover all contingencies you could check the length of the field:
Status: IIf(Len([Closer] & "") = 0,"Open","Closed")
By combining (concatenating) the [Closer] field value with a zero-length string, you will get the correct result whether [Closer] is null or a zero-length string (you can't tell just by looking, since it is blank in either case).
I have shown this as a calculated query field. Bind your form or report to the query, and select [Status] as the Control Source for a text box on a form or report.
Go to the top of the page
 
+
TheWookie
post May 1 2012, 03:41 AM
Post #14

UtterAccess Addict
Posts: 102
From: Birmingham, UK



Ok thanks for the info, it does seem a better way of doing it. Will have a try at your suggestion.
seful tip about defining null data, I have one or two statements in my project regarding null value, will have to double check they are checking the string length too.
Go to the top of the page
 
+
BruceM
post May 1 2012, 06:26 AM
Post #15

UtterAccess VIP
Posts: 3,572
From: Downeast Maine



It's not always necessary to do this, but it is a good idea when you are uncertain about the nature of a blank field, or when a zero-length string (ZLS) is possible. For instance, a text field in an Access table has an "Allow Zero Length" property. I tend to set that to No unless there is a (rare) specific reason to allow a ZLS. Also, some functions (Format, for instance) return text, which in some cases can lead to unexpected results if you are checking for Null.
It is also worth noting that 0 is neither Null nor a ZLS.
Another tool for handling Null is the Nz function. If the value passed to the function is Null, Nz returns an alternative value.
Nz([YourField],"") returns a ZLS if [YourField] is null. So you could have:
CODE
If Nz([YourField,"") = "" Then
  '  Do something
  Else
  '  Do something else
End If

Both Nz and testing for length have their place. I'm afraid I can't come up with a specific instance of when to use one or the other, although testing for Len is probably the preferred choice when testing several values at once.
Hope I haven't muddied the waters. Here's some more information about Null, which is a very useful but somewhat tricky concept:
http://allenbrowne.com/vba-NothingEmpty.html
Go to the top of the page
 
+
TheWookie
post May 1 2012, 06:32 AM
Post #16

UtterAccess Addict
Posts: 102
From: Birmingham, UK



That works a treat thank you <
would like some advice on how to modify the expression though for another query on a different table.
It has a "Status" field just the same, and it has a "Closer" field just the same, however it does not have simple Open/Closed states, for the Closed state I have modified the expression as follows:
Status: IIf(Len([Closer] & "")=0,"Open","Goods Returned / Job Completed")
This works fine for closed issues, if there is some data in the "Closer" field it calculates the "Status" field as "Goods Returned / Job Completed" not a problem, the "Closer" field is only ever populated when the issue is in a "Goods Returned / Job Completed" state.
For an Open state however it's a bit trickier, there are 3 open states lets say "Requirment Identified", "Request Submitted", "Approval Notification Recieved"
The user enters the status into the table via a form, they might initially enter Requirment Identified, but as the issue progresses it might change to "Request Submitted" or "Approval Notification". This change would currently be achieved by manually editing the Table via a query.
It would be great if it could be automated the same way. I've been trying to think what these status's relate to and depend upon:-
"Requirment Identified" status requires data to be entered into "Summary" field
"Request Submitted" status requires data to be entered in "Ref No."
"Approval Notification Recieved" status requires data to be entered into either "PO No." or "Date Approved" field
It's also possible for data to exist in all 3 of those fields simultaneously, but they definitly have an order of precedence, i.e. "Requirment Identified" Status could be overruled by "Request Submitted" status if data is entered into "Ref No." field.
Sorry it's so complicated, If it was just one of those things I could modify the expression, but I'm not sure how to modify it for multiple things or even if it's possible. If I can get it to work I can dispense with the user entering the status at all.
Go to the top of the page
 
+
BruceM
post May 1 2012, 06:52 AM
Post #17

UtterAccess VIP
Posts: 3,572
From: Downeast Maine



Is there a single field that contains one of these three phrases? If so, what is the field name, and are there any other possible values for that field?
Go to the top of the page
 
+
TheWookie
post May 1 2012, 07:09 AM
Post #18

UtterAccess Addict
Posts: 102
From: Birmingham, UK



The only field that contains these phrases is the "Status" field
The only other possible value for that field is "Goods Returned / Job Completed" which is only ever displayed when "Closer" has data entered into it.
So "Status" field can have 3 phrases for an open state, and 1 phrase for a closed state, but it actually uses the phrases as a status rather than simply open closed.
Go to the top of the page
 
+
BruceM
post May 1 2012, 07:34 AM
Post #19

UtterAccess VIP
Posts: 3,572
From: Downeast Maine



It sounds like this is a case where Status is not entirely dependent on other fields. That is, the Status is a value in its own right rather than a value derived from other values. Is that correct?
Go to the top of the page
 
+
TheWookie
post May 1 2012, 08:26 AM
Post #20

UtterAccess Addict
Posts: 102
From: Birmingham, UK



Status "Closed" or "Goods Returned / Job Completed" in this case, is entirely dependent upon the "Closer" field, same as before.
tatus "Open" or one of the 3 phrases in this case, is a bit more of a grey area.
"Request Submitted" will only ever be a set as "Status" if data is entered into "Ref No." Field. So you could say that it is dependent upon that field.
"Approval Notification recieved" will only ever be set as status if there is data in "PO No." or "Date Approved" field. So you could say that this is also dependant upon those fields.
"Requirement identified" Will only be set as "Status" if there is data in "Summary" field, however if there is also data in the "Ref No.", "PO No." or "Date Approved" field then "Request Submitted" or "Approval Notification recieved" will be set as the Status. This is the complicated part I think.
I'm not sure if it would happen that there is a "Ref No." and a "PO No." together. Need to account for that possibility though.
Go to the top of the page
 
+

2 Pages V   1 2 >
Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 



RSS Search  ·  Go to Top  ·  Lo-Fi Version Time is now: 31st October 2014 - 07:22 PM

Tag cloud: