Using "-" In Text Boxes for Queries

[ASherbuck]

Hello,
I've scoured the forum and think I've found a way to do this but I figured I'd post first to make sure.

What I've got a short form that basically provides the criteria for a query behind a report. The form allows a user to select sales records between dates from a single Vendor, department and category and a bit of text for the item short name.

My query was built in Access and my question involves the usage of the "-" key in a search field. Everyone who has used a modern search knows they can use the minus key to exclude works from the search query.

Is there a simple way to do this?

The solution I've found thus far involves writing a large amount of VB that would basically build a sql string based on conditions set forth in the form, then gets into using recordsources. Is this the way to go or does Access have some nifty built in function I'm overlooking ?

 

[wazz]

you can use

Not Like "text"

check 'like' in vba help for a lot more options including wildcards.
hyphen is for a range of things, like A-Z.
* is a wildcard
list: [charlist]
not in list: [!charlist]
...

 

[ASherbuck]

So when the user types in "Batman -Robin" in the text box how is that going to work?

 

[wazz]

i see. i don't know of a simple way. perhaps two textboxes, one with stuff to include and one with stuff to exclude?

 

[dkinley]

I agree that Waz's would be simplest IMO.

If you wanted to "writing a large amount of VB " probably have to start out with something like (not testing to see if it works btw - just throwing something on the table) ...

Like Left(Me!SearchBox, Len(Inst(Me!SearchBox), "-")) And _ 
Not Like Right(Me!SearchBox, Len(Me!SearchBox) - Len(Inst(Me!SearchBox), "-"))

Which doesn't include all of the L/RTrims not to mention seperating multiple word searches.

-dK

 

[ASherbuck]

Thx dk, yeah I figured it would take a lot of parsing and involve vb.