Richard Sutherland
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:
Is this possible?
A big thank you to all who take the time to read and reply.
Richard
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:
- 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.
- 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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello Crystal,
worked like a charm. This is awesome! You've saved me a lot of grief.
Thank you very much!
Richard
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
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:
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?