Null Problems in Select Query

[WOTRAC]

I have a database containing user details
category
type
firstname
lastname
Initial


Some of the fields will contain Nulls

The user enters the criteria for the query from an Access form, but may not enter values in all fields.
How do I get the query to return all the data even when one of the fields contains a Null Value.

Paul

 

[scriverb]

You need to direct the criteria back to the value of the column. Using a control named category this is what you would put in the Category column:

IIF(IsNull(Forms![frmYourForm]![ctlCategory]), [tblUserDetails]![Category], Forms![frmYourForm]![ctlCategory])

Let me know how this works for you.

Bob Scriver

 

[WOTRAC]

That only returns the category fields that contain a value.
I am expecting to see the fields that contain a Null value from the user table.

Or am I missing something

Paul

 

[scriverb]

Please post the SQL that you are using. The technique that I posted is used when multiple fields are being selected in query but the user may not elect to post anything as its criteria. Example: Select Joe Doe for Name but elect not to enter anything for Category. Well if you post the criteria to a form text box for Category and nothing is entered then only records with nothing will be selected. The IIF statement that I posted resolves that issue and allows the user to selectively select only those columns that they want to select on.

I am still not quite sure what you are looking for. Are you using a Form with controls corresponding to the fields to be selected before you run the query? A little more information would help.

Bob Scriver

 

[WOTRAC]

The user details table contains the fields

Category
Type
FirstName
Lastname
Initial

When details are entered the person entering may choose to leave out Initial or First name, resulting in a Null value in the corresponding field.

Next provide the user with a search form containing those same fields. The user enters search criteria for everything accept Initial, but wishes to see all records based on the rest of the criteria.
This would require the Null value fields to be returned as part of the query result.

Your statement takes care of the entry form where the user omits the criteria in the <<Initial>> field, but it fails to return the result set inclusive of the Null value fields.

If I have a default value in the Initial field, say -1, the query works fine, but this is not ideal.

I hope this makes things a little clearer


Paul

 

[scriverb]

Okay we are very close. When I posted I should have said that you need to fashion a similar statement all the way across the rows.

Criteria for Catergory:
IIF(IsNull(Forms![frmYourForm]![ctlCategory]), [tblUserDetails]![Category], Forms![frmYourForm]![ctlCategory])
Criteria for Type:
IIF(IsNull(Forms![frmYourForm]![ctlType]), [tblUserDetails]![Type], Forms![frmYourForm]![ctlType])
Criteria for FirstName:
IIF(IsNull(Forms![frmYourForm]![ctlFirstName]), [tblUserDetails]![FirstName], Forms![frmYourForm]![ctlFirstName])
Criteria for LastName:
IIF(IsNull(Forms![frmYourForm]![ctlLastName]), [tblUserDetails]![LastName], Forms![frmYourForm]![ctlLastName])
Criteria for Initial:
IIF(IsNull(Forms![frmYourForm]![ctlInitial]), [tblUserDetails]![Initial], Forms![frmYourForm]![ctlInitial])

Now these should all be on the same row so they are in an AND situation when it comes to entries. If something is entered into the form field then it is used to select records along with all others entered and with the ones not entered but the value of the Field Column used to select itself.

Does this explain it a little further for you? Get back if you need a better explaination.


Bob Scriver

 

[WOTRAC]

The previous thread makes perfect sense, unfortunately if the user elects to leave all the fields empty, so as to return all the records, the query is not correct.

As some of the database fields contain a NUll value, they prevent all the data from being returned.


eg
CATEGORY, TYPE, FIRSTNAME, LASTNAME, INITIAL

1 1 Paul Smith &quot;NULL&quot;
1 2 John Smith T

In the above example the first record set will not be returned?


Paul

 

[scriverb]

Boy, I am having trouble with this situation. But, I am working on it. Will get back with you when I solve the problem.

Bob Scriver

 

[salsanta]

When the data is saved can you append the Empty string &quot;&quot;. ie (Inital & &quot;&quot. This sets the data to an Zero length string and not a Null value and does not affect the length of any real data entered. This also resolves NULL data return problems when running subsequent queries. I saw this little trick somewhere else (I can't remember where) and I apoligize for not giving the correct author credit.

Sal

 

[scriverb]

That will certainly solve the problem with the null values. You can clean up your data file with an update query(do one field at a time only) to set all the Null text field values to the empty string &quot;&quot; value. Your numeric values should already have a default value of 0 in most cases.


Bob Scriver

 

[carbonbasedbiped]

salsanta or whom it may concern,
How and where do I append an empty string and will I be able to have a zero length for all data?
I am stuck in the same spot as the originator. Thanks a bunch in advance.

 

[scriverb]

Okay I have this figured out. Create a new query and copy and paste the following SQL into the SQL window. Save the query as qryUserDetails. Update your table name for the red code:
SELECT A![Category] & &quot;&quot; AS Category, A![Type] & &quot;&quot; AS Type, A![LastName] & &quot;&quot; AS LastName, A![FirstName] & &quot;&quot; AS FirstName, A![Initial] & &quot;&quot; AS Initial
FROM tblUserDetails as A;

This query cancatenates an empty-string(&quot;&quot onto each of the selection fields.

Now copy and paste this code into a new query and call it qryUserDetails_Select:
SELECT A.Category, A.Type, A.LastName, A.FirstName, A.Initial
FROM qryUserDetails as A
WHERE (((A.Category)=IIf(IsNull([Forms]![frmSelect]![txtCategory]),A![Category],[Forms]![frmCatTypeName]![txtCategory])) AND ((A.Type)=IIf(IsNull([Forms]![frmSelect]![txtType]),A![Type],[Forms]![frmSelect]![txtType])) AND ((A.LastName)=IIf(IsNull([Forms]![frmSelect]![txtLastName]),A![LastName],[Forms]![frmSelect]![txtLastName])) AND ((A.FirstName)=IIf(IsNull([Forms]![frmSelect]![txtFirstName]),A![FirstName],[Forms]![frmSelect]![txtFirstName])) AND ((A.Initial)=IIf(IsNull([Forms]![frmSelect]![txtInitial]),A![Initial],[Forms]![frmSelect]![txtInitial])));


I created a form called frmSelect with controls txtCategory, txtType, txtLastName, txtFirstName, and txtInitial. You have to update these queries with your table, form, and controls names to match your database. But, this will select the nulls that were missing from my original posting. By using a query that cancatenates the empty-string(&quot;&quot on the end of all fields in the table, the original statements that I posted will in fact bring in those records with null values in the fields.

Post back if you have any questions.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???