Share via

MS Query from Excel - Use of DatePart() function

Anonymous
2011-03-17T14:04:39+00:00

I’m searching a database using MS Query from Excel.  I need to limit the information returned to entries with a closed date anytime last week between Sunday and Saturday and think I need a filter like  DatePart(“ww”, LabRequests.ClosedDate) = DatePart(“ww”, GETDATE())-1 to do this so I added a WHERE statement in the query with this condition but it doesn’t work.  Can someone kindly help explain how this can be done? 

Thanks in advance for your help,

Jim

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2011-03-17T15:41:46+00:00

As you have experienced, some functions do not work in MSQuery, which do work from within Access. This is (for example) because Access allows you to use VBA functions from within a query, whereas MSQuery does not allow this (which makes sense, since it has no VBA). Your best bet is to calculate the actual date pertaining to the Sunday to Saturday range you need and use that as the filter criteria argument.

Was this answer helpful?

0 comments

0 additional answers

Sort by: Most helpful
Most helpful Newest Oldest