Need to account for weekends on some SQL

[RobertM]

I need to query for dates from current day to 7 days out. What I have here
will count weekends in the query. I need to improve this SQL so it does not
take weekend days into consideration:

Between Date()-"1" And Date()+"8"

Thank you

 

[KARL DEWEY]

First you need to delete the quotes from the numbers in this criteria.

Then add your date field in a new column of the query design view and edit
it to look like this -- Weekend Days: Format([YourDateField], "w")
Then use criteria <>1 And <>7

 

[KARL DEWEY]

There are probably beter ways to do this but try this using a table named
CountNumber with field CountNUM containing 0 (zero) through 100 --
SELECT TOP 1 DateAdd("d",-[CountNUM],Date()) AS [First_Date], NULL AS End_Date
FROM CountNumber
WHERE (((DateAdd("d",-[CountNUM],Date()))<Date()) AND
((Format(DateAdd("d",-[CountNUM],Date()),"w"))<>1 And
(Format(DateAdd("d",-[CountNUM],Date()),"w"))<>7));
UNION ALL SELECT NULL AS [First_Date], DateAdd("d",[CountNUM],Date()) AS
End_Date
FROM CountNumber AS T
WHERE ((((SELECT COUNT(*)
FROM [CountNumber] T1
WHERE DateAdd("d",[T1].[CountNUM],Date()) <=
DateAdd("d",[T].[CountNUM],Date()) AND
(((Format(DateAdd("d",[T].[CountNUM],Date()),"w"))<>1 And
(Format(DateAdd("d",[T].[CountNUM],Date()),"w"))<>7))))=9));

--
KARL DEWEY
Build a little - Test a little

First you need to delete the quotes from the numbers in this criteria.

Then add your date field in a new column of the query design view and edit
it to look like this -- Weekend Days: Format([YourDateField], "w")
Then use criteria <>1 And <>7

--
KARL DEWEY
Build a little - Test a little

I need to query for dates from current day to 7 days out. What I have here
will count weekends in the query. I need to improve this SQL so it does not
take weekend days into consideration:

Between Date()-"1" And Date()+"8"

Thank you