Date value won't work in xl vb code

[BradleyS]

I have been struggling with this for over a week now and just can't understand why I can't get the date section of code to work.

I'm using a data source and trying to enter a date range

This code works fine as it uses a predefined date range i.e. WHERE (slotapp.slotdate='20131010'):

Sub Macro1()
'
' Macro1 Macro
'

'
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Visual FoxPro Database;UID=;;SourceDB=p:\;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Y" _
        ), Array("es;")), Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT slotapp.slotdate" & Chr(13) & "" & Chr(10) & "FROM slotapp slotapp" & Chr(13) & "" & Chr(10) & "WHERE (slotapp.slotdate='20131010')" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_Visual_FoxPro_Database"
        .Refresh BackgroundQuery:=False
    End With
End Sub

I would like to use a cell in xl that I can enter a date, so to try and get this code to work I am first using a variable instead of a cell, but it doesn't recognise the date in the variable

All I have done is added the variable: Dim sDate As String sDate = 20131010

..And entered it in the code: WHERE (slotapp.slotdate='sDate')

Sub Macro1()
'
' Macro1 Macro
'

'
    'Get the date range 
    Dim sDate As String
        sDate = 20131010
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Visual FoxPro Database;UID=;;SourceDB=p:\;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Y" _
        ), Array("es;")), Destination:=Range("$A$1")).QueryTable
        .CommandText = Array( _
        "SELECT slotapp.slotdate" & Chr(13) & "" & Chr(10) & "FROM slotapp slotapp" & Chr(13) & "" & Chr(10) & "WHERE (slotapp.slotdate='sDate')" _
        )
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_Visual_FoxPro_Database"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Does anybody know what I am doing wrong or why it won't work and what it is I need to do to get it to work?

 

[Marcelo Branco]

Hi Bradley,

See if this works

....& "WHERE (slotapp.slotdate=" & sDate & ")")

M.

 

[BradleyS]

Hi No that doesn't work either.

What I have figured out is the comment symbols are nessasary here, because even in the first example that does work i.e. WHERE (slotapp.slotdate='20131010')
If I remove the comments i.e. WHERE (slotapp.slotdate=20131010) it doesn't work.

I have also tried: WHERE (slotapp.slotdate= Chr(39) & 20131010 & Chr(39)) but this doesn't work either.

The field it is entering the date value in within XL is a combo box, if that makes a difference.

It's so frustrating, because it seems so simple, but nothing seems to work as a replacement and I don't have the skill set to figure out any alternatives.

 

[FormR]

Maybe this will work?

Sub Macro1()
'
' Macro1 Macro
'

'
    'Get the date range
    Dim sDate As String
        sDate = "20131010"
    Dim sSQL As String
        sSQL = "SELECT slotapp.slotdate FROM slotapp slotapp WHERE (slotapp.slotdate='" & sDate & "')"
    
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array(Array( _
        "ODBC;DSN=Visual FoxPro Database;UID=;;SourceDB=p:\;SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Y" _
        ), Array("es;")), Destination:=Range("$A$1")).QueryTable
        .CommandText = sSQL
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "Table_Query_from_Visual_FoxPro_Database"
        .Refresh BackgroundQuery:=False
    End With
End Sub

 

[BradleyS]

THATS IT!!!!!!

OMG

I'm so happy. Thank you Thank you Thank you