D
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 bas
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 calle
"Busqueda" receives the value.
I've programmed the macro using MSQuery just to see the necessary cod
and the problem has appeared when I've tried to include the para
called "Dia" in the SQL code.
I've tried using " & Dia & " but it doesn't work and I don't know ho
to include my param "Dia" in the SQL code.
Any idea????, I'm in a hurry and whatever kind of help would be
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) & "FRO
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_NUMER
= 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 > {t
'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 Su
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 bas
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 calle
"Busqueda" receives the value.
I've programmed the macro using MSQuery just to see the necessary cod
and the problem has appeared when I've tried to include the para
called "Dia" in the SQL code.
I've tried using " & Dia & " but it doesn't work and I don't know ho
to include my param "Dia" in the SQL code.
Any idea????, I'm in a hurry and whatever kind of help would be
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) & "FRO
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_NUMER
= 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 > {t
'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 Su