Criteria or Filter Issue

[MichaelW]

Hi,



I am struggling a bit here so let me try and explain!

I have two date columns in my table and query (Rec Date and Hasten Date).

To begin, I had two simple expressions in my query:

Days Early: [Rec Date]-[Hasten Date]

Days Late: [Rec Date]-[Hasten Date]

The query runs fine but obviously I have both sets of numbers (late and early) in my output. I tried adding criteria for "<=0" in Days Early and ">0" in Days Late but still have issues.

So basically what I need is to display only numbers greater than "0" in my Days Late column and numbers less than or equal to "0" in my Days Early column.

Am I going about this the right way or should I try something different?

Thanks in advance!

[jzwp11]

You will need to nest the date expressions within an IIF() function. I also recommend using the datediff() function rather than subtracting two date fields.

Datediff("d", laterdate, soonerdate)

For days early to be <=0, Hasten date must be greater than or equal to the rec date, if that is true return the number of days different:

Days Early: IIF([Hasten Date]>=[Rec Date], datediff("d",[Hasten Date],[Rec Date]), NULL)

Therefore days late would be as follows (just swap the true and false parts of the IIF())

Days Early: IIF([Hasten Date]>=[Rec Date],NULL, datediff("d",[Hasten Date],[Rec Date]))