Help with LIKE operator/IfThenElse Statement

[sali]

I've been racking my brain trying to figure out how to write this, but my
brain can't think anymore. Here's my problem. I have a search form and
have the following code: Basically I want to say, if all the fields on the
form are empty, do the first condition, if all the fields on the form are
not empty and they equal to some value of what was entered, using the LIKE
operator, then open another form with that information pops up, the third
condition i would like to include in there is if the fields are not null,
but none of the fields are LIKE any of the data entered, then I want it to
pop up with a message box that says something like, No data was found...Im
thinking I need to write something in the effect as if Me.txtcasenumber NOT
LIKE so and so, but I just can't figure out how to do htat. Please help. I
hope this was clear enough and not too confusing. Thanks in advance.



stSQL = ("SELECT [Case Information].CaseNumber, [Case Information].Agency,
[Case Information].AgencyNumber, " & _
"[Case Information].OffenseID, [Case
Information].OffenseDate, [Victims].FirstName, " & _
"[Victims].LastName, [Suspects].FirstName,
[Suspects].LastName FROM ([Case Information] " & _
"INNER JOIN ([Case Analysis] INNER JOIN
Victims ON [Case Analysis].SubmissionID=Victims.SubmissionID) " & _
"ON [Case Information].CaseNumber=[Case
Analysis].CaseNumber) " & _
"INNER JOIN Suspects ON [Case
Analysis].SubmissionID=Suspects.SubmissionID")

If Nz(Me.txtCaseNumber, "") = "" And Nz(Me.txtAgencyName, "") = "" And
Nz(Me.txtAgencyNumber, "") = "" And _
Nz(Me.txtOffenseDate, "") = "" And Nz(Me.txtVictimFirstName, "") = ""
And Nz(Me.txtVictimLastName, "") = "" And _
Nz(Me.txtSuspectFirstName, "") = "" And Nz(Me.txtSuspectLastName, "")
= "" Then
iAnswer = MsgBox("No data was entered, do you want to
continue?", vbYesNo)
If iAnswer = vbYes Then
Me.txtCaseNumber.SetFocus
GoTo Exit_Sub
Else
GoTo Exit_Sub
End If

Else

If Nz(Me.txtCaseNumber, "") <> "" And Nz(Me.txtAgencyName, "") <> "" And
Nz(Me.txtAgencyNumber, "") = "" And _
Nz(Me.txtOffenseDate, "") <> "" And Nz(Me.txtVictimFirstName, "") <>
"" And Nz(Me.txtVictimLastName, "") <> "" And _
Nz(Me.txtSuspectFirstName, "") <> "" And Nz(Me.txtSuspectLastName,
"") <> "" Then
stSQL = stSQL & " [Case Information].CaseNumber = LIKE *'" &
Me.txtCaseNumber & "*'" Or _
"[Case Information].DateofOffense = LIKE *#" & Me.txtOffenseDate
& "*#" Or _
"[Victims].FirstName = LIKE *'" & Me.txtVictimFirstName & "*'"
Or _
"[Victims].LastName = LIKE *'" & Me.txtVictimLastName & "*'" Or
_
"[Suspects].FirstName = LIKE *'" & Me.txtSuspectFirstName & "*'"
Or _
"[Suspects].LastName = LIKE *'" & Me.txtSuspectLastName & "*'"

End If
End If

 

[Marshall Barton]

The most straightforward way to open a form and filter its
data is to use the OpenForm method's WhereCondition
argument. To do this the form's Recor Source need to be
your query without the where clause. This way, when you
open the form from the database window, it will have all the
records produced by the unfiltered query.

Your command button to open the form would then use a
modified version of your code:

If Nz(Me.txtCaseNumber, "") = "" And . . . Then
. . .
Else
If Nz(Me.txtCaseNumber, "") <> "" And . . . Then
stSQL = "[Case Information].CaseNumber Like '*" _
& Me.txtCaseNumber & "*'" _
& " Or [Case Information].DateofOffense = " _
& Format(Me.txtOffenseDate, "\#m\/d\/yyyy\#") _
& " OR [Victims].FirstName Like '*" _
& Me.txtVictimFirstName & "*'"
& " OR . . .
DoCmd.OpenForm "fromname", , , stSQL
End If
End If

Note that you can not use = and Like together, either one or
the other but not both. Also you should not use Like with a
date value.

As for checking if there is no data meeting the criteria,
the easiest way to do that is for the form to check that
itself in its Load event:

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No Data"
DoCmd.Close acForm, Me.Name
End If
--
Marsh
MVP [MS Access]

I've been racking my brain trying to figure out how to write this, but my
brain can't think anymore. Here's my problem. I have a search form and
have the following code: Basically I want to say, if all the fields on the
form are empty, do the first condition, if all the fields on the form are
not empty and they equal to some value of what was entered, using the LIKE
operator, then open another form with that information pops up, the third
condition i would like to include in there is if the fields are not null,
but none of the fields are LIKE any of the data entered, then I want it to
pop up with a message box that says something like, No data was found...Im
thinking I need to write something in the effect as if Me.txtcasenumber NOT
LIKE so and so, but I just can't figure out how to do htat. Please help. I
hope this was clear enough and not too confusing. Thanks in advance.



stSQL = ("SELECT [Case Information].CaseNumber, [Case Information].Agency,
[Case Information].AgencyNumber, " & _
"[Case Information].OffenseID, [Case
Information].OffenseDate, [Victims].FirstName, " & _
"[Victims].LastName, [Suspects].FirstName,
[Suspects].LastName FROM ([Case Information] " & _
"INNER JOIN ([Case Analysis] INNER JOIN
Victims ON [Case Analysis].SubmissionID=Victims.SubmissionID) " & _
"ON [Case Information].CaseNumber=[Case
Analysis].CaseNumber) " & _
"INNER JOIN Suspects ON [Case
Analysis].SubmissionID=Suspects.SubmissionID")

If Nz(Me.txtCaseNumber, "") = "" And Nz(Me.txtAgencyName, "") = "" And
Nz(Me.txtAgencyNumber, "") = "" And _
Nz(Me.txtOffenseDate, "") = "" And Nz(Me.txtVictimFirstName, "") = ""
And Nz(Me.txtVictimLastName, "") = "" And _
Nz(Me.txtSuspectFirstName, "") = "" And Nz(Me.txtSuspectLastName, "")
= "" Then
iAnswer = MsgBox("No data was entered, do you want to
continue?", vbYesNo)
If iAnswer = vbYes Then
Me.txtCaseNumber.SetFocus
GoTo Exit_Sub
Else
GoTo Exit_Sub
End If

Else

If Nz(Me.txtCaseNumber, "") <> "" And Nz(Me.txtAgencyName, "") <> "" And
Nz(Me.txtAgencyNumber, "") = "" And _
Nz(Me.txtOffenseDate, "") <> "" And Nz(Me.txtVictimFirstName, "") <>
"" And Nz(Me.txtVictimLastName, "") <> "" And _
Nz(Me.txtSuspectFirstName, "") <> "" And Nz(Me.txtSuspectLastName,
"") <> "" Then
stSQL = stSQL & " [Case Information].CaseNumber = LIKE *'" &
Me.txtCaseNumber & "*'" Or _
"[Case Information].DateofOffense = LIKE *#" & Me.txtOffenseDate
& "*#" Or _
"[Victims].FirstName = LIKE *'" & Me.txtVictimFirstName & "*'"
Or _
"[Victims].LastName = LIKE *'" & Me.txtVictimLastName & "*'" Or
_
"[Suspects].FirstName = LIKE *'" & Me.txtSuspectFirstName & "*'"
Or _
"[Suspects].LastName = LIKE *'" & Me.txtSuspectLastName & "*'"

End If
End If