Search Form Handling of Nulls

I have a table (tblFormData) populated with data and a form to display single records from the table.

The user will need to find the record they want to view by searching various field that are displayed on the form, such as Company Name, Account Number, first name, Last Name, Address1, Address2, Address3, City, ZIP, or Phone Number.

To accomplish this I first created a preliminary search form with these fields on it and a submit button that that opens the primary form, whose record source was set to be the following query:

SELECT tblFormData.Name, tblFormData.FullName, tblFormData.ParentRefFullName, tblFormData.MaxOfTxnDate, tblFormData.CompanyName, tblFormData.FirstName, tblFormData.LastName, tblFormData.TotalAging, tblFormData.Current, tblFormData.[1-30], tblFormData.[31-60], tblFormData.[61-90], tblFormData.[>90], tblFormData.BillAddressAddr1, tblFormData.BillAddressAddr2, tblFormData.BillAddressAddr3, tblFormData.BillAddressCity, tblFormData.BillAddressState, tblFormData.BillAddressPostalCode, tblFormData.BillAddressCountry, tblFormData.Phone, tblFormData.SalesRepRefListID, tblFormData.CustomFieldNotes, tblFormData.AccountNumber, tblFormData.TimeStamp
FROM tblFormData
WHERE (((tblFormData.CompanyName) Like "*" & [Forms]![SearchForm]![CompanyName] & "*") AND ((tblFormData.FirstName) Like "*" & [Forms]![SearchForm]![FirstName] & "*") AND ((tblFormData.LastName) Like "*" & [Forms]![SearchForm]![LastName] & "*") AND ((tblFormData.BillAddressAddr1) Like "*" & [Forms]![SearchForm]![BillAddress1] & "*") AND ((tblFormData.BillAddressAddr2) Like "*" & [Forms]![SearchForm]![BillAddress2] & "*") AND ((tblFormData.BillAddressAddr3) Like "*" & [Forms]![SearchForm]![BillAddress3] & "*") AND ((tblFormData.BillAddressCity) Like "*" & [Forms]![SearchForm]![BillAddressCity] & "*") AND ((tblFormData.BillAddressPostalCode) Like "*" & [Forms]![SearchForm]![BillAddressZip] & "*") AND ((tblFormData.AccountNumber) Like "*" & [Forms]![SearchForm]![AccountNumber] & "*"));

I immediately had problems trying to use this will a very small set of data (only 8 records)  where only three records had values in BillAddress3. The result of the query only contains those three records.

I did some research and thought I found a solution in code that I put on the  On Load event of the record display form:

Private Sub Form_Load()
'define variables
Dim varFirstParameter As Variant
Dim varSecondParameter As Variant
Dim varThirdParameter As Variant
Dim varFourthParameter As Variant
Dim varFifthParameter As Variant
Dim varSixthParameter As Variant
Dim varSeventhParameter As Variant
Dim varEighthParameter As Variant
Dim varNinthParameter As Variant
Dim varTenthParameter As Variant
Dim strSQL As String

 
'set paramter variables
'Note: chr(42) is an asterisk
'Note: ch(34) is a double quote

If IsNull([Forms]![Searchform]![CompanyName]) = True Then
varFirstParameter = "*"
Else
varFirstParameter = Chr(42) & [Forms]![Searchform]![CompanyName].Value & Chr(42)
End If

If IsNull([Forms]![Searchform]![AccountNumber]) = True Then
varSecondParameter = "*"
Else
varSecondParameter = Chr(42) & [Forms]![Searchform]![AccountNumber].Value & Chr(42)
End If

If IsNull([Forms]![Searchform]![FirstName]) = True Then
varThirdParameter = "*"
Else
varThirdParameter = Chr(42) & [Forms]![Searchform]![FirstName].Value & Chr(42)
End If

If IsNull([Forms]![Searchform]![LastName]) = True Then
varFourthParameter = "*"
Else
varFourthParameter = Chr(42) & [Forms]![Searchform]![LastName].Value & Chr(42)
End If

If IsNull([Forms]![Searchform]![BillAddress1]) = True Then
varFifthParameter = "*"
Else
varFifthParameter = Chr(42) & [Forms]![Searchform]![BillAddress1].Value & Chr(42)
End If

If IsNull([Forms]![Searchform]![BillAddress2]) = True Then
varSixthParameter = "*"
Else
varSixthParameter = Chr(42) & [Forms]![Searchform]![BillAddress2].Value & Chr(42)
End If

If IsNull([Forms]![Searchform]![BillAddress3]) = True Then
varSeventhParameter = "*"
Else
varSeventhParameter = Chr(42) & [Forms]![Searchform]![BillAddress3].Value & Chr(42)
End If

If IsNull([Forms]![Searchform]![BillAddressCity]) = True Then
varEighthParameter = "*"
Else
varEighthParameter = Chr(42) & [Forms]![Searchform]![BillAddressCity].Value & Chr(42)
End If

If IsNull([Forms]![Searchform]![BillAddressZip]) = True Then
varNinthParameter = "*"
Else
varNinthParameter = Chr(42) & [Forms]![Searchform]![BillAddressZip].Value & Chr(42)
End If

If IsNull([Forms]![Searchform]![PhoneNumber]) = True Then
varTenthParameter = "*"
Else
varTenthParameter = Chr(42) & [Forms]![Searchform]![PhoneNumber].Value & Chr(42)
End If

'build SQL statement
strSQL = "SELECT "
strSQL = strSQL & "* "
strSQL = strSQL & "FROM "
strSQL = strSQL & "tblFormData "
strSQL = strSQL & "WHERE "
strSQL = strSQL & "("
strSQL = strSQL & "("
strSQL = strSQL & "(tblFormData.CompanyName) Like " & Chr(34) & varFirstParameter & Chr(34)
strSQL = strSQL & ")"
strSQL = strSQL & " OR "
strSQL = strSQL & "( "
strSQL = strSQL & "(tblFormData.AccountNumber) Like " & Chr(34) & varSecondParameter & Chr(34)
strSQL = strSQL & ")"
strSQL = strSQL & " AND "
strSQL = strSQL & "("
strSQL = strSQL & "(tblFormData.Name) Like " & Chr(34) & varThirdParameter & Chr(34)
strSQL = strSQL & ")"
strSQL = strSQL & " AND "
strSQL = strSQL & "("
strSQL = strSQL & "(tblFormData.LastName) Like " & Chr(34) & varFourthParameter & Chr(34)
strSQL = strSQL & ")"
strSQL = strSQL & " AND "
strSQL = strSQL & "("
strSQL = strSQL & "(tblFormData.BillAddressAddr1) Like " & Chr(34) & varFifthParameter & Chr(34)
strSQL = strSQL & ")"
strSQL = strSQL & " AND "
strSQL = strSQL & "("
strSQL = strSQL & "(tblFormData.BillAddressAddr2) Like " & Chr(34) & varSixthParameter & Chr(34)
strSQL = strSQL & ")"
strSQL = strSQL & " AND "
strSQL = strSQL & "("
strSQL = strSQL & "(tblFormData.BillAddressAddr3) Like " & Chr(34) & varSeventhParameter & Chr(34)
strSQL = strSQL & ")"
strSQL = strSQL & " AND "
strSQL = strSQL & "("
strSQL = strSQL & "(tblFormData.BillAddressCity) Like " & Chr(34) & varEighthParameter & Chr(34)
strSQL = strSQL & ")"
strSQL = strSQL & " AND "
strSQL = strSQL & "("
strSQL = strSQL & "(tblFormData.BillAddressAddr1) Like " & Chr(34) & varNinthParameter & Chr(34)
strSQL = strSQL & ")"
strSQL = strSQL & " AND "
strSQL = strSQL & "("
strSQL = strSQL & "(tblFormData.Phone) Like " & Chr(34) & varTenthParameter & Chr(34)
strSQL = strSQL & ")"
trSQL = strSQL & ")"
trSQL = strSQL & ";"
Me.RecordSource = strSQL

End Sub

Open in new window


However, the problem persists - the nulls are not getting handled.

After writing this up I am thinking that this could be simplified by putting the code on the After Update event of the record display form, changing the references in the code to point to the actual fields on the record display form, letting the user search directly from there, and eliminating the separate search form,

However, unless I can get by the nulls it won't matter.
haidentAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
In my opinion, you're better off building up your WHERE clause based on user input. For example, if I have a form that has 3 Textboxes named txAccount, txName and txType, and I want to allow the user to enter one or more, then I do this:
Dim sWhere As String
If Nz(Me.txAccount, "") <> "" Then
  sWhere = " Account ='" & Me.txAccount & "'"
End If
If Nz(Me.txName, "") <> "" Then 
  If Len(sWhere) > 0 Then
    sWhere = sWhere & " AND "
  End If
  sWhere  = sWhere & " Name='" & Me.txName & "'"
End If
If Nz(Me.txType, "") <> "" Then 
  If Len(sWhere) > 0 Then
    sWhere = sWhere & " AND "
  End If
  sWhere  = sWhere & " [Type]=" & Me.txName 
End If

Dim sql As String
sql = "SELECT * FROM SomeTable
If Len(sWhere) > 0 Then
  sql = sql & " WHERE " & sWhere
End If

Me.MyListBox.RowSource = sql

Open in new window

That would fill a Listbox, buy you could easily use it to fill another form, or a Subform, etc.
0
PatHartmanCommented:
Scott's method of building the SQL string is an excellent option and I use it frequently.  Occasionally, if my selection criteria is more limited, I embed it in a querydef.

The ONLY reason to use LIKE with wild cards is if you actually need to support the entry of partial strings.  Just keep in mind that the technique will not always produce the expected result for numeric values and dates should be handled differently by using date functions such as Year() or Month() to select partial values.  LIKE is inefficient in that it precludes the use of indexes so using like in many situations will force the query engine to do a full table scan.

Null always needs to be handled specifically since somefield = null will always return false even if somefield is null.  If you want the selection to return the specified value OR null then your where needs to be:

Where (somefield is null OR somefield LIKE "*xxxx*") AND (someotherfield is null OR someotherfield LIKE "*xxx*")


Notice the parentheses.  They are required whenever your compound condition includes AND, OR, and NOT  in order to make the query engine evaluate the condition as you intend.  Without them, Boolean logic applies.  That would have the above expression evaluated as:
Where somefield is null OR (somefield LIKE "*xxxx*" AND someotherfield is null) OR someotherfield LIKE "*xxx*"
And that would produce very different results.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Was there some reason you awarded a B grade without asking the Experts for more help? EE guidelines suggest you should give the Experts a chance to improve on their comments before giving a grade lower than A:

http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-?b_id=44
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.