Report Criteria Help

[amerfeld]

Hi All,
I have tried to find help on this with no luck. I have a form that prompts a user for criteria, then runs a report (based on a set of queries), dependent upon the user's entry. More specifically the form gives the user the option of choosing "Yes - include all paint numbers" or "No - include a specific number". If "No" is chosen the user enters the paint number into a combo box and clicks a button to run the report.

My problem is I cannot figure out how to run the query based upon either option. Any idea? I'm leaning toward an IIF statement but not sure how to use it.

 

[Matt Greatorex]

Behind the on click property of the button, in VBA, put :

DoCmd.OpenReport {report name}, {where clause}

The report name speaks for itself. Presumably, the same basic report is being opened for each situation and only the criteria changes?
The where clause will be whatever criteria you wish to apply on that occasion e.g "[Paint] = 'red'".

Any help?

 

[amerfeld]

Thanks Matt:
I have gotten this far with no luck. Here is the code behind the button.

Dim stDocName As String

stDocName = "rptLiquidBacklog"
DoCmd.OpenReport stDocName, acPreview, , tblpaintno.PaintNo = Forms! frmliquidbacklogreport!Combo2
I get an "object required" error when I try to run this.

If I take out tblpaintno and just leave PaintNo, the report will run all paint numbers if I ask it to, so that works, but it doesn't grab the paint number if I enter one.

Any ideas?

 

[Matt Greatorex]

It's expecting the where clause as a string, so you need quotation marks. Also, the value it's grabbing from the form need to be outside the quotes. Try:

Dim stWhere as String
Dim stDocName As String

stDocName = "rptLiquidBacklog"
stWhere = "[tblpaintno].[PaintNo = " & Forms![formliquidbacklogreport].Combo2

DoCmd.OpenReport stDocName, acPreview, , stWhere

 

[Matt Greatorex]

Just had another thought. I know it says number in the title, but if the value for Forms![formliquidbacklogreport].Combo2 is stored as text, try the following, instead (just puts quotation marks around the value):

stWhere = "[tblpaintno].[PaintNo = '" & Forms![formliquidbacklogreport].Combo2 & ''"

 

[amerfeld]

Hi Matt:
Thanks for hanging with me. I am having no luck, however. I have been playing around with this but no success. You were right that the paintno field is text. Here is what I have (note I added a bracket and changed the end & part because I was getting a compile error).

varCriteria = "[tblpaintno].[PaintNo] = '" & Forms!frmliquidbacklogreport.Combo2 & "'"

At this point I am prompted for tblpaintno.paintno parameter when the report runs. When I enter an existing paint no (just to see what will happen) there is no information on the report.

I am also wondering if another problem is I have other criteria in both queries. Also, in the first query I have is not null in the paint no. In the second query I have no criteria in the paint number field.

Here is the where statement I have in my second query if this helps.

WHERE (((tblPOMst.CreateDate) Is Not Null) AND ((tblPricing.CstDesc)<>"min" And (tblPricing.CstDesc)<>"setup") AND ((tblOrderStatus.OrderStatusID)=1 Or (tblOrderStatus.OrderStatusID)=5));

Thanks again for your help!

 

[Matt Greatorex]

Oops! Sorry about the compilation error. Should have tested it before posting it.

The fact that you're prompted for tblpaintno.paintno when the report opens could be a few things:
Is the report information definitely coming from the tblpaintno table?
Is there definitely a field called paintno in this table?

At the moment, all I can think is that - - as you hinted at - something in the one set of criteria contradicts the results of the other. Do both sets of criteria return something, if run in isloation? If not, there's your problem. If so, try adding criteria, one item at a time until it does return nothing, to identify your problem area.

Hopefully, this might help a bit?

 

[amerfeld]

Matt:
Ok. I have to admit you were right about the table. Should have known that, but sometimes helps to get a "knock, knock puddin' head". This is what I ended up with:

Dim stDocName As String
Dim varCriteria As String

If Combo2 = "" Then
stDocName = "rptLiquidBacklog"
varCriteria = "[PaintNo] = '" & Forms!frmliquidbacklogreport.Combo2 & "'"
DoCmd.OpenReport stDocName, acPreview
Else
stDocName = "rptLiquidBacklog"
varCriteria = "[PaintNo] = '" & Forms!frmliquidbacklogreport.Combo2 & "'"
DoCmd.OpenReport stDocName, acPreview, , varCriteria
End If

This works wonderfully. Thanks for all your help!

 

[Matt Greatorex]

Not a problem, glad it works