Results 1 to 7 of 7
  1. #1
    jabadoojr is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Dec 2012
    Posts
    6

    Compile Error: Expected: list separator or )

    Hi All,

    I found code online to help me split and export one Access report into several PDFs on a shared drive. The code is entered in the "On Click" event of a button on the report. First, I declared a variable in a Module as Public. Then I put some code on the "On Open" and "On Close" events on the report (just let me know if you'd like to see that code). I'm a novice at VBA, so there may be many errors in the code (please bear with me). The problem seems to be in the SQL aspect of the code. I get the error:

    "Compile Error:

    Expected: list separator or )"


    with the word "cash" (in the code) highlighted. Any help is much appreciated!!! Here is the code:

    Private Sub Command43_Click()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As QueryDef

    Set db = CurrentDb

    Set rst = CurrentDb.OpenRecordset ("SELECT DISTINCT [PARISHUPDATE.FundID] FROM PARISHUPDATE LEFT JOIN OPENPLEDGES ON (PARISHUPDATE.FundID = OPENPLEDGES.FundID) AND (PARISHUPDATE.GiftDate = OPENPLEDGES.GiftDate) AND (PARISHUPDATE.ConstituentID = OPENPLEDGES.ConstituentID) AND (PARISHUPDATE.GiftAmount = OPENPLEDGES.GiftAmount) AND (PARISHUPDATE.PledgeBalance = OPENPLEDGES.PledgeBalance)WHERE ((PARISHUPDATE.GiftType)="cash" Or (PARISHUPDATE.GiftType)="pledge" Or (PARISHUPDATE.GiftType)="stock/property") AND ((PARISHUPDATE.CampaignID)="embrace"));",dbOpenSna pshot)

    Do While Not rst.EOF
    strRptFilter = "[FundID] = " & Chr(34) & rst![FundID] & Chr(34)

    DoCmd.OutputTo acOutputReport, "Commitments", acFormatPDF, "H:\Development\AOB Capital Campaign planning\Stewardship & Cultivation\Parish Update Reports\Report Destination" & "\" & rst.Fields("FundDescription") & Format(Date, "mmddyyyy") & ".pdf"


    DoEvents
    rst.MoveNext
    Loop

    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
    End Sub

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    Why don't you build an Access query first? Build one and place the query name in your DAO recordset.

    set rst = CurrentDb.OpenRecordset ("qryMyTestQuery", dbOpenSnapshot)

    If you test the query and it works outside of your VBA, then that can help you troubleshoot the reat of your code. Then, you can view the SQL in your named query and paste the SQL in your DAO recordset. Try and run the code then. If everything works, then you can get rid if your test query and just depend upon your SQL in your VBA module.

  3. #3
    jabadoojr is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Dec 2012
    Posts
    6
    Hey ItsMe, this got me past the big obstacle that was hindering my progress. Thanks! I have a few more issues to get past, but I'm much closer to completing the task. Thanks again!!!

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I see you have a solution, but, in answer to your question, you are missing a space in front of "WHERE".

    ...AND (PARISHUPDATE.PledgeBalance = OPENPLEDGES.PledgeBalance)WHERE ((PARISHUPDATE.GiftType)="cash" Or...
    Should be
    ...AND (PARISHUPDATE.PledgeBalance = OPENPLEDGES.PledgeBalance) WHERE ((PARISHUPDATE.GiftType)="cash" Or...

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows XP Access 2003
    Join Date
    Aug 2013
    Posts
    7,862
    As steve pointed out there is an issue with your original SQL. My suggestion was simply a way to focus your energy in one area at a time. It is simply an aproach I sometimes use in complex procedures. Just a way to avoid playing Whack-A-Mole

  6. #6
    jabadoojr is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Dec 2012
    Posts
    6
    Hey Steve,

    Although that may be one issue with the code, it didn't seem to solve the problem. I put the space in, and got the same error. Any thoughts?

    Thanks,
    Joe

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It sounded like you had a resolution to your problem, so I didn't look at the code to closely.

    You will also error on the words "pledge", "stock/property" & "embrace".

    When you have text literals inside of a string (the select string), you have to use single quotes.
    This won't work: "SELECT ... FROM..WHERE (something = "this" OR something = "that")"
    This will work: "SELECT ... FROM..WHERE (something = 'this' OR something = 'that')"

    I re-wrote your sub to make it easier to read (at least for me ) I use a string variable to create the SQL; then I can use the "Debug" command to print the SQL to the immediate window to see if it is formed correctly. If I have a question or want to test the SQL I can copy the string from the immediate window and paste it into a new query.

    I did the same for the "OutputTo" command for the out put file.

    And I commented out the querydef statements. You aren't using querydefs.

    Here is the code:
    Code:
    Private Sub Command43_Click()
       Dim db As DAO.Database
       Dim rst As DAO.Recordset
       '   Dim qdf As QueryDef
       Dim sSQL As String
       Dim outFile As String
    
       Set db = CurrentDb
    
       sSQL = "SELECT DISTINCT [PARISHUPDATE.FundID]"
       sSQL = sSQL & " FROM PARISHUPDATE LEFT JOIN OPENPLEDGES"
       sSQL = sSQL & " ON (PARISHUPDATE.FundID = OPENPLEDGES.FundID) AND (PARISHUPDATE.GiftDate = OPENPLEDGES.GiftDate)"
       sSQL = sSQL & " AND (PARISHUPDATE.ConstituentID = OPENPLEDGES.ConstituentID) AND (PARISHUPDATE.GiftAmount = OPENPLEDGES.GiftAmount)"
       sSQL = sSQL & " AND (PARISHUPDATE.PledgeBalance = OPENPLEDGES.PledgeBalance)"
       sSQL = sSQL & " WHERE ((PARISHUPDATE.GiftType) = 'cash' Or (PARISHUPDATE.GiftType) = 'pledge' Or (PARISHUPDATE.GiftType) = 'stock/property')"
       sSQL = sSQL & " AND ((PARISHUPDATE.CampaignID) = 'embrace'));"
       '   Debug.Print sSQL
    
       Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot)
    
       Do While Not rst.EOF
          strRptFilter = "[FundID] = " & Chr(34) & rst![FundID] & Chr(34)
    
          outFile = "H:\Development\AOB Capital Campaign planning\Stewardship & Cultivation\Parish Update Reports\Report Destination" & "\" & rst.Fields("FundDescription") & Format(Date, "mmddyyyy") & ".pdf"
          '      Debug.Print outFile
          DoCmd.OutputTo acOutputReport, "Commitments", acFormatPDF, outFile
          DoEvents
          rst.MoveNext
       Loop
    
       rst.Close
       Set rst = Nothing
       '   Set qdf = Nothing
       Set db = Nothing
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Compile error: Expected: list separator or )
    By ThatWumboGuy in forum Programming
    Replies: 2
    Last Post: 08-03-2013, 10:33 PM
  2. Compile Error: Expected: list separator or )
    By Kirsti in forum Programming
    Replies: 2
    Last Post: 03-27-2012, 02:52 PM
  3. Replies: 6
    Last Post: 11-24-2011, 08:38 PM
  4. Replies: 8
    Last Post: 01-19-2011, 04:48 AM
  5. compile error: expected end of statement
    By RedGoneWILD in forum Programming
    Replies: 5
    Last Post: 07-29-2010, 10:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums