CODEWITHSUNDEEP
sqlexcelvbaadodb
xnewbie
xnewbie

Reputation: 23

Writing Query statement with a String and a Date in Excel VBA

Using an ADODB connection, I have an excel database where I store data, and I am trying to make a query in one of the sheets(db_Reports). Values for Lastname and Birthday are entered in textboxes (tb_Lastname & tb_bday, respectively) from a userform.

My problem is that the first two query statements work (commented in this case): one queries Lastname which gets a string, the other queries a date. Each of them works separately, but if I combine them since I need to query both name or date in the same search, it is giving me Type Mismatch; Runtime error 13. I have been searching anywhere but I can't figure out how can I make this work. I even tried to convert date into a string but still, it failed. 

Private Sub SearchRecords_Click()

    Dim conn As New ADODB.Connection 
    Dim rst As New ADODB.Recordset 
    Dim db_path As Variant

    db_path = "E:\DATABASE.xlsm"

    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & db_path & ";Extended Properties=""Excel 12.0 Macro;HDR=YES;"";"

    Dim qry As String

    'qry = "Select [LastName], Format([Birthday], 'mmmm dd, yyyy') from [db_Reports$] where [LastName] = '" & tb_Lastname & "'"  'Working
    'qry = "Select [LastName], Format([Birthday], 'mmmm dd, yyyy') from [db_Reports$] where [Birthday] = '" & CStr(CLng(CDate(tb_bday))) & "'"  'Working
    qry = "Select [LastName], Format([Birthday], 'mmmm dd, yyyy') from [db_Reports$] where [LastName] = '" & tb_Lastname & "' or [Birthday] = '" & CStr(CLng(CDate(tb_bday))) & "'"  'Not Working - Type Mismatch; Runtime error 13'

    rst.Open qry, conn, adOpenKeyset, adLockOptimistic

    rst.Close
    conn.Close
    Set rst = Nothing
    Set conn = Nothing
End Sub

Basically, how can you make a query for both a string and a date in one line?

I apologize for the long code line, the lingo I used, and even the formulation of the question as I am a self-taught newbie.

Upvotes: 0

Views: 215

Answers (1)

xnewbie
xnewbie

Reputation: 23

It seems that '[Birthday] = '" & CStr(CLng(CDate(tb_bday))) &' creates an error when no value is entered in the textbox, tb_bday. I cant explain why. As a workaround, I just created an if-then statement. I still would want to avoid using if-then if it can be coded in one line instead. 

Private Sub SearchRecords_Click()

    Dim conn As New ADODB.Connection 
    Dim rst As New ADODB.Recordset 
    Dim db_path As Variant

    db_path = "E:\DATABASE.xlsm"

    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & db_path & ";Extended Properties=""Excel 12.0 Macro;HDR=YES;"";"

    Dim qry As String

    if tb_bday.value = ""  then 
        qry = "Select [LastName], Format([Birthday], 'mmmm dd, yyyy') from [db_Reports$] where [LastName] = '" & tb_Lastname & "'"  
    else 
        qry = "Select [LastName], Format([Birthday], 'mmmm dd, yyyy') from [db_Reports$] where where [LastName] = '" & tb_Lastname & "' or [Birthday] = '" & CStr(CLng(CDate(tb_bday))) & "'"
    end if 

    rst.Open qry, conn, adOpenKeyset, adLockOptimistic

    rst.Close
    conn.Close
    Set rst = Nothing
    Set conn = Nothing
End Sub

Upvotes: 2

Related Questions