Using DateAdd in a "Between" criteria in SQL

[SERT1]

Does anyone know the syntax to query for data between 3 months ago and 1 month ago, ie.

>=DateAdd(&quot;m&quot;,-3,Date()) And <=DateAdd(&quot;m&quot;,-1,Date())

or

Between DateAdd(&quot;m&quot;,-3,Date()) And DateAdd(&quot;m&quot;,-1,Date())

I've tried both, and neither works. Can you use the &quot;dateadd&quot; function in a &quot;between&quot; statement?

This is being used in the criteria part of a query in Design SQL.

Thanks.

 

[SgtJarrow]

This would be the SQL from the first....

SELECT Table1.Date
FROM Table1
WHERE (((Table1.Date)>=DateAdd(&quot;m&quot;,-3,Date()) And (Table1.Date)<=DateAdd(&quot;m&quot;,-1,Date())));

And the second....

SELECT Table1.Date
FROM Table1
WHERE (((Table1.Date) Between DateAdd(&quot;m&quot;,-3,Date()) And DateAdd(&quot;m&quot;,-1,Date())));

They both worked fine for me in Access97 There is no I in team.

Robert L. Johnson III, A+, Network+, MCP
robert.l.johnson.iii@citi.com

 

[SERT1]

I tried it and it doesn't work. I'll copy a piece of my sql:
((Format([FIN_DATE],&quot;mmmm yyyy&quot) Between DateAdd(&quot;m&quot;,-3,Date()) And DateAdd(&quot;m&quot;,-1,Date()))

I get 0 records, and there should be some.

 

[rajeessh]

Does the Project MDB has References for Microsoft Visual Basic for Applications
Sometimes format doesnt work without that???

goto Code
Tools->References->Select Microsft Visual Basic For Applocations Extensibility 5.x or so ever
Click Ok

Try running query

 

[SERT1]

Yes, it was already selected and the query still isn't returning the proper data.