User defined 'not' wildcard?

[BTilson]

I have a form where the end user can specify certain criteria. This critera is referenced in a query like this:

Like [Forms]![TestForm]![Text1].value

This approach has worked fine so far, the user can type in what they want to search for, wildcards included, and press the button that executes the query and get their report.

However, it has been requested of me now to add in the ability to allow the user to be able to specify a "not" value. For instance, show me all records that do NOT contain the string "CSM" in them.

I looked in the help file and the closest thing I found was something like [!CSM] but that doesn't seem to be working. I can code a workaround if necessary, and have it run a seperate query with a "not like" instead of a "like" statement, but I would really rather keep it clean with just one background query.

Does anyone have any input or know if this is even possible?

Thanks in advance!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[Golom]

The NOT (!) operator works for ranges like [!0-9] but not for strings.

There is no way to do this with pure SQL but you might try something like

Select *
From SomeTable
Where  IIF(Left$([Parameter],1)='!',  

 myfield NOT LIKE  Mid([Parameter],2) & '*'  ,   

 myfield LIKE [Parameter]  & '*')

 

[AlexCuse]

or you could add a second parameter if you don't trust 'em with the !.

Something like

select * from mytable
where (myfield like [Parameter] & '*' AND [ExclusionParameter] = 'N')
or (myfield not like [Parameter] & '*' AND [ExclusionParameter] = 'Y')

Where ExclusionParameter is just a Y/N answer to "would you like to exclude matches?"

Hope this helps,

Alex

[small]----signature below----[/small]
Majority rule don't work in mental institutions

My Crummy Web Page