Pulling the number of days in a given month from a date range 1

[beverlee]

Seasons Greetings All!-

I need to pull the number of days in any given month from a date range. The start of the date range is a field called [hookupdate] and the end of the date range is a field called [enddate]. I need to be able to tell how many days in that range fall within any given month, but don't want to base it on the current date. The users may need to go back and pull historical data for reports and so I want them to be able to enter the [Begin Date] and [End Date] as parameters for whatever month they are looking for. I tried this query but it doesn't like it.

SELECT tblEM.HookupDate, tblEM.EndDate, IIf([hookupdate]<[Begin Date] And [enddate]>[End Date],[End Date]-[Begin Date],"Test") AS DaysServed
FROM tblEM;


It works up until I ask it to subtract the two parameter values from eachother. Any way to assign those to variables or something like that?

Thanks for your time!

 

[SkipVought]

Hi,

I think your problem is with the AND in your IIF.

You'll have to use nested IIFs, cuz, there's neither an AND nor is there an OR function in Access.

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[PHV]

there's neither an AND nor is there an OR function in Access
But there is an AND and an OR operator ...

 

[beverlee]

The And is functioning correctly, I just tested it. The part that it is not liking is subtracting the two parameter values. Any other ideas?

 

[PHV]

You may try something like this
PARAMETERS [Begin Date] DateTime, [End Date] DateTime;
SELECT HookupDate, EndDate, IIf(EndDate>[End Date],[End Date],EndDate)-IIf(HookupDate<[Begin Date],[Begin Date],HookupDate) AS DaysServed
FROM tblEM
WHERE HookupDate<[End Date] AND EndDate>[Begin Date];

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

 

[SkipVought]

Egg on my face

Skip,
[sub]
[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine![/sub]

 

[beverlee]

Thanks PHV! That suggestion worked like a charm!

Merry Christmas and Happy New Year! Have a Star!