I must be using the word 'field' incorrectly. Hopefully I can clarify.
The expression I entered in the Closed Date's control source is
=IIf([Status]="Closed",Date()," ")
Basically, if the status of an issue is changed to 'closed' the current date
displays, if it is anything else, nothing displays.
I understand that I may not want the calculation to be stored in a table,
but I do need it to display in Queries and Reports.
Is there a way to do this?
Thank you for your help.
fredg said:
It would have been helpful had you posted the actual calculation so my
reply could have been specific instead of general.
Forms do NOT contain fields. Tables contain fields, forms contain
controls. This distinction might help clarify your thinking regarding
forms and tables.
To explain why your expression result is not being saved to your
table, as the control source of this form control is an expression
(and not the name of a field) how would Access know which field to
store the data in.
As a general rule, you do NOT store calculated data in any table.
As long as you have the fields that make up the calculation stored in
your table, any time you need the result, simply re-calculate it as
you are doing now. This assures that the result of the calculation is
always correct and up-to-date. What would happen to your calculated
data if you discovered an error in one of the previously entered
fields and changed it? You now have an incorrect calculated data
stored.
Yes, it can be displayed in a reoort or query.
The problem though is with [Status].
Is it indeed a Text datatype field and the word "Closed" is stored in
the table field (that is the report's record source)?
Or is it a look up field that displays the word "Closed" but stores a
Number value?
Or is it a Check Box Yes/No field?
If [Status] is a Text field.....
In an unbound text control on your report:
=IIf([Status]="Closed",Date(),"")
If the [Status] field is a Text datatype then, if the value is
"Closed" the current date will display, otherwise the control will be
blank.
If [Status] is a lookup field, then you need to know the number value
that equates to "Closed". Let's assume it's 3.
=IIf([Status] = 3, Date(),"")
If [Status] is a Yes/No check box, then
=IIf([Status] = -1,Date(),"")
The above assumes that [Status] is either -1 (for Closed) or 0 (for
Not Closed or whatever)
You would use similar expressions in a query (without the = sign)
i.e.
ClosedDate:IIf([Status] = "Closed",Date(),"")
etc.
The used the [ClosedDate] field in the report.
