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
The Public function which retrieves this Partial customer string is as follows:
Public Function getpartialCustName() As String
getpartialCustName = GlobalPartialCustomerName & "*"
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.