Link to home
Create AccountLog in
Avatar of solarisinfosys
solarisinfosys

asked on

date expression in MS Access 2003

I want to use date functon to replace this hardcoded expression:

between #10/1/2012# And #11/1/2012#

basically I want to select transactions from the previous month
Avatar of IrogSinta
IrogSinta
Flag of United States of America image

Try this:
Between DateSerial(Year(Date()),Month(Date())-1,1) AND DateSerial(Year(Date()),Month(Date()),0)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Chris B
Chris B
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
The sample that IrogSinta provides will work if your date field does not include time data, as it will generate:

Between #10/1/12# AND #10/31/12#

But if your data contains time values, then any time after midnight on Oct 30th will not be included in the results of the query.  To ensure that you have all the results you are looking for, I prefer to use a  >= and < construct, similar to:

[DateField] >= DateSerial(Year(Date()),Month(Date())-1,1) AND
[DateField] < DateSerial(Year(Date()),Month(Date()),1)

This will work whether or not you have time values in your date field.
Avatar of solarisinfosys
solarisinfosys

ASKER

Chris B: Thank you very much. short simple and worked like a charm
Thanks. One issue however, it will get confused in January. Use this criteria instead -
IIf(Month(Date())=1,12,Month(Date())-1)
However, if you have multiple years worth of data in your database, just creating a computed Month column will not be sufficient, you will also have to include a computed year column.  You are far better off using a where Clause as either Irog or I proposed.

Among other things, the computed column cannot be indexed, so searches or filters on that field will be slower than ones conducted on an indexed column.