How do I use calculated value from form in query?

[03SCBeast]

tbxFirstAidStatus located on frmManagers (underlying tblManagers).

Ideally, I would like to bound the textbox above to a field so I could use it in reports in queries. Is there a way to do this even though the control source is a calculated value. I can do the above with no problem if I do a query based on the underlying table, base a form on the query, and do an expression based with the code below but I'm trying to cut down on any unnecessary queries.

My code for tbxFirstAidStatus: Control Source= IIf(DateDiff("d",Now(),[ASW_FirstAidExp])>30,"Current",IIf(DateDiff("d",Now(),[ASW_FirstAidExp]) Between 0 And 30,"Due",IIf(DateDiff("d",Now(),[ASW_FirstAidExp])<0,"Expired","")))

 

[PHV]

Usually it's a bad idea to store derived/calculated value.
Furthermore tbxFirstAidStatus may change depending current date !
You may simplify your formula:
IIf(DateDiff("d",Now(),[ASW_FirstAidExp])>30,"Current",IIf(DateDiff("d",Now(),[ASW_FirstAidExp]) Between 0 And 30,"Due","Expired"))

You may use it in a query too.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[03SCBeast]

Thanks PHV, I forgot about the principle of not storing a calculated value in a table and I appreciate your simplified formula.

 

[PHV]

Even simpler:
IIf(DateDiff("d",Now(),[ASW_FirstAidExp])>30,"Current",IIf(DateDiff("d",Now(),[ASW_FirstAidExp])<0,"Expired","Due"))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244