Specify Criteria for the Report

[MagnumVP]

Here is what I have. I have 2 queries that I use to create a form. That form has 4 combo boxes that indicate the criteria that I want to search for.

Example: I want to search for a (1) 27 year old, (2) Male, (3) Black Hair, (4) Living in New York.

I want those criteria to be pasted to the report. Any thoughts on how this could be done?

If I go into the design view of the queires I can specify these results, but I'm trying to make this as idiot proof as possible, because I'm not the only one going to be using this DB.

 

[LarryDeLaruelle]

Add an unbound control to the report for each value.

Set the control source to:

=Forms!frmName.cboName.Column(1)

This assumes that the combo has only two columns -- a key field and and a display value. If you are using only a single column eliminate the .Column(1). Of course, if the display column is not the second column (combo columns are zero based) then adjust the (N) accordingly.

HTH

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[MagnumVP]

Thanks for all the help. It worked PERFECT!!!

Now I'm having this issue.

I've added a Checkbox next to one of the combo boxes (Check13). This combo box has three values. New = 1, Old = 2, Current = 3. The Check box specifies that I want to see the New and Old NOT Current. I've tried <> 3 and NOT 3 and = 1 or 2 but nothing seemed to work.

I've also tried this

IIf([forms]![Query
Select]![Check13]=-1,([Query1].[StatusId])<>3,([Query1].[StatusId]) Like "*" & [forms]![Query Select]![Text15] & "*")

What do you think?

 

[LarryDeLaruelle]

Just to make sure I'm clear on what you are doing.

1. You are using query by form where you are referencing the form's controls from the query to obtain the filter criteria.

2. There is a column/field in the query where you are referencing the combo for StatusID (New; Old; Current) by it's numeric value.

3. You want to give the user the option of using the check box (which is NOT in the data source/table) to select two of the StatusIDs from the combo.

4. You are using the Immediate If (IIf) in the query to make the modification in 3 above.

If that sums up what you are attempting, I don't think its doable. This type of multi-choice setup requires building the query using SQL:

SELECT Field1, Field2, etc FROM TableName WHERE StatusID <> 3 and 'other criteria' (This is psuedo code, it requires a bit more syntax than I've shown.)

And then use a DoCmd to actually create the query.

If you are interested, I have a writeup on how to do this that I use in class which I can send you.

Let me know.



Larry De Laruelle
ldelaruelle@familychildrenscenter.org