Last Month in a query

  • Thread starter Thread starter Adam
  • Start date Start date
A

Adam

Hi All,

I've got a query which has data with a date field.

I want to be able to run this report for last month on each occassion.

I can do a criteria formula that looks at last month however I always
get stuck when it comes to January.

Does anyone have a formula that will work accurately ?

Adam
 
You need to check the DateSerial Function

If you want more help post up your query and I will try and knock it into
shape for you, as your post does not describe your situation well enough, as
DateAdd function may also be answer.
 
This is the query in SQL view:

SELECT Right([CALLID_HDW],6) AS [Call Reference],
ConvertDate([CALLDAT_HDW]) AS [Date], TimeStuff([CALLTME_HDW]) AS [Call
Time], dbo_CALL_HDW.CATCAL1_HDW AS Team, dbo_CALL_HDW.CATCAL2_HDW AS
[Category 2], dbo_CALL_HDW.CATCAL3_HDW AS [Category 3],
dbo_CALL_HDW.CATADV1_HDW AS Avoidable, dbo_CALL_HDW.CATADV2_HDW AS
[Avoidable Reason]
FROM dbo_CALL_HDW
WHERE (((dbo_CALL_HDW.CATCAL1_HDW)="LCSC" Or
(dbo_CALL_HDW.CATCAL1_HDW)="PICEXP" Or
(dbo_CALL_HDW.CATCAL1_HDW)="PICFL" Or
(dbo_CALL_HDW.CATCAL1_HDW)="PICTECH" Or
(dbo_CALL_HDW.CATCAL1_HDW)="SALES&GEN" Or
(dbo_CALL_HDW.CATCAL1_HDW)="SUPP.CARS"));


In the ConvertDate field I would place the criteria. It needs to be
something like Month(now())-1 but better because in January this would
return 0.
 
dateserial(year(now),month(now)-1,day(now))

would give you today's date 1 month previous regardless of which month you
are currently on.

are you trying to pick a specific date or a date range e.g. between
01/12/2004 and 31/12/2004? sorry I live in the UK the dates are in British
format. dd/mm/yyyy
 
Hi I'm UK as well so pls do use british dates !

I'm trying to look at last month, so from the 1st until the end of that
month.
 
Hi I'm UK as well so pls do use british dates !

I'm trying to look at last month, so from the 1st until the end of
that month.
Click to expand...

WHERE DateField
BETWEEN DateSerial(Year(Date()), Month(Date())-1, 1)
AND DateSerial(Year(Date()), Month(Date()), 0)
 
Back
Top