Multiple List Boxes as Selection Criteria for Report 1

[tigercrap]

Hi,

I have been stuck on this for a few days and can not seem to get any where. I have a Criteria Selection form with 4 list boxes and date range text boxes that are used to open up a report. It works great when using only one list box and the date range, but not more than one. I followed FAQ181-5497, but i can not get it to work.

Thanks in Advance!
Tim in Iraq

 

[FancyPrairie]

The FAQ you mentioned should work fine. You just need to make sure your Tag Properties are setup correctly for each of your listboxes and date range.

Start by getting just one list box to work.

Say your default query for the report is Select * from tblEmployee;.

Now say one of your list boxes represents departments and the rowsource for the list box is Select lngID, strDeptName from tblDept. If you were going to create a query that just displays employees from the IS department (assume ID assigned to that department is 320), then your query would look like this Select * from tblEmployee Where tblEmployee.lngID = 320. The tag property for the list box would then look like this:

Where=tblEmployee.lngID,Long;

or, optionally, it could look like this:

Where=tblEmployee.lngID,Long,=;

Open the report like this:

Docmd.OpenReport "yourReportName",acPreview,,BuildWhere(Me)

To see what BuildWhere returns, instead of issuing the Docmd.OpenReport, issue this command: MsgBox BuildWhere Me. In this case it will return something like this: (tblEmployee.lngID In (320))

 

[tigercrap]

Thanks FancyPrairie! I started out with one list box and the date range. When the report opens, the list box filters perfect except the date range. I checked that the range boxes had _BeginR and _EndR and the BeginR box has the following tag: Where=BaghdadMaint.DateIn,Date; Any suggestions.

Thanks again for taking your time to help me out!
Tim in Iraq

 

[FancyPrairie]

You're right. The date range doesn't work any more. I think what happened is that I had a problem with the option group and, so, modified the code to fix it. In doing so I think I messed up the data range. Anyway, recopy and paste the code from the FAQ. It should work ok now.

 

[tigercrap]

Recopied the edited code from the FAQ and got rt error 2465 "MS Acces can't find the field 'StartDate_EndR' referred to in expression."

 

[FancyPrairie]

I just tested it and if your prefix for the EndDate control is not the same as the BeginDate control, you will get that error message. (Got called to a meeting in the middle of this sentence). It appears that your BeginDate control name is StartDate_BeginR. Therefore, the name of your EndData Control should be StartDate_EndR. Note that the prefix is the same (StartDate) only the suffix changes (BeginR or EndR)

 

[tigercrap]

How minor! Thank you very much! Now I get a rt error 3075 missing operator in query expression. I tried:

DoCmd.OpenReport....BuildWhere(Me, " AND "). No luck.

Not sure what I have wrong. I will be in a couple of hours as it is 2100 over here.

Thanks again for you help!
Tim in Iraq

 

[FancyPrairie]

Try this to see what it returns. Could be your Select statement is wrong.

Instead of: Docmd.OpenReport...BuildWhere(Me," AND ")

try this:

Msgbox BuildWhere Me, " AND "

It should return something like this:
(BaghdadMaint.DateIn Between #12/21/2004# AND #12/25/2004 23:59:59#)

If not, use debug to step thru the code.

 

[tigercrap]

The Msgbox returned:
(BaghdadMaint.DateIn Between #1/1/2004# AND #1/1/2004 23:59:59#).

But when I use the Doc.OpenReport...BuildWhere(Me," AND ")

I still get the rt error 3075, missing operator.

Thanks!
Tim in Iraq

 

[FancyPrairie]

The msgbox is returning the information correctly. I would say the problem is in the query you are using for the report. What does the query for your report look like?