Date Query Criteria

[mellis]

Hi there,

I have a query that returns rows a row of data everyday for around 500 clients.

The first field is the extract date. As the data is monthly cummilative I would like to set a criteria for the last day of the month...what ever month or year that may be in.

Is there a simple criteria I can enter rather than inputting about a million dates.

Thanks

 

[plog]

Can you demonstrate what you want with data? Include table and field names.

 

[MarkK]

Check out this formula, which always returns the last day of the month . . .

DateSerial(Year(Date()), Month(Date()) + 1, 0)

 

[mellis]

Thanks Mark,

Where should I be putting this formula?

I put it under criteria, but I dont believe I am correct in doing so as it filters out all my data.

I've attached what I've done on screen shot.

Michael

 

[MarkK]

If you put it in the criteria for a date field, it will only return rows where the date in that field is equal to the last day of this month. So if your date field includes time, for instance, you'll never get equality (except for a time of midnight exactly).

But where you put it depends on what you need to do.

It's common with dates to filter on a range, so something like . . . .

WHERE SomeDate >= #1/1/2015# AND SomeDate <= #1/31/2015#