Help with Query By Form - Returning No results when blank

[schreibman]

Hello! I have appreciated very much the examples in this post, and am trying to do something a bit different.

(Edit - first post, so no hyperlinks!! In this thread:

./forums/showthread.php?t=103312

)


I am using the Basic Criteria Format detailed, i.e. I set the criteria for each field in a separate column in the query grid like this in query Design view:
Field: [FieldName]=[Forms]![FormName]![ControlName] or [Forms]![FormName]![ControlName] Is Null
Show: uncheck
Criteria: True


Which works as expected, allowing me to leave it blank and it will pull all records.

For example in the sample database attached, when I leave this blank it pulls all records regardless of MaritalStatus:

[MaritalStatus]=[Forms]![Main Form]![cboMaritalStatus] Or [Forms]![Main Form]![cboMaritalStatus] Is Null



My question is, how would I modify that query or the form to allow me to pull ONLY rows where the MaritalStatus IS NULL ?


I'm racking my brain and have done about 100 trial and errors, I'm sure the answer is in the land of an IIF statement or somesuch.

Ideally, I'd like the form to still return all records when null, but be able to maybe enter NULL in the field and cause it to return the rows where the field IS NULL

 

[Alansidman]

There is not attachment to review.

 

[spikepl]

First and foremost, you need to indicate to the system what you want. With a plain combo, you have the option of chosing a specific value or let it be untouched. This you can exploit in your criterion, and you did. But that is the limit. If you also want to be able to pull all the [MaritalStatus] IS NULL, then first decide how to indicate that on your form, and post your decision. That can then be included as a separate criterion.

 

[schreibman]

Perhaps next to the combo box I could add a check box, perhaps labled "Marital status NULL?" and the behaviour I would like assistance in the query would be when checked, the rows with Marital Status = Blank are returned.

 

[spikepl]

([MaritalStatus]=[Forms]![Main Form]![cboMaritalStatus] Or [Forms]![Main Form]![cboMaritalStatus] Is Null) AND NOT [Forms]![Main Form]![ckbMaritalStatusNull] OR ([MaritalStatus] IS NULL AND [Forms]![Main Form]![ckbMaritalStatusNull] )

 

[schreibman]

Perfect! Thank you so much!