Link to home
Start Free TrialLog in
Avatar of Richard Sutherland
Richard SutherlandFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Access2016 ignore hyphen in data entry or searches

Hello all,

I am working on an Access 2016 database which has a plain text field which contains a product code consisting of a combination of letters and numbers. These are stored only as letters and numbers while public sources may list them with a single hyphen separating characters.

Expample: D2-AH2U  would be stored in the database as D2AH2U

What I would like to do in a form is twofold:

  1. Be able to create a new record, enter the code with a hyphen and have Access ignore the hyphen and store the value without it. In my example: I enter D2-AH2U  but only D2AH2U is recorded. This is relevant as I work a lot with copy and paste and having to go back into the value, position cursor and delete the - is time consuming.
  2. I would like to do a search (Find: look in current field, match whole field) but again, as I am using copy and paste function the hyphen would be in the search parameters. My aim is to have access ignore it.

Is this possible?

A big thank you to all who take the time to read and reply.

Richard
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

Hi Richard,

you can use an InputMask that supplies the dash for display but doesn't store it. Go to the Design View of the table. Select the field. In the lower pane, click in the InputMask, and then on the Builder ... button to the right. A wizard will come up that will let you create and test an InputMask. After it is changed, you can use an Update Query to remove the dash from the data:
Field  --> MyFieldname
Replace with  --> Replace([MyFieldname],"-","")
criteria --> [MyFieldname] is not null

Open in new window

Alternately, if you are doing this because you want an easier time to copy without the dash, what about a little button to copy what is there, remove the dash, and put it on the Windows clipboard for you?
Avatar of Richard Sutherland

ASKER

Hello Crystal,

many thanks for taking the time to share your thoughts on a solution, I do appreciate it.  

Let's put the input mask idea on ice for now as I *don't* want to see the hyphen at all. To complicate matters It can be located either after first, second or fifth character, so difficult to standardize.  
Your solution to update the data via query is great. Not sure if it will work if the data rules allow for alphanumeric characters only.

I think the most pressing matter is the search. In very simple terms I want to search for "D2-AH2U" and have the system find "D2AH2U" which is how the information is stored in the field.
ASKER CERTIFIED SOLUTION
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello Crystal,
worked like a charm. This is awesome! You've saved me a lot of grief.
Thank you very much!
Richard
you're welcome, Richard ~ happy to help