473,322 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

How can I modify the format of all records in a field?

I have two tables that both have a postal code field but the records are entered in two different formats for Canadian Postal code, A0A0A0 and A0A 0A0. Similarly, phone numbers are entered (000)000-0000 and (000) 000-0000
How can I update these two fields in one of the tables so that they both are in the same format?

I am using access 97 on Windows XP Pro

Thank you in advance.
Feb 15 '07 #1
11 2120
NeoPa
32,556 Expert Mod 16PB
Unfortunately, you don't give any field or table names so I'll have to invent some for you.
This SQL code will update the table with the extra spaces in to strip them out :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [YourTable]
  2. SET [PostCode]=Replace([PostCode],' ',''),
  3.     [Phone]=Replace([Phone],' ','')
Feb 15 '07 #2
ADezii
8,834 Expert 8TB
I have two tables that both have a postal code field but the records are entered in two different formats for Canadian Postal code, A0A0A0 and A0A 0A0. Similarly, phone numbers are entered (000)000-0000 and (000) 000-0000
How can I update these two fields in one of the tables so that they both are in the same format?

I am using access 97 on Windows XP Pro

Thank you in advance.
In reverse order, assuming that the Postal Code is contained in a Field named [Postal], and that Field is restricted to exactly 6 alphanumeric characters, then
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblCopyTo
  2. SET tblCopyTo.Postal = Left([Postal],3) & " " & Right([Postal],3);
will update Postal Codes in the 2nd Table to A0A 0A0 Format. Changing the Input Mask in the 2nd Table's Phone Number Field to
Expand|Select|Wrap|Line Numbers
  1. \(000") "000\-0000
will produce Phone Numbers in the Format (000) 000-0000.
Feb 15 '07 #3
ADezii
8,834 Expert 8TB
Unfortunately, you don't give any field or table names so I'll have to invent some for you.
This SQL code will update the table with the extra spaces in to strip them out :
Expand|Select|Wrap|Line Numbers
  1. UPDATE [YourTable]
  2. SET [PostCode]=Replace([PostCode],' ',''),
  3.     [Phone]=Replace([Phone],' ','')
NeoPa:
Not stepping on your toes, just trying to provide an alternative, not better, solution.
Feb 15 '07 #4
NeoPa
32,556 Expert Mod 16PB
You go ahead ADezii (It's enough that I know my answers are the best ;)).
I'm just going to edit your SQL (post #3) for visibility though. It's best to fit it within the viewing window where possible.
Feb 15 '07 #5
ADezii
8,834 Expert 8TB
You go ahead ADezii (It's enough that I know my answers are the best ;)).
I'm just going to edit your SQL (post #3) for visibility though. It's best to fit it within the viewing window where possible.
Edit till you heart is content.
Feb 16 '07 #6
Thanks for the replies.
I just copied the original table and named it "new" the fields are "zipcode" and "phone"
I tried pasting the codes above into the SQL view of an update query but get this: undefined function 'replace' in expression.
I'm a beginner at this stuff and I usually only use the design view for queries.
Appreciate the help.

Thanks
Feb 16 '07 #7
NeoPa
32,556 Expert Mod 16PB
I was hoping to get to this today but at 04:00 it's running a bit late.
I just thought I'd post to say it's not been forgotten.
Feb 17 '07 #8
NeoPa
32,556 Expert Mod 16PB
Posting it into the SQL view of a query is exactly what you needed to do :)
I've changed the SQL to match your database exactly (assuming the data in the table [New] is what you need to be changed).
I checked the Replace() function and I can't understand your problem. It works fine in SQL and the Reference it needs is the main VBA library (which should always be available).
Expand|Select|Wrap|Line Numbers
  1. UPDATE [New]
  2. SET [ZipCode]=Replace([ZipCode],' ',''),
  3.     [Phone]=Replace([Phone],' ','')
Feb 17 '07 #9
I tried that, but same error. It may be that there is a missing VBA link.
Anyways, a colleague showed me another way around it.

Created 3 new columns, updated first 3 of zip to one column, then last 3 of zip to second column and then in the third column, just combined the other two columns.

Thanks again for the help.
Feb 19 '07 #10
MMcCarthy
14,534 Expert Mod 8TB
I tried that, but same error. It may be that there is a missing VBA link.
Anyways, a colleague showed me another way around it.

Created 3 new columns, updated first 3 of zip to one column, then last 3 of zip to second column and then in the third column, just combined the other two columns.

Thanks again for the help.
The replace function may not have been available in the standard library in Access '97.

Mary
Feb 20 '07 #11
NeoPa
32,556 Expert Mod 16PB
That's the answer Mary :)
I have some old A97 DBs at work and they certainly don't have the Replace() function available.
Feb 20 '07 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Danny | last post by:
How to allow users to select a set of records and then let them change a field for all these records at once? I would like to do this in code on a form. I will have a form with tabular view of...
4
by: MikeY | last post by:
Hi everyone, I have posted earlier this week, but I'm still scratching my head trying to figure out how to change/modify my data back to my db. Using C# Windows forms. I am trying to learn how...
0
by: richardkreidl | last post by:
I have the following hash script that I use to compare two text files. 'Class Public Class FileComparison Public Class FileComparisonException Public Enum ExceptionType U 'Unknown A 'Add...
6
by: coriolis_wong | last post by:
Hi, I need to transfer csv format file to DBase III format file. How do i do it in Python language? Any help is appreciated. Thanks.
2
by: junkaccount | last post by:
Hello, I currently have a field named QuoteNumber in a table named Quotes. The field is set as autonumber and is used to assign sequential numbers as users enter information in the table through...
6
by: Brian Campbell | last post by:
HELP! SOS! I have a field that I'm trying to format within a query. The results from my field "Appointment.EMP_FILE_NB" give various numbers: (e.g.: 213 105523 125400 1245 834
4
by: Bob | last post by:
Hi all, I'm trying to import data, modify the data then insert it into a new table. The code below works fine for it but it takes a really long time for 15,000 odd records. Is there a way I...
23
by: no1zson | last post by:
I have been adding buttons to my GUI to manipulate list data. I added a Delete button this morning in case I decide I no longer needed a particular element. I am now working on a modify button, in...
8
by: joemacbusiness | last post by:
Hi All, How do I format printed data in python? I could not find this in the Python Reference Manual: http://docs.python.org/ref/print.html Nor could I find it in Matloff's great tutorial:...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.