Interesting Date Problem

[Doc94]

I have an interesting problem in trying to get report dates. I have a query to report a count of certain records. a report is linked to this query that is run each day and gives the total by day starting at the begining of the month through the current day. I t is run each moring for the previous day. I use the follwing as criteria in the date field to return the appropriate records.
Between DateSerial(Year(Date()),Month(Date()),1) And (Date()+1)
This works fine for everyday but the first day of the month which should be returning records for the 1st through the last day of the previous month.
Any thoughts out there on an answer??

 

[MichaelRed]

See DateSerial via the ubiquitous {F2} (a.k.a. HELP).

Isolate the various parts of the date.

If the Day is the 1st of the month Then

use month - 1 as the month,
1 as the start and
the last day of the (previous) month as the end

else
Use the current month
1 as the startdate and the current date as the last date



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[TonyJollans]

Hi Doc94,

You always want the first day of the year and month of yesterday, so use it ..

Between DateSerial(Year(Date()

-1

),Month(Date()

-1

),1) And (Date()+1)

Enjoy,
Tony

 

[Doc94]

Tony-
Fascinatingly logical.
Thanks