Postal Code

[tim]

I am importing a text file into a Access table and I have a postal code
field that contains Canadian and US postal codes. The field is 7 characters
long and I am importing it as a text field. The Canadian date looks like
this: A0B 2J0 and the US looks like this: 834020000 I need to select the
first 5 characters of the US postal code and the first 7 of the Canadian
postal code. Is this possible?


Tim
Access 2000

 

[Lost]

Yes,

iif([Country] = 'Canada', LTrim([PostalCode], 7),
Ltrim([PostalCode],5))

See if this works.

 

[Brendan Reynolds]

Unless you're already using custom code to import the data, the simplest
solution is probably to import the data 'as is' and then run an update query
to tidy it up. Presumably there is another field in the table that
identifies the country? If so, the update query would look something like
....

UPDATE tblWhatever SET PostalCode = IIf([Country] = 'USA',
Left$([PostalCode], 5), Left$([PostalCode], 7))

The above assumes that Country is always either 'USA' or 'Canada', and that
neither PostalCode nor Country can be Null. The expression may need
modification if either of those assumptions is not true.

 

[tim]

I don't have a field that distinguishes the country of origin. I only have a
state field that lists the abbreviation for the state or province.

Thanks,
Tim

Unless you're already using custom code to import the data, the simplest
solution is probably to import the data 'as is' and then run an update query
to tidy it up. Presumably there is another field in the table that
identifies the country? If so, the update query would look something like
...

UPDATE tblWhatever SET PostalCode = IIf([Country] = 'USA',
Left$([PostalCode], 5), Left$([PostalCode], 7))

The above assumes that Country is always either 'USA' or 'Canada', and that
neither PostalCode nor Country can be Null. The expression may need
modification if either of those assumptions is not true.

--
Brendan Reynolds

I am importing a text file into a Access table and I have a postal code
field that contains Canadian and US postal codes. The field is 7
characters
long and I am importing it as a text field. The Canadian date looks like
this: A0B 2J0 and the US looks like this: 834020000 I need to select the
first 5 characters of the US postal code and the first 7 of the Canadian
postal code. Is this possible?


Tim
Access 2000

 

[Lost]

I dont know an easier way to do this.. but maybe this will work

iif([StateCode] In("PA", "AR", "NY"......(list all US state
Abbreviations)), LTrim([Zipcode], 5), LTrim([Zipcode], 7))

do you only have US and Canada data?

 

[Lost]

Sorry made a mistake - below is the correct format

iif([StateCode] In("PA", "AR", "NY"......(list all US state
Abbreviations)), Left([Zipcode], 5), Left([Zipcode], 7))

 

[Vincent Johns]

Since U.S. codes begin with a digit and Canadian codes begin with a
letter, you could use an expression something like this to determine the
length of your string:

IIf(InStr("0123456789",Left$([PostalCodes]![Codes],1))>0, 5, 7)

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Unless you're already using custom code to import the data, the simplest
solution is probably to import the data 'as is' and then run an update query
to tidy it up. Presumably there is another field in the table that
identifies the country? If so, the update query would look something like
...

UPDATE tblWhatever SET PostalCode = IIf([Country] = 'USA',
Left$([PostalCode], 5), Left$([PostalCode], 7))

The above assumes that Country is always either 'USA' or 'Canada', and that
neither PostalCode nor Country can be Null. The expression may need
modification if either of those assumptions is not true.

-- Brendan Reynolds

 

[John Spencer]

UPDATE YourTable
Set PostalCode = Left(PostalCode,5)
WHERE PostalCode Like "#####*"

This says to change the postal code the first five characters where the
postal code starts with five numbers. So the presence of a letter or a
space in the first five characters would cause that record to be skipped.

I don't have a field that distinguishes the country of origin. I only have
a
state field that lists the abbreviation for the state or province.

Thanks,
Tim

Unless you're already using custom code to import the data, the simplest
solution is probably to import the data 'as is' and then run an update query
to tidy it up. Presumably there is another field in the table that
identifies the country? If so, the update query would look something like
...

UPDATE tblWhatever SET PostalCode = IIf([Country] = 'USA',
Left$([PostalCode], 5), Left$([PostalCode], 7))

The above assumes that Country is always either 'USA' or 'Canada', and that
neither PostalCode nor Country can be Null. The expression may need
modification if either of those assumptions is not true.

--
Brendan Reynolds

I am importing a text file into a Access table and I have a postal code
field that contains Canadian and US postal codes. The field is 7
characters
long and I am importing it as a text field. The Canadian date looks
like
this: A0B 2J0 and the US looks like this: 834020000 I need to select
the
first 5 characters of the US postal code and the first 7 of the
Canadian
postal code. Is this possible?


Tim
Access 2000

 

[Brendan Reynolds]

Any of the techniques that others have suggested should work. An alternative
approach would be to create a table with two fields, Country and PostalCode,
and join it to your existing table on PostalCode. I don't know whether this
is a one-off task, or an on-going one. If it is a one-off, it probably
wouldn't be worth the extra work to create and fill the table. If it is an
on-going one, it might be.

--
Brendan Reynolds

I don't have a field that distinguishes the country of origin. I only have
a
state field that lists the abbreviation for the state or province.

Thanks,
Tim

Unless you're already using custom code to import the data, the simplest
solution is probably to import the data 'as is' and then run an update query
to tidy it up. Presumably there is another field in the table that
identifies the country? If so, the update query would look something like
...

UPDATE tblWhatever SET PostalCode = IIf([Country] = 'USA',
Left$([PostalCode], 5), Left$([PostalCode], 7))

The above assumes that Country is always either 'USA' or 'Canada', and that
neither PostalCode nor Country can be Null. The expression may need
modification if either of those assumptions is not true.

--
Brendan Reynolds

I am importing a text file into a Access table and I have a postal code
field that contains Canadian and US postal codes. The field is 7
characters
long and I am importing it as a text field. The Canadian date looks
like
this: A0B 2J0 and the US looks like this: 834020000 I need to select
the
first 5 characters of the US postal code and the first 7 of the
Canadian
postal code. Is this possible?


Tim
Access 2000

 

[tim]

That worked with one minor problem. When I ran the statement; Like "#####*",
it cut off the Canadian postal entries that ended with a number. All I did
is swap the wild card in front of the number placeholders, Like "*#####",
and it worked.

Thank you for the help.
Tim

UPDATE YourTable
Set PostalCode = Left(PostalCode,5)
WHERE PostalCode Like "#####*"

This says to change the postal code the first five characters where the
postal code starts with five numbers. So the presence of a letter or a
space in the first five characters would cause that record to be skipped.

I don't have a field that distinguishes the country of origin. I only have
a
state field that lists the abbreviation for the state or province.

Thanks,
Tim

Unless you're already using custom code to import the data, the simplest
solution is probably to import the data 'as is' and then run an update query
to tidy it up. Presumably there is another field in the table that
identifies the country? If so, the update query would look something like
...

UPDATE tblWhatever SET PostalCode = IIf([Country] = 'USA',
Left$([PostalCode], 5), Left$([PostalCode], 7))

The above assumes that Country is always either 'USA' or 'Canada', and that
neither PostalCode nor Country can be Null. The expression may need
modification if either of those assumptions is not true.

--
Brendan Reynolds

I am importing a text file into a Access table and I have a postal code
field that contains Canadian and US postal codes. The field is 7
characters
long and I am importing it as a text field. The Canadian date looks
like
this: A0B 2J0 and the US looks like this: 834020000 I need to select
the
first 5 characters of the US postal code and the first 7 of the
Canadian
postal code. Is this possible?


Tim
Access 2000

 

[Marshall Barton]

Clever, but instead of using InStr (and putting it all
together):

Left$(PostalCode, IIf(PostalCode Like "#*", 5, 7)
--
Marsh
MVP [MS Access]

Since U.S. codes begin with a digit and Canadian codes begin with a
letter, you could use an expression something like this to determine the
length of your string:

IIf(InStr("0123456789",Left$([PostalCodes]![Codes],1))>0, 5, 7)

 

[John Spencer]

That is strange. With Access, you should not have had that result. The #
wildcard matches number characters only. I'm glad you found a solution.

That worked with one minor problem. When I ran the statement; Like
"#####*",
it cut off the Canadian postal entries that ended with a number. All I did
is swap the wild card in front of the number placeholders, Like "*#####",
and it worked.

Thank you for the help.
Tim

UPDATE YourTable
Set PostalCode = Left(PostalCode,5)
WHERE PostalCode Like "#####*"

This says to change the postal code the first five characters where the
postal code starts with five numbers. So the presence of a letter or a
space in the first five characters would cause that record to be skipped.

I don't have a field that distinguishes the country of origin. I only have
a
state field that lists the abbreviation for the state or province.

Thanks,
Tim



message
Unless you're already using custom code to import the data, the simplest
solution is probably to import the data 'as is' and then run an update
query
to tidy it up. Presumably there is another field in the table that
identifies the country? If so, the update query would look something like
...

UPDATE tblWhatever SET PostalCode = IIf([Country] = 'USA',
Left$([PostalCode], 5), Left$([PostalCode], 7))

The above assumes that Country is always either 'USA' or 'Canada', and
that
neither PostalCode nor Country can be Null. The expression may need
modification if either of those assumptions is not true.

--
Brendan Reynolds

I am importing a text file into a Access table and I have a postal code
field that contains Canadian and US postal codes. The field is 7
characters
long and I am importing it as a text field. The Canadian date looks
like
this: A0B 2J0 and the US looks like this: 834020000 I need to select
the
first 5 characters of the US postal code and the first 7 of the
Canadian
postal code. Is this possible?


Tim
Access 2000