return last month from query

  • Thread starter Thread starter desertdirk
  • Start date Start date
D

desertdirk

Hi
I don't have much experience with this.
How do I get all the records from the last month in a table? I have
tried dateadd("m",-1,date()) but that does not return any records. This
is a complex query with 2 calculated fields and uses 2 tables and a
query. SQL code is below, without the criteria for last month.

SELECT TblsiteID.siteNumber, TblsiteID.customerId,
TblsiteID.rentalType, meterRead.readDate, meterRead.meterReading,
[meterRead Query].MaxOfmeterReading, [QryPrimaryReading Query].rate,
[meterRead Query]![MaxOfmeterReading]-[meterRead]![meterReading] AS
usage, [rate]*[usage] AS amountDue
FROM [QryPrimaryReading Query], TblsiteID INNER JOIN ([meterRead Query]
INNER JOIN meterRead ON [meterRead Query].siteNumber =
meterRead.siteNumber) ON (TblsiteID.siteNumber = meterRead.siteNumber)
AND (TblsiteID.siteNumber = [meterRead Query].siteNumber)
WHERE (((TblsiteID.rentalType)="leased tenant"))
ORDER BY TblsiteID.siteNumber, meterRead.readDate;

I am trying to get the last month from meterRead.readDate.
Am I trying to pack too much into one query?
Thanks
 
WHERE (TblsiteID.rentalType="leased tenant")
AND (meterRead.readDate BETWEEN DateSerial(Year(Date()), Month(Date())-1, 1)
AND DateSerial(Year(Date()), Month(Date()), 0)

DateSerial(Year(Date()), Month(Date())-1, 1) gives you the first day of the
previous month, DateSerial(Year(Date()), Month(Date()), 0) gives you the
last day of the previous month.

If meterRead.readDate includes time (as it would if you use the Now()
function to populate it), change the second parameter to
DateSerial(Year(Date()), Month(Date()), 1)
 
You have a field named meterRead.readDate.

You want to select records when the date in that field is in last month.

?You want that to be dynamic, so it will ALWAYS select the previous month,
no matter the current date?

You can use the DateSerial() function and the Date() function (today's
date), with an expression in the query criterion for the date field
something like (actual syntax may vary):

Between DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date(), Month(Date()), 0)

In English, this says to find dates starting on the 1st of the current
month - 1 (= last month), and ending on the day=0 of the current month
(which Access translates to the last day of the previous month).

This should even work in January of a new year, since subtracting 1 from the
month should 'bump' back to December of the previous year.

Good luck!

Jeff Boyce
<Access MVP>
 
thanks for this.
I was not being notified that I got any replys so sorry for the delay
and responding.

This works like a charm
DD
 
Back
Top