Windows 7 - expression used in form field won't display in query or table

Asked By annietechwrite on 17-Sep-08 01:55 PM
I am using an if/then expression in a form. The expression is entered in the
field property's Control Source.

When I open the query or table that is associated with the form, all
information (including fields that use simple expressions) displays except
for that field, which is blank.

I'm lost - Do I need to use the expression in a difference way or in a
different place?

Thank you!




fredg replied on 17-Sep-08 02:22 PM
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.


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
annietechwrite replied on 17-Sep-08 03:29 PM
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 replied on 17-Sep-08 04:16 PM
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
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.


--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail