Update form field from query

[Guest]

I have a form field that I want to be a calculated value. The form field in
which the calculated value is to be stored is "Days Open". I have a
beginning date stored in a table & have tried several ways of getting the
data to display. I have the query to calculate the value working, but can't
get the data to display in the field. I have created a macro that can be
run, but that won't show either. I always get either a blank field (no
value) or a #Name? error.

Here is the basic formula I used in the query that works (gives me the
correct value) when the query is run by itself:
DaysOpen:
Nz(CorrectiveActionTable!PermanentDate,Date())-PPR_TrackingTable!DateIssued

TIA
Ruth

 

[John Vinson]

I have a form field that I want to be a calculated value. The form field in
which the calculated value is to be stored is "Days Open". I have a
beginning date stored in a table & have tried several ways of getting the
data to display. I have the query to calculate the value working, but can't
get the data to display in the field. I have created a macro that can be
run, but that won't show either. I always get either a blank field (no
value) or a #Name? error.

Here is the basic formula I used in the query that works (gives me the
correct value) when the query is run by itself:
DaysOpen:
Nz(CorrectiveActionTable!PermanentDate,Date())-PPR_TrackingTable!DateIssued

TIA
Ruth

You're making it much harder than it needs to be!

Simply set the Control Source property of a textbox on the form to

=Nz(CorrectiveActionTable!PermanentDate,Date())-PPR_TrackingTable!DateIssued

or, better, use the DateDiff function:

=DateDiff("d", [DateIssued], NZ([PermanentDate], Date())

If you're trying to store DaysOpen in a Table somewhere... don't. It's
redundnant, since it can be recalculated; and worse, if PermanentDate
is NULL, its value will change every day anyhow.

John W. Vinson[MVP]

 

[Guest]

When I put either of these in the control source property (which is one of
the things I tried yesterday), I get the error #Name?. This is driving me
nuts... <g> I suspect that I may have done something when I set the form up,
but can't figure out what I have done that gives me this error. I know the
formula is right because it works in a query... just not in the control
source properties. I even set up a macro to run the query that works & used
it in the 'before update' event... same result.

As for storing the data that is calculated, I hadn't planned on it. This
field can be printed on a report if someone needs the information. I don't
see the sense in storing a value that can be re-calculated when it is needed
- that's what queries are for.

TIA

I have a form field that I want to be a calculated value. The form field in
which the calculated value is to be stored is "Days Open". I have a
beginning date stored in a table & have tried several ways of getting the
data to display. I have the query to calculate the value working, but can't
get the data to display in the field. I have created a macro that can be
run, but that won't show either. I always get either a blank field (no
value) or a #Name? error.

Here is the basic formula I used in the query that works (gives me the
correct value) when the query is run by itself:
DaysOpen:
Nz(CorrectiveActionTable!PermanentDate,Date())-PPR_TrackingTable!DateIssued

TIA
Ruth

You're making it much harder than it needs to be!

Simply set the Control Source property of a textbox on the form to

=Nz(CorrectiveActionTable!PermanentDate,Date())-PPR_TrackingTable!DateIssued

or, better, use the DateDiff function:

=DateDiff("d", [DateIssued], NZ([PermanentDate], Date())

If you're trying to store DaysOpen in a Table somewhere... don't. It's
redundnant, since it can be recalculated; and worse, if PermanentDate
is NULL, its value will change every day anyhow.

John W. Vinson[MVP]

 

[John Vinson]

When I put either of these in the control source property (which is one of
the things I tried yesterday), I get the error #Name?.

#Name? means that you are including some fieldname which is not
included in the Form's Recordsource query, or is otherwise
unrecognizable.

What IS the Recordsource of this form?
Does it include all of the fields you're using in the calculated
expression?

John W. Vinson[MVP]

 

[Guest]

The 2 values taken from a table come from 2 different tables -
The corrective action table has the final completed date
The PPR tracking table contains the date of the customer complaint.

Do I maybe need to combine those 2 tables?

 

[Guest]

I finally fixed this issue by combining the 2 tables (PPR-Customer Concerns
and Corrective Action Tracking). Once I combined the 2 tables and set the
PPR# field to 'not required' status, the formula worked like a charm. I
really appreciate all of the efforts to assist me - it helped me think the
process through more completely.

Thanks again,
Ruth