Help: Using search form. Need to display Null values

[JonTheOne]

Hi My Name is Jon

I will simplifie my problem

I created the following to use the form as a search tool to search the query

I created a table(table1) and a query(query1) for that table and a form(SearchForm) for that query(table->query->form)

In the table there is only 2 fields. filed1 is always filled(NOT Null) and field2 is empty sometimes(Null)

when I first created the search form I put the following code in the query desing window under the criteria row.

For field1:
Like "*" & [Forms]![SearchForm]![Field1] & "*"
For field2:
Like "*" & [Forms]![SearchForm]![Field2] & "*"

The problem with this code is that if there is an an empty(null) field under field2 the search will not return the record with the empty field. For example: I search for "abc" for field1 and there is 2 records that mach "abc" but one of those records has field2 empty(Null), the search will only return 1 record, the one that has both fields filled. the record that has field2 empty is left out.

To partially fix this I wrote the following code for field2:

Like "*" & [Forms]![SearchForm]![Field2] & "*" Or Is Null

this code is not perfect because now when i search "abc" for field1 it will return the 2 records but now it will ALSO return any records that have field 2 empty(null) even if they have nothing to do with "abc"

Thats why I am here I tried IFF functions to tried to fix this but My code skills are poor. maybe someone in this forum can shine some light in the direction I should be heading now.

this is the code I tried to fix this

IIf ([Forms]![SearchForm]![Field2]= "",
Like "*" & [Forms]![SearchForm]![Field2] & "*" or Is Null,
Like "*" & [Forms]![SearchForm]![Field2] & "*") 
 
And I tried this:
IIf ([Forms]![SearchForm]![Field2] = Null,
Like "*" & [Forms]![SearchForm]![Field2] & "*" or Is Null,
Like "*" & [Forms]![SearchForm]![Field2] & "*") 
 
Also tried this but it gives me a error code when 
i run the query(I think because "*" means both if is empty or has text in it)
IIf ([Forms]![SearchForm]![Field2] = "*",
Like "*" & [Forms]![SearchForm]![Field2] & "*",
Like "*" & [Forms]![SearchForm]![Field2] & "*" or Is Null)

than you in advance

 

[tranchemontaigne]

why not try using the IsNull function

Try something like this

'define variables
dim varFirstParameter as variant
dim varSecondParameter as variant
dim strSQL as string

'set parameter variables
'NOTE: chr(42) is an asterisk
'NOTE: chr(34) is a double quote

varFirstParameter = chr(42) & [Forms]![SearchForm]![Field1].value & chr(42)
if isnull([Forms]![SearchForm]![Field2]) = true then
varSecondParameter = "*"
else
varSecondParameter = chr(42) & [Forms]![SearchForm]![Field2].value & chr(42)
end if

'build SQL statement
strsql = "SELECT "
strsql = strsql & "* "
strsql = strsql & "FROM "
strsql = strsql & "table1 "
strsql = strsql & "WHERE "
strsql = strsql & "("
strsql = strsql & "("
strsql = strsql & "(table1.Field1) Like " & chr(34) & varFirstParameter & chr(34)
strsql = strsql & " AND "
strsql = strsql & "("
strsql = strsql & "(table1.Field2) Like " & chr(34) & varSecondParameter & chr(34)
strsql = strsql & ")"
strsql = strsql & ")"
strsql = strsql & ";"

You can now assign a recordsource or rowsource property to strSQL and be good to go.

Please let me know if this was at all helpful.
________
HOT BOX VAPE

 

[JonTheOne]

Hi tranchemontaigne and thanks for helping
I Undastand a little bit of code but what you wrote might be a little to complicade for me

I fogot I made this thead. when I wrote this it was 1 am and I was really sleepy, because of this I didnt put some very important facts:

I am working in Access 07
I am working In the Query desing window
the code I wrote was put in the criteria row for each field,regular "Normal if" function does not work in this row(or query). the function that is made to be use is "IIF" and is use as («expr», «truepart», «falsepart»)
I wasnt able to make it work so I decided to make every field "required" this fix the problem because there were no Null values(I know it was the cheap way out but I was really fustrated.

if you can explain your code I might be able to grasp the concept and try it out.

thanks for taking time to help

 

[jwhite]

His code will first check if [Field2] on the Form is Null. If it is, varSecondParameter will be set to "*" so your resulting query will not restrict returned records based on this field. If it is not Null, then varSecondParameter is set to the entered value for [Field2] on the Form. He then gave you the code to build your SQL Query, which then would be ready to assign as the RecordSource for a Form or Report. This code would go inside a Procedure on your Form where the User is selecting/entering the Criteria, not in a Stored Query.

Here are a few examples of the technique, known as Query By Form:

• Creating Dynamic Queries using Class Modules
• Search criteria (QBF)
• Sample Search Form (QBF)
• Adding a (Dynamic) Search form to a database (QBF)
• Dynamic Query Examples (QBF) (TextBox, ComboBox, Multi-Select ListBox)
• How to implement Query by Form (QBF) with dynamic QueryDef in Access 2000
• How to use the query by form (QBF) technique
• Multiple keyword search in multiple fields (free text) (QBF, QBQ)
• Universal Report Filter (QBF, URF)
• SearchBox, Query By Form Example (QBF)

 

[tranchemontaigne]

I'm guessing that you want to see an example of the approach I described. Here is one I wrote several years ago.

The premise is as follows:

(1) [Forms]![frmNameLookup]![lstPeople] shows a listing of people identified as cases or contacts throught the course of a major hepatitis outbreak

(2) [Forms]![frmSearch] has a combo box ([Forms]![frmSearch]![cboSearchField]) that displays two columns (column count = 2). The first column pulls field names, the second column pulls more "human friendly descriptions" of the filed. The column width for the first element is set to zero so it will not be displayed, but will be called when the value of this control is called by code. The next key field to note is a text box ([Forms]![frmSearch]![txtSearchTerm]) where a user can type letters (or criteria) used to filter results.

(3) Clicking on the [Forms]![frmSearch]![cmdSearch] command button causes the row source property of [Forms]![frmNameLookup]![lstPeople] to be replaced with an SQL statement that shows only the people who match the criteria specified on [Forms]![frmSearch]

(4) At some point a 3rd form (frmSearchList_Person_First_Name_Count) is momentarily opened to determine if the search criteria actually supplies any matches. If no matches are found than all records where the first character is a match will be displayed. If I were to write this today, I would determine whether there are any record matches without the overhead of opening and closing a form.

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim strSearchCriteria As String
Dim strColumnWidths As String

'set values
strSearchCriteria = [Forms]![frmSearch]![cboSearchField].Value

'define SQL clauses to be used when building the row source property
strSELECT = "SELECT Person_ID, [Case#], Person_Name_Last, Person_Name_First, Person_City, Age, Drugs, MSM, [Int'l_Travel], [Close_Contact_To_Case(HH)/(Sex)], [DayCare_Child/Employee], Food_Worker"
strFROM = "FROM tblPeople"
strORDERBY = "ORDER BY tblPeople.Person_Name_Last, tblPeople.Person_Name_First ;"

'column width property
strColumnWidths = "0" & Chr(34) & "; 0.3" & Chr(34) & "; 1.7" & Chr(34) & ";1.7" & Chr(34) & ";0.8" & Chr(34) & ";0.3" & Chr(34) & ";0.65" & Chr(34) & ";0.3" & Chr(34) & ";0.25" & Chr(34) & ";0.8" & Chr(34) & ";0.5" & Chr(34) & ";0.4" & Chr(34)

'dynamic generation of the WHERE clause
Select Case strSearchCriteria
Case "Person_Name_Last"
'determine if any matches are found
DoCmd.OpenForm "frmSearchList_Person_Last_Name_Count"

'if matches are found
If [Forms]![frmSearchList_Person_Last_Name_Count]![txtListCount].Value > 0 Then
DoCmd.Close acForm, "frmSearchList_Person_Last_Name_Count"
strWHERE = "WHERE (((Person_Name_Last) Like " & Chr(34) & [Forms]![frmSearch]![txtSearchTerm].Value & "*" & Chr(34) & "))"

'if no records are found with search
Else
DoCmd.Close acForm, "frmSearchList_Person_Last_Name_Count"
strWHERE = "WHERE (((Person_Name_Last) Like " & Chr(34) & Left([Forms]![frmSearch]![txtSearchTerm].Value, 1) & "*" & Chr(34) & "))"

End If

With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With


Case "Person_Name_First"
'determine if any matches are found
DoCmd.OpenForm "frmSearchList_Person_First_Name_Count"

'if matches are found
If [Forms]![frmSearchList_Person_First_Name_Count]![txtListCount].Value > 0 Then
DoCmd.Close acForm, "frmSearchList_Person_First_Name_Count"
strWHERE = "WHERE (((Person_Name_First) Like " & Chr(34) & [Forms]![frmSearch]![txtSearchTerm].Value & "*" & Chr(34) & "))"

'if no records are found with search
Else
DoCmd.Close acForm, "frmSearchList_Person_First_Name_Count"
strWHERE = "WHERE (((Person_Name_First) Like " & Chr(34) & Left([Forms]![frmSearch]![txtSearchTerm].Value, 1) & "*" & Chr(34) & "))"
End If

With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With


Case "Person_City"
'determine if any matches are found
DoCmd.OpenForm "frmSearchList_Person_City_Count"

'if matches are found
If [Forms]![frmSearchList_Person_City_Count]![txtListCount].Value > 0 Then
DoCmd.Close acForm, "frmSearchList_Person_City_Count"
strWHERE = "WHERE (((Person_City) Like " & Chr(34) & [Forms]![frmSearch]![txtSearchTerm].Value & "*" & Chr(34) & "))"

'if no records are found with search
Else
DoCmd.Close acForm, "frmSearchList_Person_City_Count"
strWHERE = "WHERE (((Person_City) Like " & Chr(34) & Left([Forms]![frmSearch]![txtSearchTerm].Value, 1) & "*" & Chr(34) & "))"
End If

With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With


Case "Age"
strWHERE = "WHERE (((tblPeople.Age) Between [Forms]![frmSearch]![txtSearchTerm] And [Forms]![frmSearch]![txtEnd]))"
With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With


Case "Drugs"
strWHERE = "WHERE (((Drugs) Like " & Chr(34) & [Forms]![frmSearch]![txtSearchTerm].Value & "*" & Chr(34) & "))"

With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With

Case "Food_Worker"
strWHERE = "WHERE (((Food_Worker) Like " & Chr(34) & [Forms]![frmSearch]![txtSearchTerm].Value & "*" & Chr(34) & "))"

With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With


Case "DayCare_Child/Employee"
strWHERE = "WHERE ((([DayCare_Child/Employee]) Like " & Chr(34) & [Forms]![frmSearch]![txtSearchTerm].Value & "*" & Chr(34) & "))"

With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With

Case "Close_Contact_To_Case(HH)/(Sex)"
strWHERE = "WHERE (((tblPeople.[Close_Contact_To_Case(HH)/(Sex)]) Like [Forms]![frmSearch]![txtSearchTerm]))"
With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With


Case "Hep_C+/other_pre-existing_condition"
strWHERE = "WHERE (((tblPeople.[Hep_C+/other_pre-existing_condition]) Like [Forms]![frmSearch]![txtSearchTerm]))"
With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With



Case ">2_Sexual_Partners"
strWHERE = "WHERE (((tblPeople.[>2_Sexual_Partners]) Like [Forms]![frmSearch]![txtSearchTerm]))"
With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With


Case "MSM"
strWHERE = "WHERE (((tblPeople.MSM) Like [Forms]![frmSearch]![txtSearchTerm]))"
With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With


Case "Ate_Raw_Shellfish"
strWHERE = "WHERE (((tblPeople.Ate_Raw_Shellfish) Like [Forms]![frmSearch]![txtSearchTerm]))"
With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With


Case "IG_Provider"
strWHERE = "WHERE (((tblPeople.IG_Provider) Like [Forms]![frmSearch]![txtSearchTerm]))"
With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With


Case "IG_Not_Given_Reason"
strWHERE = "WHERE (((tblPeople.IG_Not_Given_Reason) Like [Forms]![frmSearch]![txtSearchTerm]))"
With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With


Case "Food_Waterborne_OB"
strWHERE = "WHERE (((tblPeople.Food_Waterborne_OB) Like [Forms]![frmSearch]![txtSearchTerm]))"
With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With


Case "Arrested_Last_6_Months"
strWHERE = "WHERE (((tblPeople.Arrested_Last_6_Months) Like [Forms]![frmSearch]![txtSearchTerm]))"
With [Forms]![frmNameLookup]![txtTitle]
.Value = "Cases and Contacts"
.Requery
End With

Case "Rest_Name"
strSELECT = "SELECT tblRestList.Rest_ID, tblRestList.Rest_Name, tblRestList.Rest_Address, tblRestList.Rest_City, tblRestList.Rest_State, tblRestList.Rest_Phone "
strFROM = "FROM tblRestList "
strORDERBY = "ORDER BY tblRestList.Rest_Name; "
strColumnWidths = "0" & Chr(34) & ";1.7" & Chr(34) & ";1.7" & Chr(34) & ";0.8" & Chr(34) & ";0.3" & Chr(34) & ";0.65" & Chr(34) & ";0.3" & Chr(34) & ";0.25" & Chr(34) & ";0.8" & Chr(34) & ";0.5" & Chr(34) & ";0.4" & Chr(34)

With [Forms]![frmNameLookup]![txtTitle]
.Enabled = True
.Value = "Restaurants"
.Requery
.Enabled = False
End With

'determine if any matches are found
DoCmd.OpenForm "frmSearchList_Rest_List_Count"

'if matches are found
If [Forms]![frmSearchList_Rest_List_Count]![txtListCount].Value > 0 Then
strWHERE = "WHERE ((( tblRestList.Rest_Name ) Like " & Chr(34) & [Forms]![frmSearch]![txtSearchTerm].Value & "*" & Chr(34) & ")) "
DoCmd.Close acForm, "frmSearchList_Rest_List_Count"
' Debug.Print "strWHERE: "; strWHERE

'if no records are found with search
Else
strWHERE = "WHERE ((( tblRestList.Rest_Name ) Like " & Left([Forms]![frmSearch]![txtSearchTerm].Value, 1) & "*" & Chr(34) & ")) "
DoCmd.Close acForm, "frmSearchList_Rest_List_Count"
End If
Case Else
strWHERE = ""

End Select

'reset search form search criteria and close form
strSQL = strSELECT & " " & strFROM & " " & strWHERE & " " & strORDERBY

With [Forms]![frmNameLookup]![lstPeople]
.ColumnWidths = strColumnWidths
.ColumnHeads = True
.RowSource = strSQL
.Requery
End With

[Forms]![frmSearch]![txtSearchTerm].Value = Null
DoCmd.Close acForm, "frmSearch"


Exit_cmdSearch_Click:
Exit Sub

Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub

________
Geo/chevrolet prizm history

 

[JonTheOne]

this is definetly way over my head, I dont even know where to go and write this in to. maybe I need to go somewhere to learn how to put code in access

 

[jwhite]

Which suggestion?

I think his first suggestion is basically what you need. To understand how QBF's work, check out the links that I provided. Some are basic while others are more complex.

 

[tranchemontaigne]

JonTheOne,

Sorry for overloading you with detail. Here is my quicky introduction to VBA.


STEPS FOR ASSIGNING CODE TO A FORM OR FORM CONTROL

(1) bring a command button onto a form from the form toolbar
(2) cancel out of the wizard
(3) open the PROPERTIES window for the command button
(4) go to the EVENT tab of the PROPERTIES window
(5) click in the ONCLICK field in the PROPERTIES window
(6) click on the ellipsis (three dots) that appear to the right of the ONCLICK field
(7) from the CHOSE BUILDER popup, select CODE BUILDER
(8) at the top of the code window that opens, type OPTION EXPLICIT just underneath the OPTION COMPARE DATABASE line (this is an optional step, but will save you a lot of headaches)
(9) copy/paste my posting from 19-Dec-08



USING THE VBA DEVELOPMENT ENVIRONMENT

Within the CODE DEVELOPMENT area of MS Access there are a few things you need to know.
(1) from the DEBUG menubar option, there is a COMPILE option. Always compile your code. If there are errors the compiler will tell you about them.
(2) after you compile successfully, always SAVE your code
(3) not all code samples you will find will work unless you reference the code library where functions are defined. If you need to reference another code library, select TOOLS from the menubar and then the REFERENCES option. From here you can specify the library you need. Most of the time you will not need to reference external libraries, but I mention this so that you will be aware of it.
(4) within the code window, if you click on the gray left margin next to a line of executable code a dot will appear. This dot is called a BREAKPOINT. By putting lots of breakpoints into your code you will be able to figure out which line causes errors when debugging code. If you click on a break point it will go away. If you are "stepping through your code" to find out where an error is occuring, you will need to click on the RUN button (it has a triangle pointing to the right) to resume running code. To exit from a piece of running code click the RESET button (it displays a square)

NOTE: If you are editing running code you are allowed to "compile" and "save" the running code, but your changes may not actually be saved. It is good practice to RESET running code before compiling and saving.



THE FIRST STEPS TO LEARNING MS ACCESS VBA

Use the wizard to write code for you, review the code and see what is needed to make an operation happen. This has been referred to by some as learning by osmosis, but I'll let you think of it any way you want.

Note that the wizard will write error handling into the generated code. Look at this until you figure out how to write your own error handling. Error handling will help keep MS Access from acting up due to memory resident processes that may lock up the MS Access database.



NEXT STEPS

Explore the DOCMD method by typing DOCMD. (dot) and see all the options that are available. This is one of the most used methods and the one I would recommend that anyone looking to learn how to code in MS Access VBA. Some of the more useful DOCMD options are .OPENQUERY, .OPENFORM, .OUTPUTTO, .TRANSFERSPREADSHEET, .SETWARNINGS, .SENDOBJECT, .OPENREPORT, .PRINT, .BEEP, .HOURGLASS

You should also play around with the MSGBOX method.



CLOSING COMMENTS

I suspect that someone else could give you a good MS Access VBA tutorial URL if you asked. Unfortunately I have none to offer.

I'm not formally trained as a coder and did not know that the QBF approach even had a name. What I can do is say that if you think about a problem and are willing to experiment, then you will be able to do a lot.

Once you have figured out how to use the DOCMD method and write error handling, then your next steps would be to learn the IF...ELSE...END IF and SELECT CASE...END SELECT control structures. WITH...END WITH is also useful for reducing the number of characters you have to type, but is not a control structure.

Other control structures are harder to use without first earning recordsets.

Welcome to the world of coding - I've found it to be quite rewarding. Once you get the hang of it you will be able to make MS Access automate all sorts of tedious work.



EXTRA BIG NOTE: My experience with coding in MS Access has been limited to MS Access '97 and 2K. Newer versions might be different than I have outlined, but I suspect that they are similar enough for this to remain relevent.
________
GRAPE APE PICTURES

 

[jwhite]

IMO, all of this is overwhelming the OP...

 

[Mike375]

this is definetly way over my head, I dont even know where to go and write this in to. maybe I need to go somewhere to learn how to put code in access

Go to one of your forms in design view and right click on a textbox and then click Properties and then click Events

Events will list things like OnClick, LostFocus, DoubleClick, heaps of them. These are all the things that will make code or a macro run.

For each event you will see a drop down arrow and next to that are 3 dots. The drop down arrow is to select from macros in the DB. Click on the 3 dots and click CodeBuilder from the box that opens and that will take you to something that looks like

Private Sub CLSurname_Click()

End Sub

The code goes in between. In that example of I have clicked on the field CLSurname and for the Click event.

Private Sub Label2_Click()

End Sub

That was doing it on a stand alone label.

A2007 might be a bit different getting there but I think it will be basically the same.

After the code gone in then you will see [Event Procedure] to the left of the drop down arrown and 3 dots. If it was a macro then you would have the macro name.

 

[jwhite]

All this advice is good, no doubt, but it's too much for Jon to understand at his learning stage. I would recommend getting one or a couple introductory MS Access books for your version of Access. Also, these sites can help:

ACCESS Basics for Programming (by Chrystal on UA)
FunctionX Tutorials
Access 2007 Courses
Access 2007 Demo: Meet the Navigation Pane
DataPig Access Tutorials (Video Format)

 

[JonTheOne]

Thank you all for all this help.

I will try to learn VBA and how to apply it to Access