Search criteria

[TonyWilliams via AccessMonster.com]

I have a search form that selects records from a query when the value of two
fields (txtmonth-a date field and txtcompnay- a text field) in the query
match the value of two controls (txtsearch and txtsearch2) on my form.
However I want to change the code so that the serach criteria works like this:

Find records where:
txtsearch=txtcompany and txtsearch2 is blank
txtsearch2=txtmonth and txtsearch is blank
txtsearch=txtcompany AND txtsearch2=txtmonth

Here is my current code:
Private Sub txtSearch_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
'execute search
' Turn Off Screen Display while macros run
DoCmd.Echo False, "Please wait while the search takes place "
' Turns on hourglass while macro runs
DoCmd.Hourglass True
' Turns warning off so you don't answer prompt while macro runs
DoCmd.SetWarnings False

Debug.Print "Search for " & Me.txtsearch.Value
Debug.Print "Search for " & Me.txtsearch2.Value
Me.RecordSource = "SELECT * FROM [qrysearchHV] WHERE [txtcompany]
like ""*" & Me.txtsearch.Value & "*"" AND [txtmonth] like ""*" & Me.
txtsearch2.Value & "*"""

Me.btnShowAll.Visible = True
'Me.lblSearch.Visible = False
If Me.RecordsetClone.RecordCount = 0 Then
Me.RecordSource = "qrysearchHV"
Me.txtsearch.SetFocus
Me.btnShowAll.Visible = False
MsgBox "No records found for your search criteria", vbInformation,
strAppName
'Me.lblSearch.Visible = True
End If
End If
' Turns off hourglass
DoCmd.Hourglass False
' Turns warnings back on
DoCmd.SetWarnings True
' Activates Screen display again
DoCmd.Echo True, ""
End Sub

Can someone give me a steer on how I can change the code to achieve this?
Thanks
Tony

 

[Marshall Barton]

I have a search form that selects records from a query when the value of two
fields (txtmonth-a date field and txtcompnay- a text field) in the query
match the value of two controls (txtsearch and txtsearch2) on my form.
However I want to change the code so that the serach criteria works like this:

Find records where:
txtsearch=txtcompany and txtsearch2 is blank
txtsearch2=txtmonth and txtsearch is blank
txtsearch=txtcompany AND txtsearch2=txtmonth

Here is my current code:
Private Sub txtSearch_KeyPress(KeyAscii As Integer)
If KeyAscii = 13 Then
'execute search
' Turn Off Screen Display while macros run
DoCmd.Echo False, "Please wait while the search takes place "
' Turns on hourglass while macro runs
DoCmd.Hourglass True
' Turns warning off so you don't answer prompt while macro runs
DoCmd.SetWarnings False

Debug.Print "Search for " & Me.txtsearch.Value
Debug.Print "Search for " & Me.txtsearch2.Value
Me.RecordSource = "SELECT * FROM [qrysearchHV] WHERE [txtcompany]
like ""*" & Me.txtsearch.Value & "*"" AND [txtmonth] like ""*" & Me.
txtsearch2.Value & "*"""

Me.btnShowAll.Visible = True
'Me.lblSearch.Visible = False
If Me.RecordsetClone.RecordCount = 0 Then
Me.RecordSource = "qrysearchHV"
Me.txtsearch.SetFocus
Me.btnShowAll.Visible = False
MsgBox "No records found for your search criteria", vbInformation,
strAppName
'Me.lblSearch.Visible = True
End If
End If
' Turns off hourglass
DoCmd.Hourglass False
' Turns warnings back on
DoCmd.SetWarnings True
' Activates Screen display again
DoCmd.Echo True, ""
End Sub

Const SQL As String = "SELECT * FROM qrysearchHV "
Dim strWhere As String

If Not IsNull(Me.txtsearch) Then
strWhere = " And txtcompany Like ""*" & Me.txtsearch &
"*"" "
End If
If Not IsNull(Me.txtsearch2) Then
strWhere = " And txtmonth Like ""*" & Me.txtsearch2 &
"*"" "
End If

If strWhere = "" Then
Me.RecordSource = SQL
Else
Me.RecordSource = SQL & "WHERE" & Mid(strWhere, 6)
End If

I don't understamd why you have that code in a keyboard
event instead of your search button's Click event.

 

[Daryl S]

Tony -

Is this what you want?

If isnull(txtsearch2) Then
Me.RecordSource = "SELECT * FROM [qrysearchHV] WHERE [txtcompany]like
""*" & _
Me.txtsearch.Value & "*""
Else
If isnull(txtsearch) Then
Me.RecordSource = "SELECT * FROM [qrysearchHV] WHERE [txtmonth] like
""*" & _
Me.txtsearch2.Value & "*"""
Else

Me.RecordSource = "SELECT * FROM [qrysearchHV] WHERE
[txtcompany]like ""*" & _
Me.txtsearch.Value & "*"" AND [txtmonth] like ""*" & _
Me.txtsearch2.Value & "*"""
End If
End If

 

[TonyWilliams via AccessMonster.com]

Thanks Marshall. The code is on a keyboard event because on pressing the
return key the search populate a subform with the results. I then selkect a
record from the sub form and open another form. I think iI got the idea for
this serach form from one of the MVPS' sites.
Thanks for your help with the code.
Tony

I have a search form that selects records from a query when the value of two
fields (txtmonth-a date field and txtcompnay- a text field) in the query

[quoted text clipped - 41 lines]

DoCmd.Echo True, ""
End Sub

Const SQL As String = "SELECT * FROM qrysearchHV "
Dim strWhere As String

If Not IsNull(Me.txtsearch) Then
strWhere = " And txtcompany Like ""*" & Me.txtsearch &
"*"" "
End If
If Not IsNull(Me.txtsearch2) Then
strWhere = " And txtmonth Like ""*" & Me.txtsearch2 &
"*"" "
End If

If strWhere = "" Then
Me.RecordSource = SQL
Else
Me.RecordSource = SQL & "WHERE" & Mid(strWhere, 6)
End If

I don't understamd why you have that code in a keyboard
event instead of your search button's Click event.

 

[TonyWilliams via AccessMonster.com]

Thanks Daryl I'll try both yours and Marshall's suggestions.
Thanks again
Tony

Tony -

Is this what you want?

If isnull(txtsearch2) Then
Me.RecordSource = "SELECT * FROM [qrysearchHV] WHERE [txtcompany]like
""*" & _
Me.txtsearch.Value & "*""
Else
If isnull(txtsearch) Then
Me.RecordSource = "SELECT * FROM [qrysearchHV] WHERE [txtmonth] like
""*" & _
Me.txtsearch2.Value & "*"""
Else

Me.RecordSource = "SELECT * FROM [qrysearchHV] WHERE
[txtcompany]like ""*" & _
Me.txtsearch.Value & "*"" AND [txtmonth] like ""*" & _
Me.txtsearch2.Value & "*"""
End If
End If

I have a search form that selects records from a query when the value of two
fields (txtmonth-a date field and txtcompnay- a text field) in the query

[quoted text clipped - 45 lines]

Thanks
Tony

 

[Marshall Barton]

Thanks Marshall. The code is on a keyboard event because on pressing the
return key the search populate a subform with the results. I then selkect a
record from the sub form and open another form. I think iI got the idea for
this serach form from one of the MVPS' sites.

Wouldn't the AfterUpdate event do the same thing? Hmmm, I
guess the difference is the search will not be done if the
mouse is used to select a different control. Maybe that's
what you are trying to separate by using the keyboard
event??

 

[TonyWilliams via AccessMonster.com]

I'm not sure of the answer to your question Marshall. As I said I've adapted
my search form from an example I found on one of your fellow MVPS sites so
I'm not sure of the logic. BUT it works so I'm pleased about that.
Thanks again
Tony

 

[TonyWilliams via AccessMonster.com]

Marshall I get a syntax error in this line:
Me.RecordSource = SQL & "WHERE" & Mid(strWhere, 6)
Could you help?
Thanks
Tony

I have a search form that selects records from a query when the value of two
fields (txtmonth-a date field and txtcompnay- a text field) in the query

[quoted text clipped - 41 lines]

DoCmd.Echo True, ""
End Sub

Const SQL As String = "SELECT * FROM qrysearchHV "
Dim strWhere As String

If Not IsNull(Me.txtsearch) Then
strWhere = " And txtcompany Like ""*" & Me.txtsearch &
"*"" "
End If
If Not IsNull(Me.txtsearch2) Then
strWhere = " And txtmonth Like ""*" & Me.txtsearch2 &
"*"" "
End If

If strWhere = "" Then
Me.RecordSource = SQL
Else
Me.RecordSource = SQL & "WHERE" & Mid(strWhere, 6)
End If

I don't understamd why you have that code in a keyboard
event instead of your search button's Click event.

 

[Marshall Barton]

Marshall I get a syntax error in this line:
Me.RecordSource = SQL & "WHERE" & Mid(strWhere, 6)

There's supposed to be a space after Where:

Me.RecordSource = SQL & "WHERE " & Mid(strWhere, 6)

 

[TonyWilliams via AccessMonster.com]

Thanks Marshall that's fine!
Tony

There's supposed to be a space after Where:

Me.RecordSource = SQL & "WHERE " & Mid(strWhere, 6)