Report based on parameter query

[ekta]

Hi:

I have created a query which contains a parameter. The field in the query where I entered the parameter can have 2 values--Active or Retired. Based on this query I created a report.
When the user opens the report the parameter dialog box pops up and he can enter Active or Retired. So far its ok. What if the user wants to see ALL the records whether it is Active or Retired? How should I handle this?
Secondly, I want whatever value user enters in the parameter dialog box should be displayed in the Report Header. I dont know how to do this?

Thx,
Ekta

 

[bjackson]

unless you want to go into a bit of
sql and query by form you could acheive your aim by creating 3
reports with 3 querys.Then run The reports of a form

in this form create an optiongroup control
you need 3 options

1.show active-give it a value of 1

2. show retired-give it a value of 2
3. show all-give it a value of 3


in the afterupdate event for this control

if me![thenameyougive the option group].value=1 then
docmd.openreport"Showactive",acpreview
exit sub
end if

if me![thenameyougive the option group].value=2 then
docmd.openreport"Showretired",acpreview
exit sub
end if

if me![thenameyougive the option group].value=3 then
docmd.openreport"ShowAll",acpreview
exit sub
end if

this way there is no prompting for any criteria

 

[ekta]

Hi bjackson:

Thx a lot for your help. It works great.

Can you also tell me the other way of doing this. I wanna
learn.
One other question that I have is that right now I have this option group and the user selects from it. But what if I want to add one more criteria, which I have to in near future. For example, my report has BusinessLead field. At a later stage user should also be able to enter the businessLead name. So if the user selects Active AND enter a BusinessLead name it should show only those records which match this criteria.

Thx again
Ekta

 

[bjackson]

what you want to do is QBF-query by form
it is to complex for me to explain it,you would be better
off looking up the article at m/soft knowledge base site

as for your option group another way is
and probably better and easier way to follow
in the code is use select case
dim SNo as long
Sno=me![optiongroupname].value

select case SNo

case 1
docmd.openreport"Showactive",acpreview

case 2
docmd.openreport"Showretired",acpreview
case 3
docmd.openreport"ShowAll",acpreview

case 4

case 6

case 7 etc etc
case else
in here add your last case
end select

your code will work faster with this as it only
reads the cases until it finds the value its looking
for

in the case statement your could then put
the sql statement that you want to run,thus
you could use just the 1 report for a lot of
different cases

 

[ekta]

Thx again.. I will try it