Access 2010 Issues with “IS NULL” in Select statement [closed]

Closed. This question is not reproducible or was caused by typos. It is not currently accepting answers.

This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers.

Closed 5 years ago.

Improve this question

I am a novice programmer attempting to build a database for work in Access 2010 in an effort to better manage our data. Our filing system involves documents nested under one another in various branching subjects. The branches are all varying lengths, in my database each record always has a collection number, series number, and file unit number but there are several sub-series numbers that they may or may not have depending on how many subjects branch off of the previous subject. All of this information collects in a File Unit table.

I’m working on setting up a search form with a combobox for collection number, series number, each possible sub-series number, and then file unit number, so that from the first combobox the user chooses their collection number which then triggers a query that fills the series combobox with all series numbers available for that collection number, and so on and so forth down the line. My problem is with the file unit number combobox.

I need the file unit number combobox to fill with options after the series number is selected and update with each subsequent selection of sub-series number to only display file unit numbers from the file unit table that match the selected values and have null values for sub-series comboboxes that have nothing selected.

I’ve written a sub procedure that calls after the user exits each combobox when certain conditions are met. In the sub procedure, if then statements check each combobox, if there’s a value it’s stored in a corresponding string variable and if not the variable is set to IS NULL.

This is the code I’m using to fill the file unit number combobox:

File_Unit_Number.RowSourceType = "Table/Query"

File_Unit_Number.RowSource = "SELECT File_Unit.File_Unit_Number,
File_Unit.File_Unit_Name FROM File_Unit WHERE
(((File_Unit.Collection_Number) " & cmbCollection & " AND
(File_Unit.Series_Number) " & cmbSeries & "
AND(File_Unit.Subseries_1_Number) " & cmbSubseries_1 & "
AND(File_Unit.Subseries_2_Number) " & cmbSubseries_2 & "
AND(File_Unit.Subseries_3_Number) " & cmbSubseries_3 & "
AND(File_Unit.Subseries_4_Number) " & Subseries_4 & "
AND(File_Unit.Subseries_5_Number) " & cmbSubseries_5 & "));"

I’ve run tests to make sure that my if-then statements are triggering correctly and that the SQL string is printing correctly. When I remove the fields that have null values (ie. delete everything except series_number) from the WHERE part of the statement and test then items populate my file unit combobox. Problem is these numbers are all of the file unit numbers regardless of value in all of the sub-series columns.

What am I doing wrong? And is there an easier way to get the results I need?

Answer

Sorry to waste anyone’s time! I sorted out the answer on my own. It turned out that some of the fields were not NULL but empty strings. I changed the string that was being saved to the variable when there was no value to:

[table].field IS NULL or [table].field = “”

Advertisement

Answer