Reports. If then question...

[Paulc]

I am attempting to create report based on data from a Access 2002 Query.
I have a Textbox in the report which is populated from the expression
[A1Date] - [P1date], giving me an integer for an answer.

The two date fields exist in a table in my database and are either populated
with a date or blank.

I would like the Textbox in my report, when run, to show a result depending
on the outcome of the following:

If [A1Date] - [P1date] = 1 then Textbox= 1 day
If [A1Date] - [P1date] = 2 (or greater) then Textbox= 1 days
If [A1Date] - [P1date] = 0 or Blank then Textbox = "No date"
If [A1Date] - [P1date] = <-1 then Textbox= Date Met"


Assuming its possible, I would appreciate guidance with a solution.

Many thanks,

Paul.

 

[Marshall Barton]

I am attempting to create report based on data from a Access 2002 Query.
I have a Textbox in the report which is populated from the expression
[A1Date] - [P1date], giving me an integer for an answer.

The two date fields exist in a table in my database and are either populated
with a date or blank.

I would like the Textbox in my report, when run, to show a result depending
on the outcome of the following:

If [A1Date] - [P1date] = 1 then Textbox= 1 day
If [A1Date] - [P1date] = 2 (or greater) then Textbox= 1 days
If [A1Date] - [P1date] = 0 or Blank then Textbox = "No date"
If [A1Date] - [P1date] = <-1 then Textbox= Date Met"

First, the difference of two date/time fields is not
necessarily an integer. You should use the DateDiff
function to do arithmetic on date/time values.

Let's say your text box is named txtDays and has the
expression:

=DateDiff("d", P1date, A1date)

Then make that text box invisibe and add another text box
using an expression along these lines:

=Switch(txtDays<0,"Date Met", Nz(txtDays,0)=0,"No Date",
txtDays=1,"1 day", True,txtDays & "Days")