i need example for an advanced search!!

[snoopy22]

hello guys,
i need to build a program which the user has an option to search with several criteria, for example. suppose the user type in the folowing control boxes:

name: matt
city: california

then he will be able to see the results(all the man with the name "matt"
which lives in california) in a list box

i've already tryed to look for examples on the internet(including this forum)
and i can't find anything usefull.

thanks in advance(sorry for the spelling)

 

[WayneRyan]

Snoopy,

Did you look at the search samples on this forum? Also, use the Search
facility here and look for "Cascading". There are examples of how to
use cascading combo-boxes to search.

Wayne

 

[24sharon]

>>>

First sure that you need do a table form.
After you built a form that the customer writes there
C_FirstName
C_LastName
C_country (list box)

Then you build a click and write there:

sfilter = "[@@@kod@@@]>0 "

If Not IsNull(C_FirstName) Then sfilter = sfilter & " and [FirstName] like " & """" & "*" & C_FirstName & "*" & """"
If Not IsNull(C_LastName) Then sfilter = sfilter & " and LastName] like " & """" & "*" & C_LastName
& "*" & """"
If C_country > 0 Then sfilter = sfilter & " and [Country] = " & C_country

DoCmd.openform "FORMNAME",, , sfilter

Hope that you understand me!

 

[EMP]

Then you build a click and write there:

sfilter = "[@@@kod@@@]>0 "
...........
...........
Hope that you understand me!

It's difficult to understand what sfilter = "[@@@kod@@@]>0 " does in the code.

And what should the user do when Access asks him/her to Enter Parameter Value for @@@kod@@@?

 

[snoopy22]

thank you for helping

but i'm having difficulty to understand the code, could you please write a short comment that explain each row?

 

[24sharon]

example...

maby it will help u

 

[snoopy22]

thank you sharon!

it's exactly what i need! but i'm afraid i didn't get the meaning of the following rows:

sfilter = "[nameID]>0 "

If Not IsNull(c_firstname) Then sfilter = sfilter & " and [FirstName] like " & """" & "*" & c_firstname & "*" & """"
If Not IsNull(c_lastname) Then sfilter = sfilter & " and LastName] like " & """" & "*" & c_lastname & "*" & """"
If c_city > 0 Then sfilter = sfilter & " and [cityID] = " & c_city

can you please explain it to me?

 

[Oldsoftboss]

Snoopy,

Did you look at the search samples on this forum? Also, use the Search
facility here.

Wayne

I use the sample I posted here....

http://www.access-programmers.co.uk/forums/showthread.php?t=63766

 

[24sharon]

i'll try...

1.sfilter = "[nameID]>0 "
2.If Not IsNull(c_firstname) Then sfilter = sfilter & " and [FirstName] like " & """" & "*" & c_firstname & "*" & """"
3.If Not IsNull(c_lastname) Then sfilter = sfilter & " and [LastName] like " & """" & "*" & c_lastname & "*" & """"
4. If c_city > 0 Then sfilter = sfilter & " and [cityID] = " & c_city
--------------------------------------------------------------------
1.
if you click and all the textboxes are null you need a sentence to filter that take all the rows.
nameID is an autonumber then sure its > from 0

2.
if the textbox "c_firstname" in the form not null (u write there something) then the sentence to filter bigger and now its like:
[nameID]>0 and [FirstName] like "*AB*"
3.
like 2.
[nameID]>0 and [LastName] like "*AB*" and [LastName] like "*CD*"

note:
" -u must write the " because the type ( [firstname] , [lastname]) is text then you need write before and after the search's word the ".

4.
c_city in the form is number, and the type of cityID is also number then you don't need write " before and after.
sentence to filter:
[nameID]>0 and [LastName] like "*AB*" and [LastName] like "*CD*" " and [cityID] =3

That's all…
Hope that you understand my broken words
s.

 

[snoopy22]

thanks man

I've got it, except for the part you're writing :

& """" & "*" & c_firstname & "*" & """"

why do i have to write 4 brackets at the begining and end of that
code, i've looked at the debug for sfilter and it gives me for example:
[FirstName] like "*mark*" and [cityID] = 1

so where do the 4 brackets fitting in?

p.s. toda raba

 

[24sharon]

snoopy22

when you write 4 brackets its write one in the sentance then you get
[FirstName] like "*mark*"
if you dont write the 4 brackets you get
[FirstName] like *mark* - and its not good


p.s.
behtzlacha
shana-tova

 

[snoopy22]

I see, thanks

i got it working now...

thank you all for posting

p.s. ata gever sof haderech