Date Restrictor for Last Week

[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

 

[Ken Snell]

Try this:

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

 

[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())));

 

[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

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>





.

 

[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>

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

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


.