Last Month in a query

[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

 

[Alex White MCDBA MCSE]

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.

 

[Adam]

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.

 

[Alex White MCDBA MCSE]

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

 

[Adam]

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.

 

[Rick Brandt]

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.

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

 

[Adam]

Thank you