Error 3075 syntax error in query statement 1

[vba317]

I Am using access 2003. I am trying to pull information from a table without much luck I am getting a syntax error. I have been trying to track down where I have made the error without much luck. I have 5 fields from a table that I am trying to export to an excel sheet. Any help would be appreciated.

The name of the tables are: fields
DAT_Pats PatName AcctNu aid
DAT_ReportData aid uci rptpd doschg ChgAmt pmts

I need to join the two tables using the aid field
I started with copying the code directly from a query that works

'SQL from query

SELECT DAT_ReportData.uci, DAT_ReportData.rptpd, DAT_Pats.PatName, DAT_Pats.AcctNu, DAT_ReportData.doschg, DAT_ReportData.ChgAmt, DAT_ReportData.pmts
FROM DAT_ReportData INNER JOIN DAT_Pats ON DAT_ReportData.aid = DAT_Pats.aid
WHERE (((DAT_ReportData.rptpd)=[Enter Period]))
ORDER BY DAT_Pats.PatName, DAT_ReportData.doschg;

'code in access
'alias DatRpt
          strSQL = "SELECT DAT_Pats.PatName, DAT_Pats.AcctNu," & _
        "DatRpt.doschg, DatRpt.ChgAmt, DatRpt.pmts" & _
        "FROM DAT_ReportData DatRpt" & _
        "INNER JOIN DAT_Pats ON DatRpt.aid = DAT_Pats.aid" & _
        "WHERE (((DatRpt.rptpd) = strCurrentMonth))" & _
        "ORDER BY DAT_Pats.PatName, ;"
        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot) Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
          If (rst.RecordCount > 0) Then
            With rst
                .MoveLast
                .MoveFirst
            End With
            'Set up the first data row on excel template AnestheticSolutions_2.xlt
            iRwCnt = 7 ' Row starts at 7
            iBegRow = 7 ' First iBegRow
            'iEndRow = 5 ' First iEndRow
            strCurYr = rst![Yr]
            strGrpYr = rst![Yr]
            'Sets 1st year
          End If
'        'Counter to loop through all records
    For Z = 1 To rst.RecordCount
            With goXL.ActiveSheet
                    .Cells(iRwCnt, 1) = rst![1]
                    .Cells(iRwCnt, 2) = rst![2]
                    .Cells(iRwCnt, 3) = rst![3]
                    .Cells(iRwCnt, 4) = rst![4]
                    .Cells(iRwCnt, 5) = rst![5]
            End With
            rst.MoveNext
            iRwCnt = iRwCnt + 1

 

[PHV]

Lack of spaces around keywords.
I assume that DatRpt.rptpd is Text

strSQL = "SELECT DAT_Pats.PatName,DAT_Pats.AcctNu," & _
 "DatRpt.doschg,DatRpt.ChgAmt,DatRpt.pmts" & _
 " FROM DAT_ReportData DatRpt" & _
 " INNER JOIN DAT_Pats ON DatRpt.aid = DAT_Pats.aid" & _
 " WHERE DatRpt.rptpd='" & strCurrentMonth & "'" & _
 " ORDER BY DAT_Pats.PatName,DatRpt.doschg"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Thank you this did fix the original error. I am now getting another error 3464 data type mismatch and

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot) highlights yellow.

Tom

 

[PHV]

What is the data type of DatRpt.rptpd ?
If numeric:
" WHERE DatRpt.rptpd=" & strCurrentMonth & _
If date:
" WHERE DatRpt.rptpd=#" & strCurrentMonth & "#" & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

I found out that i was using the wrong variable for that part of the operation. strMonth is a text AUG 2012 and lirptMon is an integer 368 which is what is in the table. I changed the code to lirptMon and now I still get an error 3464 data mismatch error.

strSQL = "SELECT DatRpt.uci, DatRpt.rptpd, DAT_Pats.PatName as 1,DAT_Pats.AcctNu as 2," & _
        " DatRpt.doschg as 3,DatRpt.ChgAmt as 4,DatRpt.pmts as 5" & _
        " FROM DAT_ReportData DatRpt" & _
        " INNER JOIN DAT_Pats ON DatRpt.aid = DAT_Pats.aid" & _
        " WHERE DatRpt.rptpd='" & liPdID & "'" & _
        " ORDER BY DAT_Pats.PatName,DatRpt.doschg"

        Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
          If (rst.RecordCount > 0) Then
            With rst
                .MoveLast
                .MoveFirst
            End With
            'Set up the first data row on excel template AnestheticSolutions_2.xlt
            iRwCnt = 7 ' Row starts at 7
            iBegRow = 7 ' First iBegRow
            'iEndRow = 5 ' First iEndRow
            strCurYr = rst![Yr]
            strGrpYr = rst![Yr]
            'Sets 1st year
          End If
'        'Counter to loop through all records
    For Z = 1 To rst.RecordCount
            With goXL.ActiveSheet
                    .Cells(iRwCnt, 1) = rst![1]
                    .Cells(iRwCnt, 2) = rst![2]
                    .Cells(iRwCnt, 3) = rst![3]
                    .Cells(iRwCnt, 4) = rst![4]
                    .Cells(iRwCnt, 5) = rst![5]
            End With
            rst.MoveNext
            iRwCnt = iRwCnt + 1
'            If Not rst.EOF Then
'                strCurYr = rst![Yr]
'
'               If (strCurYr <> strGrpYr) Then
'                        iRwCnt = iRwCnt + 4
'                        iRwCnt1 = iRwCnt - 4
'                        iRwCnt2 = iRwCnt - 3
'                        iColCnt = 2
'                        iEndRow = iRwCnt - 5
'                        'Sets Year at top of Sheet
'                        strGrpYr = strCurYr


'               End If ' strCurYr <> strGrpYr
                  
'         End If 'rst.EOF
         
    'End If '(rst.RecordCount > 0)
         
     Next Z

 

[PHV]

What are DatRpt.rptpd and liPdID ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

DatRpt.rptid is a field from the DAT_ReportData table. DatRpt is an alias for the DAT_ReportData table. liPdID is a variable that is set from a listbox on the form that this code is embeded in.
cboRptMon is the name of the control on the form, this is a number for the current month which is 368. The control is based on a query called _lstsrc_ReportMonths this query is based on a table called DICT_ImportMonthList the query has the following fields in it, ID, MonDesc and finalpost.

Me.cboRptMon.Column(1) is the next column in the table DICT_ImportMonthList, it is the MonDesc (Month Description) field an example would be AUG 2012.

liPdID is assigned in the following code:

If IsNull(Me.cboImpMonData.Value) Then
    MsgBox " Enter current month's data"
    Else
    liPdID = Me.cboRptMon.Value
strCurMonth = Me.cboRptMon.Column(1) 'CREATE REPORTS COMBOBOX
End If

 

[PHV]

So, replace this:
" WHERE DatRpt.rptpd='" & liPdID & "'" & _
with this:
" WHERE DatRpt.rptpd=" & liPdID & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Now I get a runtime error 13 Type mismatch

 

[PHV]

And this ?
" WHERE DatRpt.rptpd=" & CStr(liPdID) & _

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

No I am still getting a runtime error 13

 

[PHV]

At which line of code ?
BTW, how is Dimmed rst ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Here are all the Dim statements
Private Sub cmdExport_Click()

Dim liPdID As Integer
Dim strCurMonth As String
Dim rst As Recordset
Dim strSQL As String
Dim strSaveFile As String
Dim rstEncs As Recordset
Dim liEncCnt As Integer
Dim iRwCnt As Integer
Dim iBegRow As Integer
Dim iEndRow As Integer
Dim strUCI As String



The error is at the line
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot) which is the next line after the " ORDER BY DAT_Pats.PatName,DatRpt.doschg" line

 

[PHV]

Replace this:
Dim rst As Recordset
with this:
Dim rst As DAO.Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vba317]

Thank you for sticking with me tonight. You have fixed this I really appreciate all your help.

Tom

 

[vba317]

This morning I ran the code and I get no more errors but I am not getting the data that I want. I need to add another statement to the where clause. From the query it is AND ((DAT_ReportData.bill)=-1)) I have tried to add this to the SQL.
I keep on getting error 424 Object required. Any help would be appreciated.

I added a variable intChBx and dimmed is as an integer
The field bill from the table DAT_ReportData is a yes/no field.

intChBx = -1
   'Change where to having and move above inner join
'strSQL = "SELECT DatRpt.uci, DatRpt.rptpd, DAT_Pats.PatName as 1,DAT_Pats.AcctNu as 2," & _
'        " DatRpt.doschg as 3,DatRpt.ChgAmt as 4,DatRpt.pmts as 5" & _
'        " FROM DAT_ReportData DatRpt" & _
'        " INNER JOIN DAT_Pats ON DatRpt.aid = DAT_Pats.aid" & _
'        " WHERE DatRpt.rptpd=" & CStr(liPdID) And DatRpt.bill = " & CStr(intChBx)" & _
'        " ORDER BY DAT_Pats.PatName,DatRpt.doschg"