Topics Search

Blank Fields in NOT Null Query Criteria

Blank Fields in NOT Null Query Criteria
Views: 27
Why would a few blank fields show up in a query where I am using 'Is Not Null' in the criteria. The rest of the Nulls are filtered out - but there are 7 blank fields showing up. I went in to the table and hit delete in each of the 7 fields but they still show up in the query results.
Sponsored Links:

More topics


Returning all records when querying by form

I am trying to develop a form to allow the user to choose what fields to search for records with. I have set the default values of all fields to * thinking that Like "*" would return all records; however, this does not work.

Any records with null or blank in any of the fields on the form for which the user does not enter search criteria will not be included in the results.

If I use this in my query:

Like [Forms]![Search Clients]![LicenceNo] Or Is Null Or ""

I get all the null and blank results regardless of what the user enters. But what I want is to get all the results only if the user does not enter criteria.

Working with blank fields

I have a field where the output appears blank but is not a null character. How do I query to get the blank data when it is not null?

Help with Query By Form - Returning No results when blank

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 records. when I leave this blank it pulls all records regardless of MaritalStatus:

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

How would I modify that query or the form to allow me to pull ONLY rows where the MaritalStatus IS NULL?

Conditional Criteria

I'm designing a search menu that uses a query to find a record. I want the menu to allow the user to enter in up to 13 text boxes of information on the record in order to find it. I have created a query that can find the record if all 13 text boxes are entered, however if one is blank the query fails.

I want to write an sql code for the query that sets the criteria to the value of the text box in the search menu if the text box is not null, and leaves the criteria field blank if the text box is null.

Here is an example of what I have tried in one criteria box:

((Table_Closure.[Job Number]) = IIF((Forms!Search_Menu![Job#_TXT]) = Not Null, (Forms!Search_Menu![Job#_TXT])))

But the search still isn't working of a text box is left blank.

Automatically changing a blank field into a null value?


background of what I'm trying to accomplish: I have set-up a column in a query to count the amount of null values for each record. However, when a field has information and then is deleted, that field is now read as blank (or 0 value) instead of null and my count is thrown off. I used an update query to change those blank fields into nulls.

Ok this being said, I do not want to have to keep changing each field each time I find blanks, so my question is, is there a function or macro or module that will automatically change a newly created blank field into a "null value"?

How to retrieve dates that meets a criteria using Query? Exp Build

I have 4 columns/fields of dates in a table: Field A, Field B, Field C & Field D. These fields can be either filled with a date or left blank. ABCD are all "Dates".

I want to design a query or report to retrieve dates from Field A. The criteria is:
Field A must be filled (I.e. there is a date, not a blank); and when dates in any field of B or C or D are not blank, A must be more recent than this/those date(s).

How do I write it? I made "A is not null" in the first criteria. And then I think I should write something like when B or C or D are not blank, A B or C or D.

I would use expression builder. I tried a couple of them I was not able to make any of them work.

Between, I am using Access 2010. The expression builder has symbols like "&" and
"AND", what's the difference?

Passing criteria "NULL" or "IS NOT NULL" to a query

I want to pass the criteria "NULL" or "IS NOT NULL" to the underlying query in a report from a field on a form where all criteria for the particular report is selected before running the report. Typicially the procedure I use is to place the name of the control from the form in the criteria field of the query. example. if I'm providing a date to the query, I put a date field on the form and put the full name of the field "forms!myform!fieldname" in the criteria field of the query underlying the report. This is pretty bullet proof, except when I want to pass a value of "null", or "not null" as the criteria for a given field in the query. How can I set up a combo box, or command button, or selection box to feed the values "NULL" or "NOT NULL" as criteria the underlying query

Returning Null Values and Blank Criteria in Form from Query

I am trying to get a query that runs off input values in a form in access 2010. I would like to return null values in fields and if a box is not filled out in the form to ignore this parameter.

I whave been trying to do this without creating a union query. But this is what I have so far but it keeps saying I have a syntax error and sometime I have gotten you are missing an operator.

I know what those errors mean but I am getting a little stuck at the moment.

Query Criteria - not working ?

I am using a form with 10 fields used as a search form. The idea is for the user to enter data into any or of the 10 fields to search the database. If they leave the field blank, the search should not limit the returned data based on that blank field.

I am using a form to pass the search parameters to a query. The criteria line in the code has a statement like the following:

This allows the user to enter a wildcard search for partial entries which is very helpful.

This all works properly.EXCEPT when any of the search fields have no data in the database. For instance, if one of the 10 fields is missing data in the database (that field is null), then that record is overlooked for some reason.

Passing form fields criteria to a Query

I have a form for allowing the user generate report with selection criteria. This is a form with 5 Selection Fields - All of them combo-boxes. these fields passes the user selection parameters to a query, for generating the report.

Passing these combo boxes Values to the Query selection fields is easy; but I don't know how to handle the situation where the user did not select specific value in one filed or more.

I know I can use 'Is Null' in the query criteria in a specific column for the field - but then I have to make a very complex criteria, resulting in 5 * 5 situations (first field is null,2-5 are not, second filed is null the others are not etc, etc.)