Expression Builder

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a formula in an old Approach Database that I need to set as the
control source to an unbound field in Access on a report. How would I
convert it? Here's the formula:

If("Total # of Days" = 'N/A' and "Closed Date" > '', 'OK', If("Total # of
Days" = 'N/A' and "Closed Date" = '', 'N/A', If("Total # of Days" > '45',
'LATE', 'OK')))

Information:
1) "Total # of Days" and "Closed Date" are both fields in Access (renamed
without spaces)!
2) ='' means the field is empty
3) >'' means the field is not empty

Please HELP!!
 
Is the field [Total # of Days] text or numeric? Your expression compares the
field to "N/A" and later to "45".

You might be able to use something like:
=IIf(IsNull([Total # of Days]) and Not IsNull([Closed Date]), "OK",
IIf([Total # of Days] = "N/A" and IsNull([Closed Date]), "N/A", IIf([Total #
of Days] > "45", "LATE", "OK")))

Watch for line wrapping.
 
Thanks, worked like a charm!

Duane Hookom said:
Is the field [Total # of Days] text or numeric? Your expression compares the
field to "N/A" and later to "45".

You might be able to use something like:
=IIf(IsNull([Total # of Days]) and Not IsNull([Closed Date]), "OK",
IIf([Total # of Days] = "N/A" and IsNull([Closed Date]), "N/A", IIf([Total #
of Days] > "45", "LATE", "OK")))

Watch for line wrapping.
--
Duane Hookom
MS Access MVP


theitman said:
I have a formula in an old Approach Database that I need to set as the
control source to an unbound field in Access on a report. How would I
convert it? Here's the formula:

If("Total # of Days" = 'N/A' and "Closed Date" > '', 'OK', If("Total # of
Days" = 'N/A' and "Closed Date" = '', 'N/A', If("Total # of Days" > '45',
'LATE', 'OK')))

Information:
1) "Total # of Days" and "Closed Date" are both fields in Access
(renamed
without spaces)!
2) ='' means the field is empty
3) >'' means the field is not empty

Please HELP!!
Click to expand...
Click to expand...
 
Back
Top