AndrewKent
Well-known Member
- Joined
- Jul 26, 2006
- Messages
- 889
Hi there,
I have the following code that pulls back data from Access into Excel...
problem is that all the values in the query are text values, if I update the Query with an extra OR statement...
...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...
Cheers,
Andy
I have the following code that pulls back data from Access into Excel...
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
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...
Code:
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...
Code:
DBRecordSet.Open Source:=Query, ActiveConnection:=DBConnection, CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic, Options:=adCmdText
Cheers,
Andy