Date Restrictor for Last Week

H

Haji

Hi,

I need to run a query that has a date restrictor for the
previous week. So if I ran it today (November 24th), it
would get all transactions between the 16th and the 22nd.

Thanks,

Haji
 
K

Ken Snell

Try this:

Between DateAdd("d", -6 - DatePart("w", Date(), vbSunday), Date()) And
DateAdd("d", -DatePart("w", Date(), vbSunday), Date())
 
H

Haji

Ken,

Thanks for your help. I am getting a "data type mismatch
in criteria express" error when I try to run this. The
EVE_Date field is a date/time field. Any thoughts?

Thanks,

Haji


SELECT TimeMatters.EVE_Client,
ServiceRetestDays.CategoryName
FROM TimeMatters INNER JOIN ServiceRetestDays ON
TimeMatters.[EVE_Class Code] =
ServiceRetestDays.ServiceCode
WHERE (((TimeMatters.EVE_Client)>"0") AND
((ServiceRetestDays.CategoryName2)>"0") AND
((TimeMatters.EVE_Date) Between DateAdd("d",-6-DatePart
("w",Date(),"vbSunday"),Date()) And DateAdd("d",-DatePart
("w",Date(),"vbSunday"),Date())));
 
G

Graham Mandeno

Hi Haji

SQL does not understand the VB constant vbSunday, so it's converting it to a
string "vbSunday", which is not the data type (integer) that DateAdd is
expecting.

In place of "vbSunday", try the number 1.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Haji said:
Ken,

Thanks for your help. I am getting a "data type mismatch
in criteria express" error when I try to run this. The
EVE_Date field is a date/time field. Any thoughts?

Thanks,

Haji


SELECT TimeMatters.EVE_Client,
ServiceRetestDays.CategoryName
FROM TimeMatters INNER JOIN ServiceRetestDays ON
TimeMatters.[EVE_Class Code] =
ServiceRetestDays.ServiceCode
WHERE (((TimeMatters.EVE_Client)>"0") AND
((ServiceRetestDays.CategoryName2)>"0") AND
((TimeMatters.EVE_Date) Between DateAdd("d",-6-DatePart
("w",Date(),"vbSunday"),Date()) And DateAdd("d",-DatePart
("w",Date(),"vbSunday"),Date())));

-----Original Message-----
Try this:

Between DateAdd("d", -6 - DatePart("w", Date(), vbSunday), Date()) And
DateAdd("d", -DatePart("w", Date(), vbSunday), Date())


--
Ken Snell
<MS ACCESS MVP>





.
 
K

Ken Snell

My error...thanks, Graham, for correcting it.

Haji, Graham's reply is the way to fix this. I don't often use the VBA
function DatePart in a query, and overlooked that you must use the actual
constant, not the VBA constant.

--
Ken Snell
<MS ACCESS MVP>

Graham Mandeno said:
Hi Haji

SQL does not understand the VB constant vbSunday, so it's converting it to a
string "vbSunday", which is not the data type (integer) that DateAdd is
expecting.

In place of "vbSunday", try the number 1.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Haji said:
Ken,

Thanks for your help. I am getting a "data type mismatch
in criteria express" error when I try to run this. The
EVE_Date field is a date/time field. Any thoughts?

Thanks,

Haji


SELECT TimeMatters.EVE_Client,
ServiceRetestDays.CategoryName
FROM TimeMatters INNER JOIN ServiceRetestDays ON
TimeMatters.[EVE_Class Code] =
ServiceRetestDays.ServiceCode
WHERE (((TimeMatters.EVE_Client)>"0") AND
((ServiceRetestDays.CategoryName2)>"0") AND
((TimeMatters.EVE_Date) Between DateAdd("d",-6-DatePart
("w",Date(),"vbSunday"),Date()) And DateAdd("d",-DatePart
("w",Date(),"vbSunday"),Date())));

-----Original Message-----
Try this:

Between DateAdd("d", -6 - DatePart("w", Date(), vbSunday), Date()) And
DateAdd("d", -DatePart("w", Date(), vbSunday), Date())


--
Ken Snell
<MS ACCESS MVP>


Hi,

I need to run a query that has a date restrictor for the
previous week. So if I ran it today (November 24th), it
would get all transactions between the 16th and the 22nd.

Thanks,

Haji


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top