Thanks for your quick reply. Your code looks close. The only trick is that
I want them to be able to choose a dropdown option called ALL under Region
and I want the query to read that as if no region was chosen (so all regions
will be reported).
--
Thanks
You all are teaching me so much
"Brian" wrote:
[Quoted Text] > Put code like this on the button that runs the report:
>
> Private Sub MyButton_Click()
> If not isnull(Region) and not isnull(agency) then
> Msgbox "You cannot select both a region and an agency."
> agency.setfocus
> end if
> End Sub
>
> On the region, you could just let them leave it blank for all or select any
> single one. Then, modify your query to assume that if they did not select a
> region or agency, they want all regions.
>
> Your SQL will end up like this:
>
> SELECT qryEventsInfo.Reg, qryEventsInfo.Agency, qryEventsInfo.EventDate
> FROM qryEventsInfo
> WHERE
> (forms!frmSiteVisitByDateVendorParam!ChooseVendor Is Not Null AND
> qryEventsInfo.Agency)=forms!frmSiteVisitByDateVendorParam!ChooseVendor)
> OR (forms!frmSiteVisitByDateVendorParam!ChooseRegion Is Not Null AND
> qryEventsInfo.Reg)=forms!frmSiteVisitByDateVendorParam!ChooseRegion) OR
> (forms!frmSiteVisitByDateVendorParam!ChooseVendor Is Null AND
> forms!frmSiteVisitByDateVendorParam!ChooseRegion Is Null);
>
> Hopefully, I typed all that correctly! I think perhaps you want this:
>
> 1. If there is an Agency, limit query to that Agency.
> 2. If there a Region, limit query to that Region.
> 3. If there is neither, show all records.
>
> "knowshowrosegrows" wrote:
>
> > So I have a simple report that is fronted by a simple parameter form. On the
> > form they choose an agency from a drop down OR a region and then put in a
> > date range. I do not want them to be able to run the report by both a region
> > and an agency.
> >
> > If they choose to run the report according to a region, I want them to be
> > able to choose region 1,2,3,4,5 OR ALL the regions.
> >
> > I have a qry that will run the report by agency OR region. The SQL is below:
> >
> > SELECT qryEventsInfo.Reg, qryEventsInfo.Agency, qryEventsInfo.EventDate
> > FROM qryEventsInfo
> > WHERE
> > (((qryEventsInfo.Agency)=forms!frmSiteVisitByDateVendorParam!ChooseVendor))
> > Or (((qryEventsInfo.Reg)=forms!frmSiteVisitByDateVendorParam!ChooseRegion));
> >
> > What I don't know how to code in is the restriction that they can't put in
> > both an agency and a region parameter (I need a message box to pop up). I
> > also don't know how to code that if they choose ALL from the region drop
> > down, they can run a report for all the regions.
> >
> > As always - any help is a gift.
> >
> > --
> > Thanks
> >
> > You all are teaching me so much
|