Month Parameter Query

[SteveE]

I am running report on all activity in a given Month, currently I use From and To dates as the parameters but is there a way of selecting a whole month in a parameter without entering the from and to dates i.e. [March] instead of Between [From Date] (1/3/04) And [To Date] (31/3/04)

any help please.

 

[Mile-O]

But then you would be neglecting the year and get the records for March for all years.

 

[SteveE]

thanks for your reply
At present I only use the first part of dates i.e 1/3 to 31/3 which returns only the current years data I know at changes in year we use i.e 12/12/03 - 12/1/04 to avoid issues.
thanks

 

[Mike375]

thanks for your reply
At present I only use the first part of dates i.e 1/3 to 31/3 which returns only the current years data I know at changes in year we use i.e 12/12/03 - 12/1/04 to avoid issues.
thanks

Here is one thing you can do, but it will only work for the next 5 years

The Year and Month functions will give you the year as in 2004 and the month as in 5 etc. If you use the Mid function you can put the last number from the year into a field. You then join them so for this year you get 14 for Jan, 64 for June and 124 for December and for last year it would be 13, 63 and 123.

 

[SteveE]

Ok thanks, I though my ignorance I might be missing a simple existing function but I can see from your answers probably not!
thanks
Have a good day

 

[Mile-O]

If it's only for one month of the current year:

Between DateSerial(Year(Date()),[Enter month],1) And DateAdd("d",-1,DateAdd("m",1,DateSerial(Year(Date()),[Enter month],1)))

 

[Mile-O]

Or, this SQL is another example:

SELECT DateField
FROM MyTable
WHERE DateField Between DateSerial(Year(Date()), 1, 1) And DateSerial(Year(Date()), 12, 31)
AND Month(DateField) = [Enter month]
ORDER BY DateField;

 

[SteveE]

Thats great and works a treat
many thanks