How to use DoCmd.OpenReport with an inclusive WHERE condition

[diddydustin]

Hey guys,

I'm trying to do this:

DoCmd.OpenReport "EMR_Time_Report", , , "MACHINE = " & Me!empMachine.Value & _
" AND Shift = " & Me!empShift.Value & " AND Date = " & Me!empDate.Value, , acWindowNormal

The problem is, I want the user to be able to select either the Machine, Shift, or Date, but not have to select all three. So if they select Shift 3 and nothing else, it will return all dates on all machines for shift 3.

When I am tring to run that DoCmd.OpenReport, it is returning an error because the WHERE critera will look like this:

MACHINE = AND SHIFT = 3 AND DATE =

How can I set up the WHERE critera so the user doesn't have to select all three values and can instead choose one or two?

Thanks
Dustin

 

[dRahme]

Hi, personally I would define the 'where' clause somewhere else. I like option groups. Create an option group on a form with four choices - machine, shift, date, unsorted.

After update of the option group open the report.

In the On Open event of the report, create the recordsource for the report.

dim Stsql as string

stsql = your sql without the where clause

select case form!yourForm.frmOptionGroup


case 1 ' machine

stsql = stsql & "where yourmachine = 1;"

case 2....

end select

me.recordsorce = stsql


There's many ways to skin a cat and I find this to be one of the handiest.


Hope that helps.

 

[dhookom]

Try something like:

Dim strWhere as String
strWhere = "1 = 1 " 'now I can use AND
If Not IsNull(Me.empMachine) Then
   strWhere = strWhere & " AND MACHINE = " & Me!empMachine
End If
If not IsNull(Me.empShift) Then
   strWhere = strWhere & " AND Shift = " & Me!empShift
End If
[Red]
'etc for all other potential criteria
'remember to delimit text values with quotes and 
'  date values with "#"
[/Red]
DoCmd.OpenReport "EMR_Time_Report", , , strWhere, , acWindowNormal

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[diddydustin]

Duane,

What a clever idea! Thank you so much. My last question-- what would be the best solution to use this one front end form across multiple reports, instead of duplicating the form for each seperate report?

Thank you
Dustin

 

[dhookom]

In most applications, I create a single form with a bunch of controls from date text boxes to multi-select list boxes to option buttons and combo boxes. I have created a table of reports with the object name as well as a "user friendly" title and a few other fields. I present the titles in a list box so the user can select a report, enter criteria, set the mode, etc. I even keep a table that ties reports with specific controls since not every report depends on dates or whatever. When a report is selected in the list box, some controls are disabled while others are enabled. This gives the user an indication of what controls can or should be used to filter the selected report.

Then when they choose to run the report, I have code that loops through all the controls on the form. If a control is enabled and has a value, it is added to the "strWhere". Most reports can be filtered based on multiple selections from a list box so I use just one function to handle all list boxes.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.