setup a report to pull mutilple peoples data.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to set up a report that will provide me with the data for only
certain employees but on the same report. I have setup the query using the
In() expression in the criteria and that works fine. However, I have to
enter a value for each time I have requested data, even if it is just a null
value. My In() statement reads:

IN([Enter 1st CSR Name], [Enter 2nd CSR Name], [Enter 3rd CSR Name], [Enter
4th CSR Name], [Enter 5th CSR Name], [Enter 6thCSR Name])

I will be setting up a form to fill in the names upon running the report. I
just need to how to set it up to enter a null value if I don't choose a name.
Example, I have the posibility to enter 6 names but I only need to pull
information on 4 names. How do I set it up to enter a null value for the
last two names?
 
Don't put criteria in the query, instead have the report
show everything and use use the WHERE clause of the
OpenReport action to limit the records

DoCmd.OpenReport reportname[, view][, filtername][,
wherecondition]

ie:

DoCmd.OpenReport "MyReport", acViewPreview,, "city IN
('Denver','Chicago')"

'----------------------
dim mCriteria as string, varItem
mCriteria = ""
For Each varItem In listbox_controlname.ItemsSelected
mCriteria = mCriteria & "'" _
& listbox_controlname.ItemData(varItem) _
& "', "
Next varItem

'Take the comma off the end and print
if len(mUserCriteria ) > 0 then
mCriteria = _
left(mCriteria , len(mCriteria )-2)
DoCmd.OpenReport "MyReport", _
acViewPreview,, _
"fieldname IN (" & mCriteria & ")"
else
'no criteria specified
DoCmd.OpenReport "MyReport", _
acViewPreview
end if
'----------------------

if your criteria is numbers, remove the ' delimiters

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com
 
Back
Top