If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
how to change format of data already entered
I entered a list of phone numbers and changed the input mask to display the
numbers including the dash and parenthese, ie. (555) 555-5555. However, the data is stored with only the numbers. What code do I need to type into an update query so the parenthases, space and dash are stored as well? That way when I export my date it stays in the required format. |
#2
|
|||
|
|||
how to change format of data already entered
You have several options - How is the data stored? Is it in a text or number
field? Do you want to modiy the data or just change how it looks in query results, form, or report display? If it is in a number field and you want to store the dash and parenthese you will need to change the field to text as a number field will not store dash and parenthese. You would backup the database, add the new text field, and run this query -- UPDATE [YourTable] SET [YourTable].[NewPhone] = "(" & Left([YourTable].[OldPhone],3) & ") " & Mid([YourTable].[OldPhone],4,3) & "-" & Right([YourTable].[OldPhone],4); -- Build a little, test a little. "Beth A" wrote: I entered a list of phone numbers and changed the input mask to display the numbers including the dash and parenthese, ie. (555) 555-5555. However, the data is stored with only the numbers. What code do I need to type into an update query so the parenthases, space and dash are stored as well? That way when I export my date it stays in the required format. |
#3
|
|||
|
|||
how to change format of data already entered
On Thu, 13 Aug 2009 14:15:01 -0700, Beth A wrote:
I entered a list of phone numbers and changed the input mask to display the numbers including the dash and parenthese, ie. (555) 555-5555. However, the data is stored with only the numbers. What code do I need to type into an update query so the parenthases, space and dash are stored as well? That way when I export my date it stays in the required format. When using an Input Mask, the mask is NOT saved with the data unless you expressly tell it to (in the mask itself). From Access help on the Input Mask property: Section Description Second Specifies whether Microsoft Access stores the literal display characters in the table when you enter data. If you use 0 for this section, all literal display characters (for example, the parentheses in a phone number input mask) are stored with the value; if you enter 1 or leave this section blank, only characters typed into the control are stored. So !(999) 000-0000;;_ will NOT store the mask. but !(999) 000-0000;0;_ will. Changing the mask will affect storage of newly entered data. To change existing data use an Update Query: Update YourTable Set YourTable.[PhoneField] = Format([PhoneField],"(@@@) @@@-@@@@") Where [PhoneField is not null and Len([PhoneField] = 10; -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#4
|
|||
|
|||
how to change format of data already entered
|
#5
|
|||
|
|||
how to change format of data already entered
Use the format function in a calculated field.
Format(Telephone,"(@@@) @@@-@@@@") If you want to permanently fix the values UPDATE YourTable SET Telephone = Format([Telephone],"(@@@) @@@-@@@@") WHERE Telephone Like "??????????" Also if you want to store formatted data change your input mask to include the formatting characters. The second argument to the input mask should be zero to do this. The input mask should look like: !\(999") "000\-0000;0;_ '================================================= === John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County '================================================= === Beth A wrote: I entered a list of phone numbers and changed the input mask to display the numbers including the dash and parenthese, ie. (555) 555-5555. However, the data is stored with only the numbers. What code do I need to type into an update query so the parenthases, space and dash are stored as well? That way when I export my date it stays in the required format. |
#6
|
|||
|
|||
how to change format of data already entered
I am trying to do this as well.
Where do I put Format([PhoneField],"(@@@) @@@-@@@@") in the query? Or are we not talking about a query? "fredg" wrote: On Thu, 13 Aug 2009 14:15:01 -0700, Beth A wrote: I entered a list of phone numbers and changed the input mask to display the numbers including the dash and parenthese, ie. (555) 555-5555. However, the data is stored with only the numbers. What code do I need to type into an update query so the parenthases, space and dash are stored as well? That way when I export my date it stays in the required format. When using an Input Mask, the mask is NOT saved with the data unless you expressly tell it to (in the mask itself). From Access help on the Input Mask property: Section Description Second Specifies whether Microsoft Access stores the literal display characters in the table when you enter data. If you use 0 for this section, all literal display characters (for example, the parentheses in a phone number input mask) are stored with the value; if you enter 1 or leave this section blank, only characters typed into the control are stored. So !(999) 000-0000;;_ will NOT store the mask. but !(999) 000-0000;0;_ will. Changing the mask will affect storage of newly entered data. To change existing data use an Update Query: Update YourTable Set YourTable.[PhoneField] = Format([PhoneField],"(@@@) @@@-@@@@") Where [PhoneField is not null and Len([PhoneField] = 10; -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
Thread Tools | |
Display Modes | |
|
|