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

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

Post by YW5uaWUgdG » Fri, 19 Sep 2008 02:55:01


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!
 
 
 

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

Post by fred » Fri, 19 Sep 2008 03:22:27


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

 
 
 

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

Post by YW5uaWUgdG » Fri, 19 Sep 2008 04:29:01

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.
 
 
 

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

Post by fred » Fri, 19 Sep 2008 05:16:34

n Wed, 17 Sep 2008 12:29:01 -0700, annie techwriter wrote:


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.


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