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.
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 : - UPDATE [YourTable]
-
SET [PostCode]=Replace([PostCode],' ',''),
-
[Phone]=Replace([Phone],' ','')
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 - UPDATE tblCopyTo
-
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
will produce Phone Numbers in the Format (000) 000-0000.
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 : - UPDATE [YourTable]
-
SET [PostCode]=Replace([PostCode],' ',''),
-
[Phone]=Replace([Phone],' ','')
NeoPa:
Not stepping on your toes, just trying to provide an alternative, not better, solution.
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.
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.
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
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.
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). - UPDATE [New]
-
SET [ZipCode]=Replace([ZipCode],' ',''),
-
[Phone]=Replace([Phone],' ','')
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.
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
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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.
|
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...
|
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
|
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...
|
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...
|
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:...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |