Previous time peroid query

[bfamo]

Hi,

I'm using this "current week to date"-query:

SELECT Table.DateField
FROM Table
WHERE [Table.DateField] BETWEEN 
 
DateAdd("d", -((Weekday(Date()) - 1)), Date()) AND 
 
Date();

How can I get this SQL statement to go one week back starting with [LastOfDateField]?

Thanks!

 

[HarleyQuinn]

How about:

BETWEEN [LastOfDateField] AND [LastOfDateField]-7

Hope this helps

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[bfamo]

Hmmm... that didnt seem to do the trick. I might have to give a better explanation of what I want to achieve.

I'm working on a report that's going to gather data from inspections done at our clients sites. We do these inspections at multiple sites within one week for each customer. Each site is inspected separately, and therefore get its own recordset in the database.

The query behind the report should do the following:
1) Ask for customer ID (Forms!FrmInspection!CustomerID)
2) Check when the last inspection was performed (ex. Last(TblInspection.Date))
3) Gather all recordsets from LastOfDate and 7 days back.

Now, is this possible to do in a simple query? LastOfDate require Totals to be enabled, and that sets all other fields to "Group by", which kind of messes it all up.

Any suggestions on how to solve this is greatly appreciated!!! thanks

 

[PHV]

A starting point:

SELECT A.*
FROM TblInspection A INNER JOIN (
SELECT CustomerID, Max([Date]) AS LastOfDateField FROM TblInspection GROUP BY CustomerID
) B ON A.CustomerID = B.CustomerID
WHERE A.Date Between B.LastOfDateField-7 AND B.LastOfDateField

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886