Query Coding for Null Value

[cstanley]

A simple query question (for someone else).

I have an option box on a form with three choices. The query I have written takes the numerical option box value and uses it to filter values. The first two choices are specific, but for the third one I want the query to return all the values in the table. The option group doesn't let me have a "Null" value - how do I code for the query to get it from the numeric value in the option group?

i.e.

[forms]![frmAssociateDesignRequirementsWithDocuments]![optSelectDesignDocumentType]

as the query criteria.


Thanks!


Chris

 

[pdx_man]

Try:

Iif([forms]![frmAssociateDesignRequirementsWithDocumen
ts]![optSelectDesignDocumentType] = 2,,[forms]![frmAssociateDesignRequirementsWithDocumen
ts]![optSelectDesignDocumentType])

Assuming that options are 0,1,2

 

[cstanley]

Yes, I tried that, but it gives me an error message that "this is an invalid string larger than 255 characters" if it is just a ,, and if it replaces that portion of the code with ,", it says "This statement is too complex to be evaluated"


Other ideas?

Chris

 

[pdx_man]

Can you zip and post your DB in A97?

 

[cstanley]

Sorry - it's way too big.

Chris

 

[pdx_man]

OK, I created a sample myself and saw the same error you had. I could have sworn I've done the same thing before ... Anyway, here is a solution:

Have the option to show all the values be a value that is not one of the values in the field, like, say, -1.
Create a new column in the query that does not show that has in the Field row:
[forms]![frmAssociateDesignRequirementsWithDocuments]![optSelectDesignDocumentType] = [YourField]
YourField is the field where you had the criteria under before
For the criteria for this new column, have:
IIf([forms]![frmAssociateDesignRequirementsWithDocuments]![optSelectDesignDocumentType]=-1,0,-1)

This worked for me ...
By the way, that is quite the name for the form. I have seen where users have exceeded limitations because of lengthy naming. Just an FYI.
frmAssociateDesignRequirementsWithDocuments becomes frmAssctDsgnReqWDoc, maybe.

 

[cstanley]

Thanks so much! This worked when I removed the iif in your example from the criteria field.

Chris

 

[cstanley]

Wait a second - no it didn't. I'm sorry to say that I don't quite get it - could you explain a little further?

Thanks,

Chris

 

[pdx_man]

i.e.

[forms]![frmAssociateDesignRequirementsWithDocumen
ts]![optSelectDesignDocumentType]

as the query criteria.

As the criteria for what field ... ? I'm going to call it [YourField].
Change the option valuefor the All option to -1.
Remove any criteria that you currently have on this field.
In a blank column in that query, on the Field row, put:

[forms]![frmAssociateDesignRequirementsWithDocumen
ts]![optSelectDesignDocumentType] = [YourField]

This will return a true/false condition, a -1 or 0. Run the query with different values from your form and check it out.
Now, for the criteria on this newly created column, put:

IIf([forms]![frmAssociateDesignRequirementsWithDoc
uments]![optSelectDesignDocumentType]=-1,0,-1)

When you have the All option selected, this will cause the Iif function to return the first value of 0, which, as you can see from your trials before you had the criteria, will return all rows. Why, because none of the values in [YourField] equal -1. Otherwise, it returns those that equal the value of the option group.