query criteria if null then like "*"

[mcfadwmc]

I have a query (lossdata) where i want the user to be able to set the criteria for the results

so i have a table called 'chooser' which has one record of several combobox's which will display the same data as is in the respective columns in the query

if the user selects one of the options in the combo box then i want the query criteria for the respective column to be equal to that but if it is null then show all values in that column

so i thought the query criteria in the product column would be something like

like iif([chooser]![product]isnull,"*",[chooser]![product])

but it doesn't work so i tried

iif([chooser]![product] is null, like "*",like [chooser]![product])

but that does't work so i tried

iif([chooser]![product] is null,[lossdata]![product]like "*",[lossdata]![product]like[chooser]![product]

still doesn't work.

any help would be much appreciated as i thought i had finally got my head around access by doing this but it seems i haven't

 

[Joe4]

Try just putting this in your Criteria field:

like [chooser]![product] & "*"

 

[mcfadwmc]

hello and thanks for the reply

it still doesn't work if there's no value in [chooser]![product]

if something is selected then it works fine but if the selection is left blank then the query doesn't show anything

it's like null is represented by something and so it's looking for null* rather than just *

 

[Joe4]

Please explain the nature of this Chooser table you have established.

How many records are in it?
How is it incorporated into your query (is there a join to your data table)?
When you have the issue, how many records are in the Chooser table, is it one record with some blank fields, or no records at all)? When I use one record with blank fields, the suggestion I made seems to work for me.

 

[mcfadwmc]

hello

chooser should always only have one record in it which has 8 fields.

product, asset, shift, shift2, area, fault, faulttype, action

each of these fields has it's record source set so it only shows inputs that will be in the same column of the query. for example asset will only show a list of assets which can be entered into the table that the query is looking at for the asset column.

microsoft have done this article on it which i think fixes it if i only needed to do this on one field but not on 8

http://support.microsoft.com/kb/209261

 

[Joe4]

OK, in your query how are you linking this Chooser table to your main data table (or are you doing a Cartesian Product with no link at all - which should work if you have an exactly one record table)?
Is there a defined relationship?

 

[mcfadwmc]

there no defined relationship. i was gonna link the chooser table to the query on each related field but then the null value will cause the query not to display anything

so i'm trying to filter the query using criteria so that if theres a selected product in the product field of the chooser table then it will only show records relating to that product in the query but if nothing is selected then it'll show all products... and

if i also choose an asset as well in the chooser table then the query will only display records where the product matches and so does the asset.

etc etc for 8 different selectables

 

[Joe4]

The structure of your query and criteria obviously have a big effect on how things work. I am trying to re-create your scenario here so I can see if I can re-create the issue exactly.

Can you paste the SQL code for your query here? Don't worry if your Criteria isn't functioning properly, please include it. I think it is more important to see how you have set up the multiple criteria.

All you need to do to get the SQL code is change your query to SQL view and cut and paste the code here.

 

[mcfadwmc]

HI Joe,

sorted it, you were nearly right with

like [chooser]![product] & "*"

but i solved it like this

Like nz([chooser]![product],"*")

uses the nz to replace null with * or accept the chooser option


many thanks for looking at this

 

[Joe4]

I was thinking along those lines (using NZ), but I really wanted to see how your query was structured because they way I have structured mine, it seems to work fine without the NZ.

I think if you do a Cartesian product (have your criteria table in your query with no join and exactly one record), then I don't think that should be necessary, but if there are no records in your criteria table, then the NZ will probably be necessary.