Syntax error

VGuerra67
VGuerra67 used Ask the Experts™
on
Syntax error for my strSQL string.


Dim db As DAO.Database
Dim rs, rs2 As DAO.Recordset
Dim strSQL, RIGACCT, ClientID, dispname, comp, acctno, bal, exp, expdel, EXPDELDATE, eq, eqdel, EQDELDATE, tu, tudel, tuddate As String
Dim pudate, credotes, newpudate As String

strSQL = "SELECT CREDITREPORT.RIGACCT_FK, CREDITREPORT.CLIENTID_FK, CREDITREPORT.DISPLAYNAME, CREDITREPORT.COMPANYNAME, CREDITREPORT.ACCOUNTNUMBER, CREDITREPORT.BALANCE, CREDITREPORT.EXPERIAN," _
        & " CREDITREPORT.EXPERIANDEL, CREDITREPORT.EXPDELDATE, CREDITREPORT.EQUIFAX, CREDITREPORT.EQUIFAXDEL, CREDITREPORT.EQDELDATE, CREDITREPORT.TRANSUNION, CREDITREPORT.TRANSUNIONDEL, " _
        & "CREDITREPORT.TUDELDATE, CREDITREPORT.PULLEDDATE, CREDITREPORT.CREDREPORTNOTES, CREDITREPORT.NEWPULLEDDATE " _
        & "FROM CREDITREPORT" _
        & "WHERE (((CREDITREPORT.CLIENTID_FK)= & '"[forms]![SubMc_CredRptDE1Frm]![CLIENTID_FK]"' & ) AND ((CREDITREPORT.PULLEDDATE)= & '"[forms]![SubMc_CredRptDE1Frm]![PULLEDDATE_Txt]"' & ));"



Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Analyst Assistant
Commented:
Try this.
strSQL = " SELECT CREDITREPORT.RIGACCT_FK, CREDITREPORT.CLIENTID_FK, CREDITREPORT.DISPLAYNAME, CREDITREPORT.COMPANYNAME, CREDITREPORT.ACCOUNTNUMBER, CREDITREPORT.BALANCE, CREDITREPORT.EXPERIAN," _
        & " CREDITREPORT.EXPERIANDEL, CREDITREPORT.EXPDELDATE, CREDITREPORT.EQUIFAX, CREDITREPORT.EQUIFAXDEL, CREDITREPORT.EQDELDATE, CREDITREPORT.TRANSUNION, CREDITREPORT.TRANSUNIONDEL, " _
        & "CREDITREPORT.TUDELDATE, CREDITREPORT.PULLEDDATE, CREDITREPORT.CREDREPORTNOTES, CREDITREPORT.NEWPULLEDDATE " _
        & " FROM CREDITREPORT " _
        & " WHERE (((CREDITREPORT.CLIENTID_FK)= '" & [forms]![SubMc_CredRptDE1Frm]![CLIENTID_FK] & "') AND ((CREDITREPORT.PULLEDDATE)= '" & [forms]![SubMc_CredRptDE1Frm]![PULLEDDATE_Txt] & "'));"

Open in new window

Most Valuable Expert 2012
Top Expert 2014

Commented:
Also be aware of your data types. For example, if CLIENTID_FK is a Numeric value you remove the single quotes from Norie's suggestion.

Author

Commented:
Thanks to both of you
Most Valuable Expert 2012
Top Expert 2014

Commented:
The syntax provided by Norie should resolve the issue

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial