SQL Query in VBA

[AndrewKent]

Hi there,

I have the following code that pulls back data from Access into Excel...

Sub FindCustomerData(DBConnection As ADODB.Connection)

    Dim DBRecordSet As ADODB.Recordset
    Dim Query As String

    Query = "SELECT tblCustomerData.Record_ID, tblCustomerData.First_Name, tblCustomerData.Surname, tblCustomerData.Age, tblCustomerData.Created_Date FROM tblCustomerData  WHERE (((tblCustomerData.Record_ID)='" & Worksheets("Search Engine").Range("C4").Value & "')) OR (((tblCustomerData.First_Name)='" & Worksheets("Search Engine").Range("C5").Value & "')) OR (((tblCustomerData.Surname)='" & Worksheets("Search Engine").Range("C6").Value & "'));"
    
    Set DBRecordSet = New ADODB.Recordset
    DBRecordSet.CursorLocation = adUseServer
    DBRecordSet.Open Source:=Query, ActiveConnection:=DBConnection, CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, Options:=adCmdText
    
    Worksheets("Search Engine").Range("E5").CopyFromRecordset DBRecordSet

    DBRecordSet.Close
    Set DBRecordSet = Nothing

End Sub

problem is that all the values in the query are text values, if I update the Query with an extra OR statement...

OR (((tblCustomerData.Age)='" & Worksheets("Search Engine").Range("C7").Value & "'))

...I get errors, how to I update my code here so that it will allow me query on a number as well as a text string. I'll also need to know if there is any differences for queries based on dates as well.

The error I get is data type mismatch on this line...

DBRecordSet.Open Source:=Query, ActiveConnection:=DBConnection, CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, Options:=adCmdText

Cheers,

Andy

 

[RoryA]

I suspect your data is actually coming across as text if the numberformat doesn't work. You could use the Format function in the query to format the dates?

 

[AndrewKent]

I've only just got my head around adding SQL queries to VBA code so without sounding too much like I need spoon fed on this one, I've no idea how to format the query.

 

[RoryA]

Query = "SELECT tblCustomerData.Record_ID, tblCustomerData.First_Name, tblCustomerData.Surname, tblCustomerData.Age, Format(tblCustomerData.Created_Date, "dd/mm/yyyy hh:mm:ss") As CreateDate FROM tblCustomerData WHERE (((tblCustomerData.Record_ID)='" & Worksheets("Search Engine").Range("C4").Value & "')) OR (((tblCustomerData.First_Name)='" & Worksheets("Search Engine").Range("C5").Value & "')) OR (((tblCustomerData.Surname)='" & Worksheets("Search Engine").Range("C6").Value & "'));"

for example.

 

[AndrewKent]

Just adapted that code and put it in, works grand! Thank you!