Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with DATE data type
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_SM P99E;", 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 -- dmplacebo ------------------------------------------------------------------------ dmplacebo's Profile: http://www.excelforum.com/member.php...o&userid=25095 View this thread: http://www.excelforum.com/showthread...hreadid=386352 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with DATE data type
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems with DATE data type
As I recall, it is pretty flexible, and I tend to use an unambiguous format, such as Format,Dia,"dd-mmm-yyyy") Bob "abcd" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
plot schedule-type data in graph, date and times as x & y axes | Charts and Charting in Excel | |||
Problems with DATE data type | New Users to Excel | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) | |||
COUNTIF() data type problems | Excel Programming |