Help returning blank (no entry) records with a parameter query

[rgsmpx]

Please help with this one:

I want a query to run off of criteria supplied by combo boxes on a form. There are seven combo boxes; the user selects values from any or all of the combo boxes, and the query then returns the records matching the user's selection -- at least that is the way I want it to work.

I've set up the form and the query. For a number of the combo boxes, I have three basic types of choices: "All", "(blank)", or a particular value from a record (e.g. "Distributor", or "Wholesaler"). When the user chooses "All", I want all the records to be returned. When the user selects "(blank)", I want the records that have no entry to be returned, and when the user selects a value like "Distributor" I want only those records with "Distributor" in the field to be returned.

My problem is that I cannot figure out how to populate the criteria in the query so that the right records are returned. My first wrong attempt was to cite the full form reference in the criteria field, for example:
Criteria: [Forms]![Longform Funnel Data]![cboSelectLeadSource]
Or: [Forms]![Longform Funnel Data]![cboSelectLeadSource] Is Null
This clearly did not work for "All" or "(blank)" but worked fine for other values.

My second attempt was to create an intermediary text box for each combo box, and have the query reference the text box. I then developed some not so elegant code to populate the text box with values that would give the query the right criteria. When the combo box has "All", the text box would be set to Null. When the combo box has a value like "Distributor", the text box gets populated with that value. Its what to do when the combo box gets popluated with "(blank)" (or if the user leaves it empty) that has me stumped.

Here is an example of the code for populating the text box -- the result is fine except for when the user chooses "(blank)" or leaves it empty (null value):

Private Sub cboSelectLeadSource_AfterUpdate()
On Error GoTo cboSelectLeadSource_AfterUpdate_Err
'puts the user selected Lead Source value for use in the filtering query
Dim strSelectFilter As String
Dim vrAssessifNull As Variant
' this assesses whether the value is null (blank)
vrAssessifNull = Me![cboSelectLeadSource]
Select Case Nz(vrAssessifNull, "nullvalue")
Case "nullvalue" 'when null, should be treated like "(blank)" -- return only records with no entry in this field
strSelectFilter = "Not Like *"
txLeadSourceFIlter = strSelectFilter
Case Else
Select Case vrAssessifNull
Case "(All)" 'user chose "All" so all records need to be returned
txLeadSourceFIlter = Null
Case "(blank)" 'user wants only records with no entry to be returned
strSelectFilter = "Not Like *"
txLeadSourceFIlter = strSelectFilter
Case Else
strSelectFilter = Me!cboSelectLeadSource
txLeadSourceFIlter = strSelectFilter
End Select
End Select
'update textbox with the value for the query to use in the filter
Me.Refresh

The criteria in the field on the query now looks like:
Criteria: [Forms]![Longform Funnel Data]![txLeadSourceFIlter]
Or: [Forms]![Longform Funnel Data]![txLeadSourceFIlter] Is Null

I assume that in the "(blank)" case the query is looking for records with the value "Not Like *" in the field, rather than returning records where the field is blank. If I delete the entire form reference criteria and just type Is Null on the criteria line, then the blank records are returned.

So how do I get only those blank records by using the parameter from the form?

Thanks,
R.G.

 

[Jack Cowley]

I think you will find your answer in this Knowledge Base article....

http://support.microsoft.com/support/kb/articles/Q136/0/62.asp?LN=EN-US&SD=gn&FR=0&qry=q136062&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=ACC97

 

[rgsmpx]

Jack, thanks for the knowledge base article. It helped solve a secondary issue with how to better set up my query in VB. Unfortunately, it did not help with the basic problem of getting the query to evaluate the form reference as Not Like "*" or alternatively Is Null rather than as a string "Is Null" or "Not Like *". Any other suggestions? Am I going about this the wrong way? Is the problem in how I reference the form, or in the code for the text box, or in both? I'm well beyond my Access capabilities with this one.

Thanks,
R.G.