SQL Wildcards and Access databases

[JLSigman]

I'm working on a personnel directory for my office, and I can make the search page work if the input is a complete first name, last name, or user id, but if I just put a "b" in the last name field (for example), I get an error. I'm not sure how to manipulate the SQL code in the Advanced Recordset settings to make it give me those back, and the SQL book I've got isn't helping. ;-) Any advice would be greatly appreciated. Thanks,

Jennifer L. Sigman
Information Resource Consultant I
SC DHEC - Bureau of Air Quality

 

[Guest_imported]

If you go to the Recordset on your results page, then click the advanced option. In the SQL box you need to insert/amend the where clause. If it states fieldname = 'MMColParam' change this to fieldname LIKE '%MMColParam%'. You may also need to change the variable's default attribute to % rather than 1.

Hope this helps.

Jennie Sanders
Cyber Marketing Administrator

 

[JLSigman]

OK... when I changed the code to this: WHERE LAST_NAME LIKE 'VARLAST%' OR FIRST_NAME LIKE 'VARFIRST%' OR USERID LIKE 'VARUSER%', it gave me back the entire database. My variables are set to % as the default.

Any other suggestions? It's not a critical function, but something that would be nice and would get me brownie points from the boss. ;-)

-jls

 

[Jayuk]

How about trying a % before var etc as well as after.

Probably stating the obvious, but what search criteria did you enter when testing, how specific were you?

 

[Jayuk]

Sorry just another thought, about not using SELECT * FROM Database, it might work better if you use SELECT LAST_NAME, FIRST_NAME, USER_ID.

 

[JLSigman]

When searching, I just put in "b" in the last name. I'll try narrowing the Select down, thanks.

 

[Cheech]

if your defaults are % then when saying x like y if default y is % then all will be returned if no value is entered.

Like this

WHERE LAST_NAME LIKE 'VARLAST%' OR FIRST_NAME LIKE 'VARFIRST%'

If there is no value entered in VARFIRST then its value in the SQL will be %% therefore returning all records Live long and make your kids suffer..

 

[JLSigman]

Cheech,

Thanks for the SQL lesson.

But now I need to know if there is a way to do it? Is there a way to do a Wildcard search? Thanks,

-jls

 

[mlawson]

I have an alphabetic search facility that searches through a 200 member database and reports back each of the surnames that begins with the letter clicked, this coupled with a surname based search using the '%surname%' variable worked for me. Got me the necessary B points too!! LEt me know if you still want it.

 

[JLSigman]

mlawson,

YES! I definitely still need it. This project is due Friday. Thanks a ton,

-jls

 

[CTekMedia]

mlawson

Any chance I could get a copy of that also? I am about to embark on a similar project.

Thanks much,

shaneh@precorp.org

BT

 

[mlawson]

Sorry for not getting it out sooner guys! Anybody else need it please contact me at design@1stclass.uk.com

Have Fun!

M