Date question

[Dustin B]

I have reports that run that contain more info that I want to use. One of
the functions I am trying to accomplish with Access is to limit the data
using a date filter. For some odd reason I can't figure this part out. I
want my query to pull only the information for the previous month. So if we
are in April I want it to show me everything from March 1 to March 31st. If
in May I want April 1 to April 30th.
The name of my field is TenderDate in a linked table called XXX_XXXX_013207.
Thank you in advance for your help.

 

[Rick Brandt]

I have reports that run that contain more info that I want to use.
One of the functions I am trying to accomplish with Access is to
limit the data using a date filter. For some odd reason I can't
figure this part out. I want my query to pull only the information
for the previous month. So if we are in April I want it to show me
everything from March 1 to March 31st. If in May I want April 1 to
April 30th.
The name of my field is TenderDate in a linked table called
XXX_XXXX_013207. Thank you in advance for your help.

SELECT *
FROM TableName
WHERE TenderDate >= DateSerial(Year(Date()), Month(Date()), 1)
AND TenderDate < DateSerial(Year(Date()), Month(Date()) + 1, 1)

That will always list all dates in the previous month. It has no problem
with year wrap-around and also handles leap years.

 

[KARL DEWEY]

Open your query in design view, click on menu INSERT - Column.
Add a calculated field like this ---
Last_Month: Format([TenderDate], "yyyymm")

Add in the criteria row for this new field the following --
Format(DateAdd("m",-1, Date()), "yyyymm")

This will pull last months data.

 

[Dustin B]

I have changed your code below to match my DB.

SELECT *
FROM qryFindBDOcean
WHERE FreightTenderDate >= DateSerial(Year(Date()), Month(Date()), 1)
AND FreightTenderDate < DateSerial(Year(Date()), Month(Date()) + 1, 1)

I get a Compile Error.

"Compile Error. in quey expression 'FreightTenderDate >=
DateSerial(Year(Date()),Month(Date()),1) AND FreightTenderDate <
DateSerial(Year(Date()), Month(Date()) + 1,1)'"

I looked at the code and the operator DateSerial and I don't see where the
code is messed up providing that Access can under stand the addition logic
for Month. Any ideas?

 

[Bob Quintal]

I have changed your code below to match my DB.

SELECT *
FROM qryFindBDOcean
WHERE FreightTenderDate >= DateSerial(Year(Date()), Month(Date()),
1) AND FreightTenderDate < DateSerial(Year(Date()), Month(Date())
+ 1, 1)

I get a Compile Error.

"Compile Error. in quey expression 'FreightTenderDate >=
DateSerial(Year(Date()),Month(Date()),1) AND FreightTenderDate <
DateSerial(Year(Date()), Month(Date()) + 1,1)'"

I looked at the code and the operator DateSerial and I don't see
where the code is messed up providing that Access can under stand
the addition logic for Month. Any ideas?

The code should go in a query, not in the visual basic modules.

Create a new query using the design tool, dismiss the Show tables
dialog without selecting any table, and select SQL View. You paste
the code in there.