Searching

[Andrew C]

Hi

Currently I have a form where staff members can search for clients. It
shows Surname, First and Phone. It also has the client ID and Active Status
of the client (but they are hidden)

My current code on exiting from searchbox is as follows:

Private Sub SearchBox_Exit(Cancel As Integer)
Me.List.RowSource = "SELECT [Clients Active].ClientID, [Clients
Active].Surname, [Clients Active].First, [Clients Active].Active,[Clients
Active].Phone FROM [Clients Active] WHERE ((([Clients Active].Surname) Like
'*" & Me.SearchBox & "*') AND (([Clients Active].Active)=yes)) ORDER BY
Client.Surname"
End Sub

What i want to add in the search form to include Acitve/Inactive Search
option. So when staff is searching they can choose whether they wish to
seach the active or inactive clients.

I have added the options and they get vales of Active = 1, Inactive = 2.
these values are not stored anywhere. Unfortunatley the Clients table has
Active status listed as either Yes or No. So i need it to relate active =
yes and inactive = no when it does its search

Is there anyway of being able to change my current code in incorporate this.

Not sure if i have explained it clear enough but if you have any questions
just ask.

Thanks

Andrew

 

[niuginikiwi]

Take a look at Allen Browne's (Access MVP) search utility at
http://allenbrowne.com

 

[John W. Vinson]

I have added the options and they get vales of Active = 1, Inactive = 2.
these values are not stored anywhere. Unfortunatley the Clients table has
Active status listed as either Yes or No. So i need it to relate active =
yes and inactive = no when it does its search

Use a criterion on the Active field of

[Forms]![yourformname]![optiongroupname] = 1

This expression will be True if they're looking for active clients, False if
they're looking for inactive ones.

This presumes that the status field is in fact a Yes/No field in your table
(which is actually stored as -1 for Yes, 0 for No, though the suggestion above
will work).

John W. Vinson [MVP]