Problem using a where statement in print code

[Michael57]

When I use the following code:
Private Sub Command6_Click()
Dim PrintQuoteNo As Integer
PrintQuoteNo = StartQuoteNo
While PrintQuoteNo <= EndQuoteNo
DoCmd.OpenReport "QuotePrint", acViewNormal, "QuoteMaster Query", "Where PrintQuoteNo < EndQuoteNo+1"
PrintQuoteNo = PrintQuoteNo + 1
Wend
End Sub

I get the following error:
Run-time error '3075'
Syntax error(missing operator) in query expression (Where PrintQuoteNo , EndQuoteNo+1)

 

[Remou]

I think this is what you want:

While PrintQuoteNo <= EndQuoteNo
   DoCmd.OpenReport "QuotePrint", acViewNormal, "QuoteMaster Query", "PrintQuoteNo = " & PrintQuoteNo 
   PrintQuoteNo = PrintQuoteNo + 1
Wend

But that's a bit of a guess, because of the query.

 

[Michael57]

Remou
I get this error now.
Runtime error 3075
Extra ) in query expression '(PrintQuoteNo=)'.

 

[Remou]

I think that the error is coming from the query. You could try:
[tt]DoCmd.OpenReport "QuotePrint", acViewNormal, ,"PrintQuoteNo = " & PrintQuoteNo[/tt]

Or, if you need the query, perhaps you could post the SQL.

 

[Michael57]

Remou here is the sql
SELECT QuoteData.QuoteNo, QuoteData.[Job#], QuoteData.Date, QuoteData.Customer, QuoteData.Contact, QuoteData.[Part#], QuoteData.[Part Name], QuoteData.[Eng Lev], QuoteData.[Blank Length], QuoteData.[Blank Width Prog], QuoteData.[Comment 1], QuoteData.[Comment 2], QuoteData.[Comment 3], QuoteData.[Comment 4], QuoteData.[Comment 5], QuoteData.Terms, QuoteData.Delivery, QuoteData.FOB, QuoteData.Currency, QuoteData.Quantity, QuoteData.Description, QuoteData.Length, QuoteData.Width, QuoteData.[Shut Height], QuoteData.[Price CDN], QuoteData.[Selling Price], QuoteData.[Op#], QuoteData.[Estimate#], QuoteData.Address, QuoteData.[Town City], QuoteData.[Postal Code]
FROM QuoteData
ORDER BY QuoteData.[Op#];

 

[Remou]

I think I have confused both of us! The query above seems to be the one that is behind the report, it is QuoteMaster Query that you seem to be using as a filter that I was wondering about. From the above, it does not seem necessary, so perhaps it could be left out? If not, it may be possible to add the Where statement to the filter query. That is why I wondered about the SQL.

When I made my suggestion for the OpenReport statement, I was guessing at names, it now seems that the name of the field should be QuoteNo, so perhaps you could try:
[tt]DoCmd.OpenReport "QuotePrint", acViewNormal, ,"QuoteNo = " & PrintQuoteNo[/tt]
Which should give you a single page report for each PrintQuoteNo between StartQuoteNo and EndQuoteNo.

If you want a multi-page report showing all quotes between StartQuoteNo and EndQuoteNo, you will need to change your code a little.

 

[Michael57]

Yes I want a multipage report using the report Quote Print for all the quote numbers in the range. I was trying to use the where statement but have had no luck can you help me.

 

[Remou]

This, with some error coding, should be sufficient:

Private Sub Command6_Click()
DoCmd.OpenReport "QuotePrint", acViewPreview, , "QuoteNo Between " & StartQuoteNo & " And " & EndQuoteNo
End Sub

I have changed the view to acViewPreview for testing.
If you are getting StartQuoteNo and EndQuoteNo of a form, it is best to say so, for example:
Me.StartQuoteNo
Or
Forms!frmForm.StartQuoteNo