Null in Parameter Query Form Drop Down

[mv66174]

I have created a Parameter Query Form with 3 drop down criteria fields (1-Product, 2-Failure Mode, 3-Failure Mechanism). The 'row source' for the drop down fields are from another query.

The issue I am having is that sometimes one or two of the drop down fields will be left blank. If any of the drop down fields are left blank the query results are blank. I would like to change the below Visual Basic code to allow Null for the drop downs and still run a search. Note that when all of the drop downs are filled out the query runs properly. Below is the code I initially tried:

Private Sub OK___Search_Click()
On Error GoTo Err_OK___Search_Click
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Dim stDocName As String
Dim stLinkCriteria As String
<o> </o>
stDocName = "frm-QIT Details"

stLinkCriteria = "[Failure Mechanism]=" & "'" & Me![FailureMechanism] & "'" & "AND [Failure Mode]=" & "'" & Me![FailureMode] & "'" & "AND [Product]=" & "'" & Me![Product] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
<o> </o>
Exit_OK___Search_Click:
Exit Sub
<o> </o>
Err_OK___Search_Click:
MsgBox Err.Description
Resume Exit_OK___Search_Click

End Sub
_________________________________

I then tried the following code with the same result (blank query results if any of the drop downs are left blank)
Private Sub OK___Search_Click()
On Error GoTo Err_OK___Search_Click
<o> </o>
Dim stDocName As String
Dim stLinkCriteria As String
<o> </o>
stDocName = "frm-QIT Details"

If IsNull(Me![FailureMode]) Then
stLinkCriteria = "[Product]=" & "'" & Me![Product] & "'"

End If

If IsNull(Me![FailureMechanism]) Then
stLinkCriteria = "[Failure Mode]=" & "'" & Me![FailureMode] & "'" & "AND [Product]=" & "'" & Me![Product] & "'"

End If

stLinkCriteria = "[Failure Mechanism]=" & "'" & Me![FailureMechanism] & "'" & "AND [Failure Mode]=" & "'" & Me![FailureMode] & "'" & "AND [Product]=" & "'" & Me![Product] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
<o> </o>
Exit_OK___Search_Click:
Exit Sub
<o> </o>
Err_OK___Search_Click:
MsgBox Err.Description
Resume Exit_OK___Search_Click

End Sub
______________________________
I appreciate any help you can provide.

 

[xenou]

I generally go to extreme lengths to validate my inputs, probably because I've not taken the time to assure myself of what happens with things like blank fields.

However, what needs to happen in your query is that the blanks (inputs) become NULL keywords in the SQL.

Its a good habit to load everything into variables - easier for debugging, if nothing else.

So, something like:

FailureMechCriteria = IIf(IsNull(Me![FailureMechanism].Value),"[Failure Mechanism] Is NULL","[Failure Mechanism] = '" & Me![FailureMechanism].Value & "'")

FailureModeCriteria = IIf(IsNull(Me![FailureMode].Value),"[Failure Mode] Is NULL","[Failure Mode] = '" & Me![FailureMode].Value & "'")

stLinkCriteria = FailureMechCriteria & " AND " & FailureModeCriteria

msgbox stLinkCriteria '//Inspect result!  See if it looks right!

The point is that you want (if the fields are blank) to get:
[Failure Mechanism] Is Null AND [Failure Mode] Is Null

This is really all assuming that your table has Nulls, rather than zero-length strings, and so on. I always assume input fields like text boxes or combo boxes might have zero length strings in them too, but I think the IsNull function returns True if there's no value in them. I often use NZ nested in a Len() function to check for nulls or zero length strings! That's where I get paranoid. At any rate, find out what works as a sample string, then get your query string to come out the same when the input fields are blank!

Alex

 

[xenou]

I hope the above made sense. In short,

if you have a non-null value you can use operators:
Field1 = 1, Field2 > 1, Field3 <= 1, Field4 = "Open", etc. etc.

If you have null values you must use the IS NULL syntax:
Field1 Is Null, Field2 Is Not Null

And what you want is to be able to create these Is Null or Is Not Null expressions as needed - I think ... I'm really guessing though. I would use a string literal first, to test that results are being returned as expected, then work out the code to create the string you need from the combo box inputs.

I'll be out for the rest of the day but post back if there's still trouble - someone else may be able to help, and I'll check in again if I can.

Regards,
Alex

 

[mv66174]

I really appreciate you responding Alex. I am fairly new to access so my apologies if I am not understanding everything you are telling me. I tried the code you provided but when I click the 'ok' button after I make my drop down selections nothing happens (the query doesn't run).

Here is my code:
________________
Private Sub OK___Search_Click()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
On Error GoTo Err_OK___Search_Click<o></o>
<o> </o>
Dim stDocName As String<o></o>
Dim stLinkCriteria As String<o></o>
<o> </o>
stDocName = "frm-QIT Details"<o></o>
<o></o>
FailureMechCriteria = IIf(IsNull(Me![FailureMechanism].Value), "[Failure Mechanism] Is NULL", "[Failure Mechanism] = '" & Me![FailureMechanism].Value & "'")<o></o>
<o> </o>
FailureModeCriteria = IIf(IsNull(Me![FailureMode].Value), "[Failure Mode] Is NULL", "[Failure Mode] = '" & Me![FailureMode].Value & "'")<o></o>
<o> </o>
stLinkCriteria = FailureMechCriteria & " AND " & FailureModeCriteria<o></o>
<o> </o>
Exit_OK___Search_Click:<o></o>
Exit Sub<o></o>
<o> </o>
Err_OK___Search_Click:<o></o>
MsgBox Err.Description<o></o>
Resume Exit_OK___Search_Click<o></o>
<o></o>
End Sub
______________

I also tried the following...which almost gets me what I want...with this code the search will run even if I leave some drop downs blank...the problem with this is that I still don't get the records that have Null for 'Failure Mode' or 'Failure Mechanism'. I just thought I would throw this out to you to see if you have any other ideas.
____________

Private Sub OK___Search_Click()<o></o>
On Error GoTo Err_OK___Search_Click<o></o>
<o> </o>
Dim stDocName As String<o></o>
Dim stLinkCriteria As String<o></o>
<o> </o>
stDocName = "frm-QIT Details"<o></o>
<o></o>
stLinkCriteria = "[Product]Like '*" & Me![Product] & "*'" & "AND [Failure Mode]Like '*" & Me![FailureMode] & "*'" & "AND [Failure Mechanism]Like '*" & Me![FailureMechanism] & "*'"<o></o>
DoCmd.OpenForm stDocName, , , stLinkCriteria<o></o>
<o> </o>
Exit_OK___Search_Click:<o></o>
Exit Sub<o></o>
<o> </o>
Err_OK___Search_Click:<o></o>
MsgBox Err.Description<o></o>
Resume Exit_OK___Search_Click<o></o>
<o></o>
End Sub
_______

Basically I want to search records for the 3 drop down selection criteria...and I want the query to behave as though 'Null' is a valid selection (bring back the records with 'Product' "Display"...'Failure Mode' "(blank/null)"...'Failure Mechanism' "(blank/null)"

Thanks again!
Molly

 

[xenou]

Hi,
Can you post a sample query with just straight SQL that returns a set of records you'd like to see? Or even a set of records not quite what you want? Its a bit murky working with these example in code and with form fields - i.e., something like:

SELECT MyTable.Product, MyTable.ID, MyTable.Status From MyTable Where Product.Status = 'Active'

Those are all made up fields but you get the idea. Are your drop downs, etc. supposed to return lots of records or only one record? And are the Nulls supposed to be included with the rest of the records returned (the non-nulls) or are the null suppposed to be the only records returned (just the nulls).

Just to reiterate, SQL treats Nulls as a special case, so you do not use "=", "<", ">" or other operators with Null values.

 

[mv66174]

Alex,
Here is the SQL of the query that I am trying to run...however this is missing the stLinkCriteria from the Parameter query which would be: WHERE Product = the Product Selected in the drop down...WHERE Failure Mode = Failure Mode Selected in the drop down (which could be Null)...WHERE Failure Mechanism = Failure Mechanism Selected in the drop down (which could be Null)

SELECT [IVS Analysis Data Entry].Product, [IVS Analysis Data Entry].[Product S/N], [IVS Analysis Data Entry].RMA, [IVS Analysis Data Entry].[IVS Date Analyzed], [IVS Analysis Data Entry].[IVS Notes], [IVS Analysis Data Entry].[Failure Mode], [IVS Analysis Data Entry].[Failure Mechanism], [IVS Analysis Data Entry].[NCCA No]<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
FROM [IVS Analysis Data Entry]<o></o>
WHERE ((([IVS Analysis Data Entry].[NCCA No])>299 And ([IVS Analysis Data Entry].[NCCA No])<10000)) OR ((([IVS Analysis Data Entry].[NCCA No]) Is Null))<o></o>
ORDER BY [IVS Analysis Data Entry].Product, [IVS Analysis Data Entry].[Failure Mode], [IVS Analysis Data Entry].[Failure Mechanism];

The Drop downs will return MANY records.
If 'Null' is selected in the Drop Downs I would like the query to return just the Nulls.

Thanks again,
Molly

 

[xenou]

This probably isn't exactly right as I'm not fully sure of the query you need. I hope it will provide an example of how to construct the filter string. It seems easiest to me to use some functions to isolate the string processing. We can stop short of actually opening the other form yet -- all we want to do at this point is get a syntactically correct filter string with the right fields in it!

Check the field names, also check that there shouldn't be any parentheses added anywhere. And of course - check the message boxes and see if they are outputting the right info!

Private Sub F00()

Dim stDocName As String
Dim stLinkCriteria As String
Dim ProductCriteria
Dim FailureModeCriteria
Dim FailureMechanismCriteria

stDocName = "frm-QIT Details"

'----------------------------------------------------------------
ProductCriteria = "[Product]Like '*" & Me![Product] & "*'"

FailureModeCriteria = GetFailureModeCriteria()
MsgBox "FailureModeCriteria is: " & FailureModeCriteria
Debug.Print FailureMechanismCriteria

FailureMechanismCriteria = GetFailureMechanismCriteria()
MsgBox "FailureMechanismCriteria is: " & FailureMechanismCriteria
Debug.Print FailureMechanismCriteria
'----------------------------------------------------------------

stLinkCriteria = _
    ProductCriteria _
    & " AND " _
    & FailureModeCriteria _
    & " AND " _
    & FailureMechanismCriteria
MsgBox "stLinkCriteria is: " & stLinkCriteria
Debug.Print stLinkCriteria

'DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub
'--------------------------------------------------
Private Function GetFailureModeCriteria() As String
Dim s As String
Dim x As String

s = "[Failure Mode] Like '*XYZ*'"
x = Nz(Me.Failure_Mode.Value, "")

If Len(x) > 0 Then
    s = Replace(s, "XYZ", x)
Else
    s = Replace(s, "Like '*XYZ*'", "Is Null")
End If
    
GetFailureModeCriteria = s

End Function
'-------------------------------------------------------
Private Function GetFailureMechanismCriteria() As String
Dim s As String
Dim x As String

s = "[Failure Mechanism] Like '*XYZ*'"
x = Nz(Me.Failure_Mechanism.Value, "")

If Len(x) > 0 Then
    s = Replace(s, "XYZ", x)
Else
    s = Replace(s, "Like '*XYZ*'", "Is Null")
End If
    
GetFailureMechanismCriteria = s

End Function