Problem getting DateAdd function to work on a SQL linked table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the following expression which works perfectly when I use a local
table. When I use the table that is ODBC linked to a SQL table, the
expression brings a null answer set. Between Date() And
DateAdd("d",-6,Date()). One more thing - is there any way to write the
expression so that it captures the last 6 business days (excludes Sat and Sun
as part of the 6 days)?
 
You are my HERO - it worked!!! Thank you so much. By the way - do you have
any thoughts on how I can count back 6 that do not include Sat or Sun?
 
Create a table named CountNumber with a field CountNUM containing numbers
zero ( 0 ) thru 99.
First query is named 6_Weekdays_1 -- You will need to substitute your
own table and field names for [Change Requests].[Date open] in the query.

SELECT [Change Requests].[Date open], DateAdd("d",[cOUNTnum],[Date open]) AS
[Days after]
FROM [Change Requests], CountNumber
WHERE (((Weekday(DateAdd("d",[cOUNTnum],[Date open]))) Between 2 And 6) AND
((CountNumber.CountNUM)>0 And (CountNumber.CountNUM)<=9))
ORDER BY [Change Requests].[Date open], DateAdd("d",[cOUNTnum],[Date open]);


SELECT T.[Date open], T.[Days after], (SELECT COUNT(*)
FROM [6_Weekdays_1] T1
WHERE T1.[Date open] = T.[Date open]
AND T1.[Days after] <= T.[Days after]) AS Rank
FROM 6_Weekdays_1 AS T
WHERE ((((SELECT COUNT(*)
FROM [6_Weekdays_1] T1
WHERE T1.[Date open] = T.[Date open]
AND T1.[Days after] <= T.[Days after]))=6))
ORDER BY T.[Date open], T.[Days after];
 
Back
Top