?
Solved

Search Form Handling of Nulls

Posted on 2014-08-11
3
Medium Priority
?
246 Views
Last Modified: 2014-08-26
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.
0
Comment
Question by:haident
  • 2
3 Comments
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 750 total points
ID: 40254365
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
 
LVL 40

Accepted Solution

by:
PatHartman earned 750 total points
ID: 40255796
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
 
LVL 85
ID: 40286491
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question