Update Expression

[Guest]

I am importing data into a Access. One of the fields in the data contains a
six digit numbers seperated by "-". between the 2nd and 4th digits. Is there
an expression I can use in an update query that would remove the dashes from
the number?
Example:
Imported Number: 99-99-99
Desired Format: 999999

Thanks in advance

 

[Graham R Seach]

Antonio,

This might be a bit of overkill, however, put the code on the following page
into a standard module:

http://www.pacificdb.com.au/MVP/Code/StripChars.htm

Then you can call it from your query, like so:
SELECT StripEx([myField], 32) As MyNum FROM tblMyTable

NOTE: You need to add a reference to Microsoft VBScript Regular Expressions:
1. Open any code module
2. Select References from the Tools menu
3. Select and tick "Microsoft VBScript Regular Expressions"
4. Click OK

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[Guest]

Graham,
Thank you so very much. Your solution worked like a charm and truly helped
tremendously.

Antonio,

This might be a bit of overkill, however, put the code on the following page
into a standard module:

http://www.pacificdb.com.au/MVP/Code/StripChars.htm

Then you can call it from your query, like so:
SELECT StripEx([myField], 32) As MyNum FROM tblMyTable

NOTE: You need to add a reference to Microsoft VBScript Regular Expressions:
1. Open any code module
2. Select References from the Tools menu
3. Select and tick "Microsoft VBScript Regular Expressions"
4. Click OK

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

I am importing data into a Access. One of the fields in the data contains a
six digit numbers seperated by "-". between the 2nd and 4th digits. Is
there
an expression I can use in an update query that would remove the dashes
from
the number?
Example:
Imported Number: 99-99-99
Desired Format: 999999

Thanks in advance