last 12

[slimjen]

All, I have a table with a start date and end date with data going back 5 yrs. In a query; I using between in both criteria to get the data for July 1, 2013 to August 31, 2014. I need to pull this query on a monthly basis and I need to automate for the user to pull the last 12 months. I may only need the end date however I was using this code in the criteria: DateAdd(“m”,-12,Now) but it gave me 9/3/2014 and time for all the records. Help pls.

[June7]

Maybe you want Date() instead of Now()?

[slimjen]

Returns no data???

[June7]

Post the complete SQL statement.

Does data have time component?

[slimjen]

This is my SQL code:

SELECT tblCust.Custid, tblMstr.ScriptName, tblMstr.[WState], tblMstr.[Start Date], tblMstr.[End Date], Sum(tblMstr.[Wages]) AS [SumOfWages]
FROM tblMstr  LEFT JOIN tblCust ON tblMstr.Custid = tblCust.Custid
GROUP BY tbl Cust. Custid, tblMstr.ScriptName, tblMstr.WState, tblMstr.[Start Date], tblMstr.[End Date]
HAVING (((tblMstr.[Start Date]) Between #7/1/2013# And #8/1/2014#));

I am trying to automate to "Last twelve months" instead of prompting the users for a date. Thanks

[June7]

Does the static criteria work? Replacing with dynamic parameters should work but that is not the query you posted.

[slimjen]

I posted whAt worked if I put a date range n the criteria. But I would like to automate without users input to get the last twelve months. I tried dateadd("mm",12,Now())

[June7]

(((tblMstr.[Start Date]) Between DateAdd("m", -12, Date()) And Date()));

works for me

[slimjen]

Thanks. I'll try it tomorrow. Does this go in the criteria section?

[June7]

Yes. It should replace the HAVING criteria you show.

[slimjen]

Ok It works. But How can I modify it to get the last twelve months but 2 months behind? For ex. I need 7/1/2013-6/30/2014. I tried using this:

Between DateAdd("m",-12,Date() And DateAdd("m", -2,Date()))

but error says: "You did not enter the keyword and in the Between....And operator". What does this mean??

[June7]

Missing paren: DateAdd("m",-12,Date()) AND

Make sure they are always paired, even number of parens.