troubleshooting Question

Passing Query Criteria using IIF and one option is Is Not Null.

Avatar of cklosko
cklosko asked on
Microsoft AccessMicrosoft Development
27 Comments1 Solution719 ViewsLast Modified:
I have 2 text boxes, CustNameString and ModelNumberString on a Report form. If the user enters 's' in the CustNameString and '1' in the ModelNumberString textbox, the report will display all customers whose names begin with 's' and that have Model Numbers starting with '1'.
Once data has been entered in these strings, the AfterUpdate code is as follows:
Private Sub CustNameString_AfterUpdate()
  GlobalPartialCustomerName = CustNameString
End If
End Sub

The Public function which retrieves this Partial customer string is as follows:
Public Function getpartialCustName() As String
getpartialCustName = GlobalPartialCustomerName & "*"
End If
End Function

In the Criteria portion of the query, I use
Like getpartialCustName() and all customers starting with the characters typed in the text box are retrieved correctly. This works fine.

The problem lies in how to deal with null values coming into the query, as criteria. If the user enters characters into CustNameString, but not ModelNameString, I want all Model Numbers retrieved. There are 2 problems:
1) I need to set the Global variables to null, so that when the user previews the report with different criteria, they are starting with null Global variables. I have tried using:
GlobalPartialCustomerName = Null
GlobalPartialCustomerName.Value = Null and just receive errors.

2) I need a statement in the Criteria portion of the Query which will account for a null value coming into the query. I have tried:
IIF(GlobalModelNumber Is Null, Like getpartialCustName(), Is Not Null) in order to retrieve all Model Numbers. This statement also gave me an error.

Please help with these 2 issues.
Thanks.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 27 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 27 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros