updating existing records to match input mask format

S

Susan

While the input mask for a text based fax number database field works for new
fax number entries, I need to update the EXISTING RECORDS to match this
format. For example, existing fax numbers are formatted as 123-456-789 and
needs to change to (123) 456-789.
 
S

Susan

I realized that there were extra hyphens in the fax number records and they
were preventing the effect of the input mask.
 
J

Jeanette Cunningham

Hi Susan,
create a new query.
--use the table that has the Fax number in it
--drag the Fax number field onto the query grid
--select Query, Update query from the menu/toolbar
--in the Update To row type
"(" & Left$([tblAddress].[FaxNo],3) & ") " &
Mid$([tblAddress].[FaxNo],5)

--in the Criteria row type
Mid([tblAddress].[FaxNo],4,1)= "-"

Note: tblAddress is the name of the table with the fax no field, change it
to your table name
FaxNo is the name of the field with the fax no, change it to your field name

Run the update query
I created the above table and field and tested the update query.
Note: it is usually easier for maintenance if you store your fax numbers and
phone numbers in the table without any formatting or input masks and just
format the number how you want it to look when you are displaying it in the
form or exporting the query.

Jeanette Cunningham
 
A

Arvin Meyer [MVP]

I'd use an update query. First, make sure that the existing records are
formatted 123-456-789, not stored that way. If they're not the following
code will need to be slightly different If you remove the formatting in the
table, the existing records should look like 123456789.

Now separate out the data you want by adding the phone number and setting
the criteria condition to:

Left([Phone Number Field], 1) <> "("

which will exclude all the correctly input phone numbers. Now in your Update
query's Update To box, use the expression:

"(" & Left([Phone Number Field],3) & ") " & Mid([Phone Number Field],4,3) &
" - " & Right([Phone Number Field],4)

all on one line. Make sure of the spaces, just copy the code above into
notepad, put it all on 1 line then copy again and paste into the Update To
box.
 
S

Susan

Thanks so much for your detailed response.

How would I create the formated settings for exporting purposes?

Jeanette Cunningham said:
Hi Susan,
create a new query.
--use the table that has the Fax number in it
--drag the Fax number field onto the query grid
--select Query, Update query from the menu/toolbar
--in the Update To row type
"(" & Left$([tblAddress].[FaxNo],3) & ") " &
Mid$([tblAddress].[FaxNo],5)

--in the Criteria row type
Mid([tblAddress].[FaxNo],4,1)= "-"

Note: tblAddress is the name of the table with the fax no field, change it
to your table name
FaxNo is the name of the field with the fax no, change it to your field name

Run the update query
I created the above table and field and tested the update query.
Note: it is usually easier for maintenance if you store your fax numbers and
phone numbers in the table without any formatting or input masks and just
format the number how you want it to look when you are displaying it in the
form or exporting the query.

Jeanette Cunningham


Susan said:
While the input mask for a text based fax number database field works for
new
fax number entries, I need to update the EXISTING RECORDS to match this
format. For example, existing fax numbers are formatted as 123-456-789 and
needs to change to (123) 456-789.
 
S

Susan

How do I know how the data is stored, when different than how it appears on
the screen?

Arvin Meyer said:
I'd use an update query. First, make sure that the existing records are
formatted 123-456-789, not stored that way. If they're not the following
code will need to be slightly different If you remove the formatting in the
table, the existing records should look like 123456789.

Now separate out the data you want by adding the phone number and setting
the criteria condition to:

Left([Phone Number Field], 1) <> "("

which will exclude all the correctly input phone numbers. Now in your Update
query's Update To box, use the expression:

"(" & Left([Phone Number Field],3) & ") " & Mid([Phone Number Field],4,3) &
" - " & Right([Phone Number Field],4)

all on one line. Make sure of the spaces, just copy the code above into
notepad, put it all on 1 line then copy again and paste into the Update To
box.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Susan said:
While the input mask for a text based fax number database field works for
new
fax number entries, I need to update the EXISTING RECORDS to match this
format. For example, existing fax numbers are formatted as 123-456-789 and
needs to change to (123) 456-789.
 
J

Jeanette Cunningham

Susan,
not sure I understand what you want to export? some more info would help.
I thought we were setting the input mask for a field in a table?
Are you importing these fax numbers from somewhere, using them and then
exporting them in to another program?
This whole process could be a lot easier if the data is stored without any
special formats.
It is always easier in Access to store data in tables without any special
formats for phone and fax numbers.
Just use a calculated field in a query to format the fax numbers the way you
want to show the numbers.

Jeanette Cunningham



Susan said:
Thanks so much for your detailed response.

How would I create the formated settings for exporting purposes?

Jeanette Cunningham said:
Hi Susan,
create a new query.
--use the table that has the Fax number in it
--drag the Fax number field onto the query grid
--select Query, Update query from the menu/toolbar
--in the Update To row type
"(" & Left$([tblAddress].[FaxNo],3) & ") " &
Mid$([tblAddress].[FaxNo],5)

--in the Criteria row type
Mid([tblAddress].[FaxNo],4,1)= "-"

Note: tblAddress is the name of the table with the fax no field, change
it
to your table name
FaxNo is the name of the field with the fax no, change it to your field
name

Run the update query
I created the above table and field and tested the update query.
Note: it is usually easier for maintenance if you store your fax numbers
and
phone numbers in the table without any formatting or input masks and just
format the number how you want it to look when you are displaying it in
the
form or exporting the query.

Jeanette Cunningham


Susan said:
While the input mask for a text based fax number database field works
for
new
fax number entries, I need to update the EXISTING RECORDS to match this
format. For example, existing fax numbers are formatted as 123-456-789
and
needs to change to (123) 456-789.
 
S

Susan

When I removed the input mask, I noticed that there was some existing
formatting which includes an extra space in the middle of the fax numbers.
How do I remove this space?

Secondly,

Arvin Meyer said:
I'd use an update query. First, make sure that the existing records are
formatted 123-456-789, not stored that way. If they're not the following
code will need to be slightly different If you remove the formatting in the
table, the existing records should look like 123456789.

Now separate out the data you want by adding the phone number and setting
the criteria condition to:

Left([Phone Number Field], 1) <> "("

which will exclude all the correctly input phone numbers. Now in your Update
query's Update To box, use the expression:

"(" & Left([Phone Number Field],3) & ") " & Mid([Phone Number Field],4,3) &
" - " & Right([Phone Number Field],4)

all on one line. Make sure of the spaces, just copy the code above into
notepad, put it all on 1 line then copy again and paste into the Update To
box.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Susan said:
While the input mask for a text based fax number database field works for
new
fax number entries, I need to update the EXISTING RECORDS to match this
format. For example, existing fax numbers are formatted as 123-456-789 and
needs to change to (123) 456-789.
 
J

Jeanette Cunningham

Susan,
--create a new query to isolate the fax nos
--use the table that has the Fax number in it
--drag the Fax number field onto the query grid
--select Query, Update query from the menu/toolbar
--in the Update To row type
Replace([FaxNo],Chr(32),"")

Note: tblAddress is the name of the table with the fax no field, change it
to your table name, FaxNo is the name of the field with the fax no, change
it to your field name

--Run the update query
--I am assuming that the spaces in your fax numbers were made by pressing
space bar
--save the query in case you need it again in the future and close it
--create a new query with just the field FaxNo it it
--switch to datasheet view and check all the fax numbers to see if you have
what you need format-wise

Jeanette Cunningham
 
S

Susan

This query worked like a dream on one of my tables, but when I applied this
query to a different table, I received the error message: "Undefined function
'Chr' in expression."
 
A

Arvin Meyer [MVP]

Is the second table in the same database? Usually and undefined function
means that:

1. It's missing (i.e. you didn't import it). That's not the case here
because it's a built-in function.
2. You spelled something wrong in the function name.
3. You have a missing reference.

To check for the missing reference, hit Ctrl+G and the in the menu use Tools--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Susan said:
This query worked like a dream on one of my tables, but when I applied
this
query to a different table, I received the error message: "Undefined
function
'Chr' in expression."

Jeanette Cunningham said:
Susan,
--create a new query to isolate the fax nos
--use the table that has the Fax number in it
--drag the Fax number field onto the query grid
--select Query, Update query from the menu/toolbar
--in the Update To row type
Replace([FaxNo],Chr(32),"")

Note: tblAddress is the name of the table with the fax no field, change
it
to your table name, FaxNo is the name of the field with the fax no,
change
it to your field name

--Run the update query
--I am assuming that the spaces in your fax numbers were made by
pressing
space bar
--save the query in case you need it again in the future and close it
--create a new query with just the field FaxNo it it
--switch to datasheet view and check all the fax numbers to see if you
have
what you need format-wise

Jeanette Cunningham
 
S

Susan

Missing reference was the problem. Thanks for your detailed instructions
which has solved the issue.

Secondly, this formatting code that you have provided works great: "(" &
Left([Phone Number Field],3) & ") " & Mid([Phone Number Field],4,3) &
" - " & Right([Phone Number Field],4)

However, I would like to avoid the space created before and after the hyphen
and I can't figure out which part of the code alludes to spacing.

Desired format: (123) 456-7890




Arvin Meyer said:
Is the second table in the same database? Usually and undefined function
means that:

1. It's missing (i.e. you didn't import it). That's not the case here
because it's a built-in function.
2. You spelled something wrong in the function name.
3. You have a missing reference.

To check for the missing reference, hit Ctrl+G and the in the menu use Tools--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Susan said:
This query worked like a dream on one of my tables, but when I applied
this
query to a different table, I received the error message: "Undefined
function
'Chr' in expression."

Jeanette Cunningham said:
Susan,
--create a new query to isolate the fax nos
--use the table that has the Fax number in it
--drag the Fax number field onto the query grid
--select Query, Update query from the menu/toolbar
--in the Update To row type
Replace([FaxNo],Chr(32),"")

Note: tblAddress is the name of the table with the fax no field, change
it
to your table name, FaxNo is the name of the field with the fax no,
change
it to your field name

--Run the update query
--I am assuming that the spaces in your fax numbers were made by
pressing
space bar
--save the query in case you need it again in the future and close it
--create a new query with just the field FaxNo it it
--switch to datasheet view and check all the fax numbers to see if you
have
what you need format-wise

Jeanette Cunningham
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top