Query for a substring.... 2

[sera]

I want to be able to prompt the user for a particular substring that they wish to search on. Ideally this would consist of a form that asks for criteria and the user will be able to enter something like...
S*
this (ideally) would show all the records that start with S.
I tried creating a form and then in the query criteria adding
Like [Forms]![formname]![fieldname]
of course I added the right names for my form and for my fieldname, but this query appears to do absolutely nothing. If I add to the criteria Like "S*" then, of course, the query works. I need to make the querying process as simple as possible for my users and if I could figure this out, it would be ideal. I hope someone knows what I am talking about,
Sera

 

[JoeMiller]

I've done this very thing following the exact same steps as you and using the Like [Forms]!etc... and it works. Double-Check spelling and if nothing there post your query code for a look-see.

Joe Miller
joe.miller@flotech.net

 

[CosmoKramer]

Sera,

Try

Like [Forms]![formname]![fieldname] & '*'

in your query. This has worked for me.

 

[sera]

CosmoKramer,
Nice name! That worked well, thank you very much. Joe Miller, were you able to not use Cosmo's method and still have it work? I would like to make it just a little more flexible than just using & '*'. I want to be able to use all *S* and S? so on and so forth. What I would like to be able to do is put something like
S* in the unbound textbox and have the query pull that expression and use it. One more question for Joe(I have asked you this before)...What version of Access are you using?
Sera

 

[JoeMiller]

CosmoKramer's works as well, I've never used his method, all it does is add the * for you at the end so rather than having the user type "S*" they can just type "S". There is no functional difference between my answer and his. I use Access 97 for all my programming needs, but also have 2000 and XP when needed. As for the "*S*" you can just add another * in front like this:

Like '*' & [Forms]![formname]![fieldname] & '*'
Joe Miller
joe.miller@flotech.net

 

[CosmoKramer]

Sera, I agree with Joe. The only difference is: Do you want end users to have to enter wildcard characters? Personally, I would stay away from that, unless absolutely necessary.

 

[sera]

Do your users have to include the quotation marks when they enter the criteria into the textbox? I have tried it both ways and the only way I can get it to work is by using Cosmo's method. I would like to use your method, it is more general.
Sera

 

[JoeMiller]

No quotation marks, just enter S* with my method and you'll get what you're looking for.

HTH Joe Miller
joe.miller@flotech.net

 

[sera]

Okay, I am so trying and getting soooo frustrated, I am going to post the SQL statement and maybe you can tell me how it differs from yours. Maybe my computer is just possesed....
SELECT [Part A-Administrative Data].project_number, [Part A-Administrative Data].project_name
FROM [Part A-Administrative Data]
WHERE ((([Part A-Administrative Data].project_number) Like [Forms]![TEST_FORM]![criteria]));
Thanks,
Sera

 

[sera]

You know what, it is working now. My computer is possesed... I really think I wasn't updating the changes in the form. Thank you for all of your help.
Sera

 

[JoeMiller]

Hehe . . the evil MS bug strikes again!

Joe Miller
joe.miller@flotech.net

 

[CosmoKramer]

Don't let it get to you....

After all, we are all the masters of our own domains!!!