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