Hiya folks!

I have a report which shows a list of all menu items sold in a restaurant. Each menu item belongs to a specific category, such as fish dishes, meat dishes and desserts.

Before accessing the report, the user is asked to select the dates for the period that he wants to view in the report, and the specific categories that he wants to view.

I have been able to do all of this. The problem is, let us assume that the user wants to view all the categories in the report (i.e. the user selects the dates, but leaves the combo blank. This is what I tried in the query of the "category" field in the report (frmSelectDatesSingle is the form where the user would select the data he wishes to view):

[Forms]![frmSelectDatesSingle]![Category]

I also tried the following but I am not sure what to put in the "True" part of the statement:

iif([Forms]![frmSelectDatesSingle]![Category]=Null;*****;[Forms]![frmSelectDatesSingle]![Category])

Hope you can help!

Thank you!!!

I would try to pass this part of the parameter in a WHERE clause in the OpenReport line.

Dim sCategory As String

If IsNull(Me.cboCategory) or Me.cboCategory = "" Then
   sCategory = ""
Else
   sCategory = Me.cboCategory
End If

If sCategory = "" Then
   sLink = sCategory
Else
   sLink = "[Category] = '" & sCategory & "'"
End If

DoCmd.OpenReport sReportName, , sLink

Make sure you put the sLink reference in the WHERE clause of the DoCmd. I am not sure if it is the second or third place here, but autosense will tell you the right one.

This allows the user to pass a null value.

You may also want to add this line after the DoCmd. line above:

Me.cboCategory = ""

This will reset the category combo so that if the user pulls meats the first time, and then wants to see everything, they will have a blank combo to allow them to do this.

Did that help?


mmcdonal

Thanks for your reply!

I read the code and it looks like it will help. But I am not so sure where to create this or to plug it in.

Sorry but I am not so good a code. If you can tell me where or how to create this it would be a great help.

Thank you!

I am assuming that in order to open this report, the user selects values from some combo boxes on a form, and then clicks a button to open the report. This code should be part of the On Click event of the button to open the report.

Use the button wizard to creat the button to open the report, and then add this code between the Dim statements at the top, and the DoCmd.OpenReport statement at the bottom. Then replace the Dim sLinkCriteria line with

Dim sCategory As String
Dim sLink As String

Then on the DoCmd line, remove the word sLinkCriteria and replace it in the same place with sLink.

Did that help?

mmcdonal