How to pass a date in Excel to ms query
"WHERE (`Shift/Day Report`.Date={ts '2004-04-01 00:00:00'})"
"WHERE (`Shift/Day Report`.Date={ts '" & Format(Range("A1"), _
"YYYY-MM-DD HH:MM:SS") & "'})"
replace HH:MM:SS with 00:00:00 if you won't have a date value including time
in A1 (only a date).
--
Regards,
Tom Ogilvy
"Vince" wrote in message
...
The following code works fine, but I need the date referenced in the query
to update automatically with the date that is located in Cell A1 of the
spreadsheet.
I would appreciate any help given.
Vince
--------------------------------------------------------------------------
--------------
With Selection.QueryTable
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=C:\ABBTemp\STK5.MDB;DefaultDir=C:\ABB Temp;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5" _
), Array(";"))
.CommandText = Array( _
"SELECT `Shift/Day Report`.Date, `Shift/Day Report`.`PRODUCTION
TONS_DAY`" & Chr(13) & "" & Chr(10) & "FROM `C:\ABBTemp\STK5`.`Shift/Day
Report` `Shift/Day Report`" & Chr(13) & "" & Chr(10) & "WHERE (`Shift/Day
Report`.Date={ts '2004-04-01 00:00:00'})" & Chr(13) & "" & Chr(10) & "O" _
, "RDER BY `Shift/Day Report`.Date")
.Refresh BackgroundQuery:=False
End With
|