short-cut to changing zip/post codes

[Guest]

I have imported 7500 records from an Excel file into Access. However, each
Zip/Post code begins with a 2 letter country code, then a space. Is there an
easy way to get rid of the first two letters and the space from each record?

 

[Van T. Dinh]

--
HTH
Van T. Dinh
MVP (Access)

I have imported 7500 records from an Excel file into Access. However, each
Zip/Post code begins with a 2 letter country code, then a space. Is there an
easy way to get rid of the first two letters and the space from each

record?

 

[Van T. Dinh]

You can use an Update Query to remove the first 3 characters with SQL like:

UPDATE [YourTable]
SET [PostCode] = Mid([PostCode], 4)

--
HTH
Van T. Dinh
MVP (Access)

I have imported 7500 records from an Excel file into Access. However, each
Zip/Post code begins with a 2 letter country code, then a space. Is there an
easy way to get rid of the first two letters and the space from each

record?

 

[Guest]

So how does this fit into the Query:
SELECT [kompass exporters].co, [kompass exporters].add1, [kompass
exporters].add2, [kompass exporters].[post code]
FROM [kompass exporters];

You can use an Update Query to remove the first 3 characters with SQL like:

UPDATE [YourTable]
SET [PostCode] = Mid([PostCode], 4)

--
HTH
Van T. Dinh
MVP (Access)

I have imported 7500 records from an Excel file into Access. However, each
Zip/Post code begins with a 2 letter country code, then a space. Is there an
easy way to get rid of the first two letters and the space from each

record?

 

[Rick Brandt]

So how does this fit into the Query:
SELECT [kompass exporters].co, [kompass exporters].add1, [kompass
exporters].add2, [kompass exporters].[post code]
FROM [kompass exporters];

It doesn't. It's a completely separate query that you run exactly one time
after your import to eliminate the undesired characters.

 

[Guest]

Sorry - (never used SQL before) so I should start a new query and under SQL
view and type:
SELECT
UPDATE [kompass exporters]
SET [PostCode] = Mid([PostCode], 4)
But this says Syntax error.

So how does this fit into the Query:
SELECT [kompass exporters].co, [kompass exporters].add1, [kompass
exporters].add2, [kompass exporters].[post code]
FROM [kompass exporters];

It doesn't. It's a completely separate query that you run exactly one time
after your import to eliminate the undesired characters.

 

[Mark]

Don't type SELECT, start with UPDATE

Sorry - (never used SQL before) so I should start a new query and under
SQL
view and type:
SELECT
UPDATE [kompass exporters]
SET [PostCode] = Mid([PostCode], 4)
But this says Syntax error.

So how does this fit into the Query:
SELECT [kompass exporters].co, [kompass exporters].add1, [kompass
exporters].add2, [kompass exporters].[post code]
FROM [kompass exporters];

It doesn't. It's a completely separate query that you run exactly one
time
after your import to eliminate the undesired characters.

 

[fredg]

So how does this fit into the Query:
SELECT [kompass exporters].co, [kompass exporters].add1, [kompass
exporters].add2, [kompass exporters].[post code]
FROM [kompass exporters];

*** snipped ***

It might be a good idea for you to read Access help regarding Update
queries:
Access Help + Contents tab + Microsoft Jet SQL Reference + Data
Manipulation Language + Update statement