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]

If the parameters is a number, you don't use single quotes around it. If it's a date, you can enclose it in # signs and make sure you format the date you pass as mm/dd/yyyy

 

[AndrewKent]

So for a number, it should be...

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

...and for a date...

OR (((tblCustomerData.Date)=#" & Worksheets("Search Engine").Range("C8").Value & "#))

...and how would I format the date? In the database file it's formatted as General Date, I want the column to be brought back into Excel as dd/mm/yyyy hh:mm:ss.

 

[RoryA]

You need to pass the C8 value as mm/dd/yyyy:

Format(Range("C8").Value, "mm/dd/yyyy")

Actually if the date column in your DB includes time, you probably need a start and end date as parameters, or you will need to truncate the db date ato compare it against a single day.

 

[AndrewKent]

Stupid question but...why do I have to do it mm/dd/yyyy...is this the whole stupid VBA americanised dates issue?

 

[RoryA]

Yep.

 

[AndrewKent]

**** lol,

Funny thing is, it seems to be pulling dates back dd/mm/yyyy for me and I haven't changed the formatting. The only thing it is doing is that when I copy from record set, it formats the cells copied without the hh:mm:ss as I had them previously.

 

[AndrewKent]

Or is that because today's the 31st?!?!?!

 

[RoryA]

It's not a question of the format they come back in, it's a question of what dates you get back. It will work for dates that are not ambiguous (like 31st) but if you pass it 05/01/2009 (being 5 Jan) you will get back 01/05/2009 (i.e. 1st May).

 

[AndrewKent]

It seems a little bit temprimental to be honest. I've put in this segment of code...

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
    Worksheets("Search Engine").Range("I5:I" & Range("I65536").End(xlUp).Offset(2, 0).Row & "").NumberFormat = "dd/mm/yyyy hh:mm"

    DBRecordSet.Close
    Set DBRecordSet = Nothing

End Sub

...which pulls the data from Access into Excel. "I" is the column I want to format as a date however it doesn't seem to want to work properly. Is there a better way of structuring this?

Andy