Re: Postal Code

Tech-Archive recommends: Speed Up your PC by fixing your registry

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.

"tim" <hawk88gt@xxxxxxxxx> wrote in message
news:D_mdnU8az7_0YRzenZ2dnUVZ_v6dnZ2d@xxxxxxxxxxxx
>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
>
>
>
> "Brendan Reynolds" <brenreyn@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:%23D4izer7FHA.1140@xxxxxxxxxxxxxxxxxxxxxxx
>> 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
>>
>> "tim" <hawk88gt@xxxxxxxxx> wrote in message
>> news:isudnTnYTJNkbRzeRVn-uQ@xxxxxxxxxxxx
>> >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
>> >
>> >
>>
>>
>
>


.

Relevant Pages

  • Re: Postal Code
    ... identifies the country? ... the update query would look something like ... > field that contains Canadian and US postal codes. ... > first 5 characters of the US postal code and the first 7 of the Canadian ...
    (microsoft.public.access.queries)
  • Re: Postal Code
    ... approach would be to create a table with two fields, Country and PostalCode, ... >>> field that contains Canadian and US postal codes. ... >>> long and I am importing it as a text field. ...
    (microsoft.public.access.queries)
  • Re: Postal Code
    ... 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 ... The above assumes that Country is always either 'USA' or 'Canada', and that neither PostalCode nor Country can be Null. ... first 5 characters of the US postal code and the first 7 of the Canadian ...
    (microsoft.public.access.queries)
  • Re: Trim Help
    ... country has a trunk prefix which is dialed just before the area code for a ... phone number begins with the International Prefix of the country of origin, ... Then there's the bit about alpha characters. ...
    (microsoft.public.sqlserver.programming)
  • Damn it Shawn YHBT by an Imposter (was: Rookie Jokes ?)
    ... StateProv: NJ ... PostalCode: 08054 ... Country: US ... Hmm, that is pretty much like Dave Ruff's IP address, let's look his up his ...
    (misc.transport.trucking)