Microsoft Access Office VB VBA Help and Examples

Letting users have maths symbols in criteria for queries

You have a query in which you want to prompt users for a criteria, but you would like the users to be able to type >Deidre  , <Deidre , =Deidre.  The sql below shows how to do this. Create a table called tbl_events and then create one text field called Event , and put some names in it. Paste the SQL below into a new query then have a look in design grid and you will see how it's done.

SELECT tbl_Events.Event
FROM tbl_Events
WHERE (((Left([Prompt],1))=">") AND
((tbl_Events.Event)>Right([Prompt],Len([prompt])-1))) OR
(((Left([Prompt],1))="<") AND
((tbl_Events.Event)<Right([Prompt],Len([prompt])-1))) OR
(((Left([Prompt],1))="=") AND ((tbl_Events.Event) Like
Right([Prompt],Len([prompt])-1))) OR (((Right([Prompt],1)) Like "*") AND
((tbl_Events.Event) Like Left([Prompt],Len([prompt])-1) & "*"));
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.