RE: Search criteria

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



--
Daryl S


"TonyWilliams via AccessMonster.com" wrote:

> 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
>
> --
> Why don't my grey cells communicate with each as fast as they used to? I hate
> getting old!
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...oding/201002/1
>
> .
>