Comparing dates in access

[rantaljo]

I am trying to grab the min and max date during comparisons and then determine the number of 7 day intervals between those periods.

In this case, I added two parameters for the user to specify the 'Begin Date' and 'End Date' when the query runs. If the larger of the 'Begin Date' and a stored date (PermitEffectiveStart) is <= the 'End Date' and the smallest of the 'End Date' and a stored date (PermitEffectiveEnd) is >= [Begin Date], then the calculation is made (otherwise 0 is returned). I keep getting argument errors on MIN and MAX functions and can't seem to make this work.

IIf((MAX([Begin Date],[Permits]![DateEffectiveStart])<=[End Date]) and MIN([End Date],[Permits]![DateEffectiveEnd])>=[Begin Date]), INT(([End Date]-[Begin Date])/7),0)

Example:
DateEffectiveStart=2/13/13
DateEffectiveEnd=null
Begin Date=1/1/14
End Date=3/31/14

The calculation should be completed because both conditions are satisfied --> ([End Date]-[Begin Date]/7)=12
The number of 7-day periods is 12.

Any suggestions on the correct expression?

Julie

[HansV]

You use Min and Max here like the Excel worksheet functions. In Access / SQL, they work differently: Min([MyField]) returns the minimum value of the field MyField over all records, and similarly for Max. You cannot use them to calculate the minimum of maximum of two values.
Assuming that End Date will not be before Begin Date, you should be able to use

IIf([Permits]![DateEffectiveStart]<=[End Date]) And [Permits]![DateEffectiveEnd])=[Begin Date], INT(([End Date]-[Begin Date])/7), 0)

[rantaljo]

Thanks Hans,

I'm trying to get at the number of required inspections for a given permit that need to be completed during the user specified calendar period. The calc you suggest doesn't quite work, because I need the calculation to start/stop before the beginning/end of the user identified period if the permit wasn't effective during any portion of the user-specified range.

I thought maybe I could calculate the beginning and end dates for the calculation based on two previous calcs:

StartDateCalc: IIf([Begin Date]>=[Permits]![DateEffectiveStart],[Begin Date],[Permits]![DateEffectiveStart])
EndDateCalc: IIf([End Date]<=[Permits]![DateEffectiveEnd],[End Date],[Permits]![DateEffectiveEnd])

then,
Required Inspections: ([EndDateCalc]-[StartDateCalc]/7)

However, the first two expressions don't return what I expected. For the first one, I expected the either the user-entered begin date or the Effective Start Date, whichever is later. The query asks for the date, but doesn't display the date.

Any other thoughts on how to get at the number of required inspections during a given calendar period?

Julie

[HansV]

So what does StartDateCalc display?

[HansV]

BTW, the expression for Required Inspections should be:

Required Inspections: Int(([EndDateCalc]-[StartDateCalc])/7)

[rantaljo]

Nothing at all -- see the screen shot of the results when I entered Begin Date=1/1/14 and End Date=3/31/14.

[rantaljo]

No, that attachment was not correct. Here's the correct attachment.

[HansV]

If DateEffectiveStart is Null (blank), the condition [Begin Date]>=[Permits]![DateEffectiveStart] is not True (it evaluates to Null), so IIf([Begin Date]>=[Permits]![DateEffectiveStart],[Begin Date],[Permits]![DateEffectiveStart]) will return DateEffectiveStart, which is Null...

Try

StartDateCalc: IIf([Begin Date]>=[Permits]![DateEffectiveStart] Or [Permits]![DateEffectiveStart] Is Null,[Begin Date],[Permits]![DateEffectiveStart])

EndDateCalc: IIf([End Date]<=[Permits]![DateEffectiveEnd] Or [Permits]![DateEffectiveEnd] Is Null,[End Date],[Permits]![DateEffectiveEnd])

[rantaljo]

That's mostly right. Attached is the new screen shot.

The issues: (1) when the query is run, the user must enter 4 dates: Begin Date, End Date, Begin Date, End Date (2) I have an error in calculating the no of 7 day intervals between the dates using this:

No Reqd Inspections: Int([EndDateCalc]-[StartDateCalc])/7

[HansV]

Try the following:
- With the query open in Design View, click Parameters in the Show/Hide group of the Design tab of the ribbon.
- Enter [Begin Date] in the Parameter column, and select Date/Time in the Data Type column.
- In the next row, enter [End Date] in the Parameter column, and select Date/Time in the Data Type column.
- Click OK.
Now switch to Datasheet View to see if it works better.

[rantaljo]

That was already done. I have one row for each [Begin Date] and [End Date]. On the other question, I think I have the calculation for No Required Inspections figured out. Now just the 4 date thing...

[HansV]

Are you sure that the parameters have been spelled exactly the same wherever they occur? If they have been declared in the Parameters dialog, Access ought to prompt for each one only once.

[rantaljo]

Thanks Hans. I couldn't see the error, but I literally copied and pasted each instance, and that corrected the problem.

Thanks so much for your patience and expertise!

Julie