Access 2007 Search Form

[KC_Cheer_Coach]

I am working in Access 2007 and have a search form with multiple fields on
it. The search function works on all the fields except for a few. There are
some check boxes that are not returning any records to the subform. The
subform opens, blinks, and shows 0 rows. I need the code written to show that
when a box is checked and a certain "text" field or "number" field have
anything at all in them, then those rows will be returned. For instance, if
the SDSubmitted box is checked, then I want all rows where number field
ServiceDesk has information in it. And, if the IDTSubmitted box is checked,
then I want all rows where text field IDTNumber has information in it. I have
been working on this for a while and can't get it right.

frmSearch is the search form including frmIssuesSubform which returns data
from the main table tblIssues.

The code that I have borrowed from an Access template and have been working
on is this:

' If SD Submitted
If Nz(Me.SDSubmitted) <> "" Then
strWhere = strWhere & " AND " & "tblIssues.ServiceDesk Like '*" &
Me.SDSubmitted & "*'"
End If

' If IDT Submitted
If Nz(Me.IDTSubmitted) <> "" Then
strWhere = strWhere & " AND " & "tblIssues.IDTNumber Like '*" &
Me.IDTSubmitted & "*'"
End If

Can someone help me? With the way it is written, it will return everything I
want IF the text and number fields described above were originally
checkboxes.

 

[Dale_Fye via AccessMonster.com]

Before we answer this, the key question is:

Well, the problem looks to be that you are checking to see whether either of
the checkboxes is checked, and then writing a criteria that checks for:

[Service Desk] Like '*-1*'
and
[IDTNumber Like '*-1*'

It is no wonder you are not returning any records. Lets assume you have a
control (maybe a text box) for the "Service Desk"; we'll calll this control
txt_ServiceDesk. Then your code should look like:

' If SD Submitted
If Me.SDSubmitted = True Then
strWhere = strWhere & " AND " _
& "tblIssues.ServiceDesk Like '*" & Me.txt_ServiceDesk &
"*'"
End If

The problem with this is that it doesn't check to see whether there is any
text in txt_ServiceDesk, and if this field is blank, then it will return all
the records except where field [ServiceDesk] is NULL, which may be what you
want.

HTH
Dale

 

[KC_Cheer_Coach]

Oh geez! That was so easy and works beautifully. I cannot believe I didn't
think of that. Thank you so much!

Before we answer this, the key question is:

Well, the problem looks to be that you are checking to see whether either of
the checkboxes is checked, and then writing a criteria that checks for:

[Service Desk] Like '*-1*'
and
[IDTNumber Like '*-1*'

It is no wonder you are not returning any records. Lets assume you have a
control (maybe a text box) for the "Service Desk"; we'll calll this control
txt_ServiceDesk. Then your code should look like:

' If SD Submitted
If Me.SDSubmitted = True Then
strWhere = strWhere & " AND " _
& "tblIssues.ServiceDesk Like '*" & Me.txt_ServiceDesk &
"*'"
End If

The problem with this is that it doesn't check to see whether there is any
text in txt_ServiceDesk, and if this field is blank, then it will return all
the records except where field [ServiceDesk] is NULL, which may be what you
want.

HTH
Dale

I am working in Access 2007 and have a search form with multiple fields on
it. The search function works on all the fields except for a few. There are
some check boxes that are not returning any records to the subform. The
subform opens, blinks, and shows 0 rows. I need the code written to show that
when a box is checked and a certain "text" field or "number" field have
anything at all in them, then those rows will be returned. For instance, if
the SDSubmitted box is checked, then I want all rows where number field
ServiceDesk has information in it. And, if the IDTSubmitted box is checked,
then I want all rows where text field IDTNumber has information in it. I have
been working on this for a while and can't get it right.

frmSearch is the search form including frmIssuesSubform which returns data
from the main table tblIssues.

The code that I have borrowed from an Access template and have been working
on is this:

' If SD Submitted
If Nz(Me.SDSubmitted) <> "" Then
strWhere = strWhere & " AND " & "tblIssues.ServiceDesk Like '*" &
Me.SDSubmitted & "*'"
End If

' If IDT Submitted
If Nz(Me.IDTSubmitted) <> "" Then
strWhere = strWhere & " AND " & "tblIssues.IDTNumber Like '*" &
Me.IDTSubmitted & "*'"
End If

Can someone help me? With the way it is written, it will return everything I
want IF the text and number fields described above were originally
checkboxes.

 

[Dale_Fye via AccessMonster.com]

You are welcome.

BTW, you might want to consider using a naming convention for your form
controls.

From looking at your code, it is impossible to determine the type of control
that you are referring to. With a properly implemented naming convention,
you can look at the code and tell immediately what type of control you are
dealing with.

Unfortunately, you have to do this manually, but if you do it from the start
of your project, you will never have any problems with it.

A good place to start is: http://www.mvps.org/access/general/gen0012.htm

Dale

Oh geez! That was so easy and works beautifully. I cannot believe I didn't
think of that. Thank you so much!

Before we answer this, the key question is:

[quoted text clipped - 56 lines]

 

[KC_Cheer_Coach]

I will do that before I implement the database. Thanks again.

You are welcome.

BTW, you might want to consider using a naming convention for your form
controls.

From looking at your code, it is impossible to determine the type of control
that you are referring to. With a properly implemented naming convention,
you can look at the code and tell immediately what type of control you are
dealing with.

Unfortunately, you have to do this manually, but if you do it from the start
of your project, you will never have any problems with it.

A good place to start is: http://www.mvps.org/access/general/gen0012.htm

Dale

Oh geez! That was so easy and works beautifully. I cannot believe I didn't
think of that. Thank you so much!

Before we answer this, the key question is:

[quoted text clipped - 56 lines]

want IF the text and number fields described above were originally
checkboxes.