Need help with VBA project, need it to take dates input by user
I have a form that loads and asks for the starting date (dte1) and end date (dte2):
If I run the comand button without taking any dates it runs like this:
Private Sub CommandButton1_Click()
Dim dte1 As Date
Dim dte2 As Date
dte1 = Date1.Text
dte2 = Date2.Text
Range("A6").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=One Family;DefaultDir=W:\HOME\TU;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("A11"))
.CommandText = Array( _
"SELECT CLIENTS.NAME, CLIENTS.SDischargeDate, CLIENTS.`TANF_SSN_#`, CLIENTS.`VERIFICATION#`" & Chr(13) & "" & Chr(10) & "FROM CLIENTS.TXT CLIENTS" & Chr(13) & "" & Chr(10) & "WHERE (CLIENTS.APPLICANT_TYPE='1') AND (CLIENTS.SDischargeDate>={ts '2006-07-01 00" _
, ":00:00'}) AND (CLIENTS.SDischargeDate<={ts '2006-07-31 00:00:00'})")
.Name = "Query from One Family"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
and the rest of the code goes below.....
it takes it well like this, no errors and does what I want but with a date that is already in there (7/1/2006 to 7/31/2006.
I tried this to make it take the dates:
Private Sub CommandButton1_Click()
Dim dte1 As Date
Dim dte2 As Date
dte1 = Date1.Text
dte2 = Date2.Text
Range("A6").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=One Family;DefaultDir=W:\HOME\TU;DriverId=27;FIL=text;MaxBufferSize=2048;PageTimeout=5;" _
, Destination:=Range("A11"))
.CommandText = Array( _
"SELECT CLIENTS.NAME, CLIENTS.SDischargeDate, CLIENTS.`TANF_SSN_#`, CLIENTS.`VERIFICATION#`" & Chr(13) & "" & Chr(10) & "FROM CLIENTS.TXT CLIENTS" & Chr(13) & "" & Chr(10) & "WHERE (CLIENTS.APPLICANT_TYPE='1') AND(CLIENTS.SDischargeDate>= {ts Dte.txt }) AND (CLIENTS.SDischargeDate<= {ts Dte2.Text})
.Name = "Query from One Family"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
but when I run it, it doesn't take it, it gives me an error in the red line, but the only change is the bold one so I know the error is there. Please help. Thank you
