Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 610
  • Last Modified:

DoCmd.OpenReport Syntax Error (Comma in query expression)

Trying to run a report from VBA.  The query it uses asks for a parameter.  I'm trying to pass the parameter into the query and getting a syntax error.  The parameter/variable is set prior with a recordset number.

Here's the VBA code:
Dim lngInvoiceNumber As Long
lngInvoiceNumber = rs("tblRepat.Invoice_number")

DoCmd.OpenReport "InvOne", acViewNormal, qryOneInvoice, "[tblRepat.Invoice_number] = " & lngInvoiceNumber & ", , lngInvoiceNumber"

I get an error:
Syntax Error in (comma) in query expression '[tblRepat.Invoice_number] = 423,,lngInvoiceNumber

Using Access 2007

What I'm doing is:
Change the printer to pdf
Pulli a list of all invoices for the day
Looping thru the recordset and converting each invoice to a pdf.  
The query for the report requires an invoice number.

JS
0
jshesek
Asked:
jshesek
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:

try this first and post your comment

'open report in preview mode

DoCmd.OpenReport "InvOne", acViewPreview, , "[Invoice_number] = " & lngInvoiceNumber

'i am assuming that the record source of the report is table "tblRepat" or a query based on that table
0
 
jshesekAuthor Commented:
tblRepat is 1 of the tables the query "qryOneInvoice" pulls from.  
The report InvOne has a data source of qryOneInvoice.  

qryOneInvoice is:
SELECT tblRepat.Invoice_number, tblRepat.[Customer Key Cod], tblRepat.[Invoice date], tblItems.Invoice_number, tblItems.Items, SUM(tblItems.Item_Amt) AS Item_Amt
FROM tblRepat INNER JOIN tblItems ON tblRepat.Invoice_number=tblItems.Invoice_number
WHERE (((tblRepat.Invoice_number)=[ENTER INVOICE NUMBER]))
GROUP BY ;

It came up asking for which invoice_number do you mean - so ran this:
DoCmd.OpenReport "InvOne", acViewPreview, , "[tblRepat.Invoice_number] = " & lngInvoiceNumber

Now a popup window comes up with Enter Invoice Number.  I was hoping lngInvoiceNumber would be passed along.

0
 
Rey Obrero (Capricorn1)Commented:
you have to remove the Where clause in qryOneInvoice,

SELECT tblRepat.Invoice_number, tblRepat.[Customer Key Cod], tblRepat.[Invoice date], tblItems.Invoice_number, tblItems.Items, SUM(tblItems.Item_Amt) AS Item_Amt
FROM tblRepat INNER JOIN tblItems ON tblRepat.Invoice_number=tblItems.Invoice_number

try again
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now