How to code a search based on the data from several fields

[Guest]

I am trying to code a search based on the data from serveral fields and will
appreciate anyone who will give me a hand.

Thanks!

 

[Guest]

s = "select * from mytable" & buildwhere (organisationid , organisationstreet)

function buildwhere(orgid as integer, orgstreet as string)
where = ""
temp = ""

if orgid <> 0 then
where = where & temp & "organisationid=" & orgid
temp = " and "
end if

if orgstreet <> "" then
where = where & temp & "organisationstreet='" & orgstreet & "'"
temp = " and "
end if

if len (where) > 0 then
where = "where " & where
end if
buildwhere = where

end function

 

[Guest]

another idea is to use a temporary table which stores a userid and a primary
key of your search table (e.g. org).

Like that you can easily insert
s = "insert into temptable (userid, orgid) " & _
"select " & userid & ", orgid from org where [your condition]"
currentdb.execute s

afterward it's easy (and quick) to select the needed records from your table

s = "select * from org inner join temptable on temptable.orgid = org.id
where temptable.userid = " & userid