Hi
I am trying to send a query to access to return data to excel.
When I run it I get the following error:
Run-time error '-2147217900 (80040e14)':
Syntax error (missing operator) in query expression 'Claim Payments.Claim No'.
Currently my code reads as below.
The value for the querystring variable on this run is:
(Claim Payments.Claim No=50) AND (Claim Payments.Policy No=50) AND (Claim Payments.Payment Date>{ts '2007-12-31 00:00:00'}) AND (Claim Payments.Claim Type=AD) AND (Claim Payments.Payment Date>{ts '2008-05-30 00:00:00'})
Code
Sub importdata()
Sheets("Output").Select
Range("A18:N63000").Select
Selection.ClearContents
Range("A18").Select
Dim QueryString As String
Dim n As Integer
Dim Results As Integer
Results = Range("NoOfVariables").Value
n = Results
QueryString = ""
Sheets("Variables").Select
Range("F11").Select
Do While n <> 0
If ActiveCell.Value <> "" Then
If n = 1 Then
QueryString = QueryString & ActiveCell.Value
Else
QueryString = QueryString & ActiveCell.Value & " AND "
End If
n = n - 1
End If
ActiveCell.Offset(1, 0).Select
Loop
Sheets("Output").Select
Range("A10").Select
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim SQL As String
Dim SQL1 As String
Dim SQL2 As String
Dim SQL3 As String
Dim SQL4 As String
Dim MyDB As String
Dim R As Long, C As Long
Dim ConnStr As String
Dim User As String, PW As String
SQL1 = "SELECT Claim Payments.Claim No, Claim Payments.Policy No, Claim Payments.Policy Eff Date, Claim Payments.Claim Date, Claim Payments.Reported Date, Claim Payments.Payment Date, Claim Payments.Total Paid, Claim Payments.Claim Type, Claim Payments.Payee, Claim Payments.Payee Code, Claim Payments.Claimant, Claim Payments.Injury Code, Claim Payments.Analysis, Claim Payments.Amount"
SQL2 = "FROM I:\Finance\CLAIMS Payments Analysis\ClaimPayments.Claim Payments Claim Payments"
SQL3 = "WHERE " & QueryString
SQL4 = "ORDER BY Claim Payments.Claim No, Claim Payments.Payment Date"
SQL = SQL1 & " " & SQL2 & " " & SQL3 & " " & SQL4
MyDB = "I:\Finance\CLAIMS Payments Analysis\ClaimPayments.mdb"
User = ""
PW = ""
ConnStr = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & MyDB & ";" & _
"Persist Security Info=False"
Conn.Open ConnStr, User, PW
RS.Open SQL, Conn
Do While Not RS.EOF
R = R + 1
For C = 1 To RS.Fields.Count
If R = 1 Then
ActiveSheet.Cells(R, C) = RS.Fields(C - 1).Name
Else
ActiveSheet.Cells(R, C) = RS.Fields(C - 1).Value
End If
Next
If Not R = 1 Then RS.MoveNext
Loop
RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing
ActiveSheet.Columns.AutoFit
End Sub
Display More
Hopefully it's something really simple I'm missing! Any help greatly appreciated.
Thanks
Aaron