Problems with DATE data type

[dmplacebo]

Hi!!!

I've got a little problem with the next macro in Excel.

The problem is that I must look for some information in a data base
using as filter a specific day an hour in the year.

I get the date and the hour using a form and then the macro called
"Busqueda" receives the value.

I've programmed the macro using MSQuery just to see the necessary code
and the problem has appeared when I've tried to include the param
called "Dia" in the SQL code.

I've tried using " & Dia & " but it doesn't work and I don't know how
to include my param "Dia" in the SQL code.

Any idea????, I'm in a hurry and whatever kind of help would be a
pleasure.

Thank you in advance, David.





Sub Busqueda(Dia As Date)
'
' Busqueda Macro
' Macro grabada el 04/07/2005 por s602043
'

'
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=PAL_SMP99E;UID=SMP;PWD=SMP;SERVER=PAL_SMP99E;",
Destination:=Range( _
"A7"))
..CommandText = Array( _
"SELECT T_BLOC_ARRET.I_ZON_NUMERO,
T_BLOC_ARRET.C_BA__DATE_DE_DEBUT, T_BLOC_ARRET.C_BAP_LIBELLE_ARRET,
T_POSTE_ARCHIVE.I_HPO_DATE" & Chr(13) & "" & Chr(10) & "FROM
SMP.T_BLOC_ARRET T_BLOC_ARRET, SMP.T_POSTE_ARCHIVE T_POSTE_ARCHIV" _
, _
"E" & Chr(13) & "" & Chr(10) & "WHERE T_BLOC_ARRET.I_HPO_NUMERO
= T_POSTE_ARCHIVE.I_HPO_NUMERO AND T_BLOC_ARRET.I_ZON_NUMERO =
T_POSTE_ARCHIVE.I_ZON_NUMERO AND T_BLOC_ARRET.C_BA__DATE_DE_DEBUT > {ts
'2005-07-11 09:26:24'} AND ((T_BLOC_ARRET.I_ZON_NUMERO=1002))")
..Name = "Consulta desde PAL_SMP99E"
..FieldNames = True
..RowNumbers = False
..FillAdjacentFormulas = False
..PreserveFormatting = True
..RefreshOnFileOpen = False
..BackgroundQuery = True
..RefreshStyle = xlInsertDeleteCells
..SavePassword = True
..SaveData = True
..AdjustColumnWidth = True
..RefreshPeriod = 0
..PreserveColumnInfo = True
..Refresh BackgroundQuery:=False
End With
End Sub

 

[abcd]

I think Dia is a serial number where the SQL code needs a text
formating (don't remember but may be something like #MM-DD-YYYY# needed
or similar)

so you will need a Format(Dia,"mm-dd-yyyy") to convert the date in
the regular syntax for SQL.

Tou should try to ask for a fixed date in a string, see if it works

then try to make a dynamic string by "...." & Format(...)
(use a msgbox to see if ok)

then add this dynamic string in the SQL request


this is the "pro-procedure" to be sure and keep time

 

[Bob Phillips]

As I recall, it is pretty flexible, and I tend to use an unambiguous format,
such as

Format,Dia,"dd-mmm-yyyy")

Bob