Better filter/criteria (1 Viewer)

[anski]

I have a report that should show all colors except the colors indicated in 5 fields. My filter (found in Report Property Sheet) for the report is this:

color <> DLookUp("color1","tbl_color") and
color <> DLookUp("color2","tbl_color") and
color <> DLookUp("color3","tbl_color") and
color <> DLookUp("color4","tbl_color") and
color <> DLookUp("color5","tbl_color")

The above works but is there a better way to construct this filter?

 

[theDBguy]

Maybe try the InStr() function?

 

[CJ_London]

If your filter is permanent, include in the report recordsource as criteria instead

 

[Josef P.]

If tbl_color was normalized, this could work:

color not in (select color from tbl_color where ColorNo between 1 and 5)

 

[plog]

I liked Josef's solution but need to comment to:

What happens when you need to add a 6th color? Or subtract a color? Rhetorical question--you not only need to update your table structure but all your code that references that old table structure. A super pain for a simple change.

If instead tbl_color is a list of colors to exclude you should just have 1 field in it and then 5 records. That way if you need to add or subtract any it doesn't matter how many are in there--it finds and uses however many are there--you just add/delete records in tbl_color.

So to modify Josef's code, this is what you ultimately should aim for:

color not in (select color from tbl_color)

 

[anski]

I liked Josef's solution but need to comment to:

What happens when you need to add a 6th color? Or subtract a color? Rhetorical question--you not only need to update your table structure but all your code that references that old table structure. A super pain for a simple change.

If instead tbl_color is a list of colors to exclude you should just have 1 field in it and then 5 records. That way if you need to add or subtract any it doesn't matter how many are in there--it finds and uses however many are there--you just add/delete records in tbl_color.

So to modify Josef's code, this is what you ultimately should aim for:

color not in (select color from tbl_color)

I know what you are saying about one field and five records. i need separate fields because these 5 fields are used as columns for a report (5 columns). then in a subreport, these 5 "colors" should not be included anymore. my dilemma is, as you have mentioned, if in the future another "color"/column is added to the main report, i would have to update several objects.

 

[CJ_London]

i need separate fields because these 5 fields are used as columns for a report (5 columns).

use a crosstab query to convert from vertical to horizontal.

You are making the classic error of trying to apply excel principles to a database, resulting in much more complex coding to overcome the shortfalls.

Do not confuse data storage with data views