How to assign the value from an InputBox to a WHERE statement?

[Jonas F]

Sub ShowPeriod()

Dim mydate As Date
Dim myDate2 As Date

Sheets("Period").Select
Range("A1").Select

mydate = InputBox("From date YYYY-MM-DD")

myDate2 = InputBox("Till datum YYYY-MM-DD")

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=E:\WINNT\Profiles\skotor\…….
………………
"SELECT `table`.date, …..
………………
`table`.Productname" & Chr(13) & "" & Chr(10) & "FROM `E:\WINNT\Profiles\skotor\……`.`table` `table`" & Chr(13) & "" & Chr(10) & "WHERE (`table`" _
, _
".date>={'mydate'} And `table`.datum<{'myDate2'})" & Chr(13) & "" & Chr(10) & "ORDER BY `table`.date" _
)
.FieldNames = False
.RefreshStyle = xlOverwriteCells
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.HasAutoFormat = False
.BackgroundQuery = True
.TablesOnlyFromHTML = True
.Refresh BackgroundQuery:=False
.SavePassword = True
.SaveData = False

End With
End Sub

myDate gets the value from the InputBox on the 6th and 7th rows, but when I step into the macro I get a run time error when I do the refresh backgroundquery. Can somebody help me?
Please

[Leandro Avelar]

I not sure but you have some problems,

1 - When i use a text or a mask for date and put in a query i use ...where DATE=#mm/dd/yy#
2 - the date it´s with only 8 digits
3 - the date it´s in mm/dd/yy format

try this and i hope to help

Leandro de Avelar
System Engineer
National Institut of Telecommunications