vba - VB Form and MS Access SQL Wildcard Search -

my table table1 has 3 fields: fname, lname, phone. using microsoft access 2010 running sql query. rows has empty / null phone values.

i have vb form accepts search parameters. user can enter (fname , lname) or (phone), not both @ same time.

when try:

select table1.lname, table1.fname, table1.phone table1 table1.lname ('*' & forms!frmsearchmain!lname & '*') , table1.fname ('*' & forms!frmsearchmain!fname & '*') order table1.lname, table1.fname;

it gives me list of user matching given (fname , lname) parameters. works fine.

similarly, when try:

select table1.lname, table1.fname, table1.phone table1 table1.phone ('*' & forms!frmsearchmain!phone & '*') order table1.lname, table1.fname;

it gives me list of user matching given (phone) parameter. works fine too.

but, when combine both these queries:

select table1.lname, table1.fname, table1.phone table1 table1.phone ('*' & forms!frmsearchmain!phone & '*') , table1.lname ('*' & forms!frmsearchmain!lname & '*') , table1.fname ('*' & forms!frmsearchmain!fname & '*') order table1.lname, table1.fname;

it doesn't give me expected results.

i tried or condition between (fname , lname) , (phone) search parameters. doesn't work. have tried nz(frmsearchmain!phone,"") search params, % instead of *, no luck far.

i new ms access sql query format. have used type of queries lot of times in mysql. couldn't expected results one.

any appreciated.

i think should easier sort 1 out if simplify it. arbitrarily decided ignore lname now, , searching based on fname or phone.

in version of frmsearchmain, named text box holds search target value fname txtsearchfname because prefer name control differently record source field. similarly, chose txtsearchphone phone search target text box.

since had table named table1, called mine tblkeerthiram instead.

with changes, query gives me rows match txtsearchfname or txtsearchphone. if both txtsearchfname , txtsearchphone null, query return rows table ... hope want.

select     t1.id,     t1.fname,     t1.phone tblkeerthiram t1            (t1.fname "*"                & [forms]![frmsearchmain]![txtsearchfname]                & "*"         or [forms]![frmsearchmain]![txtsearchfname] null)     ,            (t1.phone "*"                & [forms]![frmsearchmain]![txtsearchphone]                & "*"         or [forms]![frmsearchmain]![txtsearchphone] null) order t1.fname; 

a consequence of approach that, if both txtsearchfname , txtsearchphone non-null, query return rows match both. understood description, not want. want search 1 or other, not both @ same time.

in case suggest use after update events 2 search text boxes set other null when non-null value has been entered 1 of them. i'm unsure how clear sentence was, add code form's module. ensure 1 of 2 text boxes contain non-null value.

option compare database option explicit  private sub txtsearchfname_afterupdate()     if not isnull(me.txtsearchfname)         me.txtsearchphone = null     end if end sub  private sub txtsearchphone_afterupdate()     if not isnull(me.txtsearchphone)         me.txtsearchfname = null     end if end sub 

finally if works, need revise deal lname. hope part won't daunting. luck.