Query on Date position

[tkusina]

I have a table where the date field is stored as yyyy/mm. I want to be
able to query all records that have the previous month in their
record. In other words if it is May 1st, 2007 i want to return all
records that show up in my table that are 200704 (meaning april).

i know how to write the query to return records for the previous month
if the date was in normal format but the odd formatting of yyyy/mm is
causing me some confusion.

thanks

 

[Duane Hookom]

200704 is not in the format yyyy/mm. If the date field is numeric, you should
be able to search with a criteria of:

Year(DateAdd("m",-1,[DateField])) * 100 + Month(DateAdd("m",-1,[DateField]))

 

[Jerry Whittle]

First thing first: If it's a Date datatype field, your dates are not stored
as yyyy/mm. It's actually stored as a number in Access. For example today at
noon is actually stored as: 39218.5 .

Your date is formatted to display as yyyy/mm. This is very important as your
date field might be actually storing the day and time as well. This can mess
up your calculations. Try running a query such as this to see the actual date
values and not just what is displayed:

SELECT Format([Your Date Field], "General Date")
FROM YourTable;

To cover all eventualities (except it not being an actual date field), I'd
use something like below for the criteria:

Between DateAdd("m",-1, CDate(Year([Your Date Field]) &"/" & Month([Your
Date Field]))) and CDate(Year([Your Date Field]) &"/" & Month([Your Date
Field])) - .00001)