On Friday, September 13, 2013 10:06:01 AM UTC+8, witek wrote:
Steven North wrote:

"WHERE (GL_DATABASE.CC LIKE " & mqryCCA & ") AND (GL_DATABASE.PERIOD_NAME LIKE " & mqryPERIOD & ")"





print it in debug window

do not you miss "" around mqryCCA and mqryPERIOD values?





LIKE '" & mqryCCA & "')



or



LIKE """ & mqryCCA & """)

Thanks witek... this is perfect!

LIKE '" & mqryCCA & "')

Revised code...

---<BEGIN CODE---

Public Sub ImportTransactions()
Dim rsDATA As ADODB.Recordset
Dim szCONNECT As String
Dim szSQL As String
Dim mqryCCA As String
Dim mqryPERIOD As Date


' Dim conn As New Connection, rec As New Recordset
Dim ws As Worksheet
Dim sql$, i&

Set ws = ThisWorkbook.Worksheets("Sheet1")

' Create the connection String

' Obtain the values from Excel
mqryCCA = ws.Range("qryCCA").Value
mqryPERIOD = ws.Range("qryPERIOD").Value

szCONNECT = "Provider=microsoft.ACE.OLEDB.12.0;" + _
"Data Source=" + ThisWorkbook.Path + "\OEPA Fiancial Database.accdb"

' szCONNECT = "Provider=microsoft.JET.OLEDB.4.0;" & _
"Data Source=" + ThisWorkbook.Path + "\OEPA Fiancial Database.accdb"

' Create the SQL statement

szSQL = "SELECT * " & _
"FROM GL_DATABASE " & _
"WHERE (GL_DATABASE.CC LIKE '" & mqryCCA & "') AND (GL_DATABASE.PERIOD_NAME LIKE '" & mqryPERIOD & "')" & _
"ORDER BY GL_DATABASE.GL_CODE, GL_DATABASE.POSTED_DATE "



' Create the Recordset object and run the query
Set rsDATA = New ADODB.Recordset
rsDATA.Open szSQL, szCONNECT, adOpenForwardOnly, _
adLockReadOnly, adCmdText


' Make sure we got records back
If Not rsDATA.EOF Then
' Dump the contents of the record set onto the worksheet
ws.Range("A10").CopyFromRecordset rsDATA
' Close the recordset
rsDATA.Close
' Fit the columd widths to the data
ws.UsedRange.EntireColumn.AutoFit
Else
MsgBox "Error: No records returned.", vbCritical
End If

' Close the recordset if it is still open
If CBool(rsDATA.State And adStateOpen) Then rsDATA.Close
Set rsDATA = Nothing





End Sub

---<END CODE---