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

Question

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.

at line 15 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' — xnewbie
– xnewbie, Commented Feb 12, 2020 at 2:29
And can you show us how tb_Lastname and tb_birthday get set? — Joseph
– Joseph, Commented Feb 12, 2020 at 2:30
I appreciate your question but I do not know what you mean by "get set." It must be wrong for me not to set it or I may have done something to it not knowing that I have "set it" already. I have been doing the same by not setting them up but it had worked before. Can you please expound what you mean by "get set"? To tell a bit more, those are the Names I assigned the textboxes. — xnewbie
– xnewbie, Commented Feb 12, 2020 at 2:38
Sure thing, what I mean is that there must be some code somewhere that looks like tb_birthday = some_value. What I'm thinking is that one of those items is not being set correctly which could cause the error. The mismatch error means a "type" error. Quick example, if your variable expects a number but you provide a string that cannot be converted to a number, then you will get this kind of error. By the way, don't worry that you're new to this stuff. We all were at some point. — Joseph
– Joseph, Commented Feb 12, 2020 at 2:43

xnewbie · Accepted Answer · 2020-02-12 03:26:32Z

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

it errors because, CDate(...) expects a valid date expression and an empty string isn't one. If you want your users to run the query without a date, you can't avoid the check. Note the correct check is If tb_Name.Text = vbNullString Then (Null and "" (an empty string) arn't the same thing). Note: you'll still get an error if the user enters something that is not a recognisable date.
You are absolutely right. I did use "" instead of null in my code coz just like you warned me about, it made an error. Of course, you spot my error before i had a chance to edit it. But I appreciate you informing me that I cant avoid the check coz I have spent the weekend just to find that out. Also to avoid an unrecognizable date, I put up validation in the textbox. Thanks.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related