Comparing a Postal Code against a Zone Table from UPS

[Guest]

Hello,

I got a table with Postal Codes and Zones from UPS Canada.

The data is like this:

'Postal Code' 'Express' 'Expedited' 'Standard'
'A0A–A9Z' '411' '311' '211'
'B0A–B2T' '411' '311' '211'

I want to determine the zone depending of the postal code.

In fact the 'A0A–A9Z' represents a range.

I want to build a select query in which we will copy the postal code and
get the zone.

I got a hard time matching a postal code or the first 3 values of a postal
code to the those ranges.


Thank You

uberblick

 

[Douglas J. Steele]

Have 5 fields in the table: PostalCodeStart, PostalCodeEnd, Express,
Expedited and Standard.

Create a query that joins the two tables, then go into the SQL of the query
and change the Join criteria to:

FROM Table1 INNER JOIN UPSTable
ON Left(Table1.PostalCode, 3) BETWEEN UPSTable.PostalCodeStart AND
UPSTable.PostCodeEnd

 

[Guest]

Douglas,

Thanks

My table contains the postal code range (Start and End) and Zones per service.

You're telling me to have 5 fields in the table which is ok.

Than you said to join the 2 tables, which is where I am confused.

What is the 2nd table suppose to be ?

Thanks

uberblick

 

[Guest]

I have enclosed my select query here:

PARAMETERS PostalCode Text ( 255 );
SELECT ZonesCanada.Expedited
FROM ZonesCanada
WHERE (("WHERE ([PostalCode],3)" BETWEEN [ZonesCanada]![PCStart] AND
[ZonesCanada]![PCEnd]));


If I try it I get a an empty field with the header column "Expedited"

 

[Douglas J. Steele]

I just assumed that you had a table of addresses, and you wanted to know the
rates for each of them.

If you simply want to be prompted for a postal code, use

PARAMETERS PostalCode Text ( 255 );
SELECT ZonesCanada.Expedited
FROM ZonesCanada
WHERE Left([PostalCode],3) BETWEEN [PCStart] AND [PCEnd]

 

[Guest]

Thanks Douglas.

But I get a blank field with the column header "Expedited"

Regards

uberblick

I just assumed that you had a table of addresses, and you wanted to know the
rates for each of them.

If you simply want to be prompted for a postal code, use

PARAMETERS PostalCode Text ( 255 );
SELECT ZonesCanada.Expedited
FROM ZonesCanada
WHERE Left([PostalCode],3) BETWEEN [PCStart] AND [PCEnd]



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

Thanks

My table contains the postal code range (Start and End) and Zones per
service.

You're telling me to have 5 fields in the table which is ok.

Than you said to join the 2 tables, which is where I am confused.

What is the 2nd table suppose to be ?

Thanks

uberblick

 

[John W. Vinson]

I have enclosed my select query here:

PARAMETERS PostalCode Text ( 255 );
SELECT ZonesCanada.Expedited
FROM ZonesCanada
WHERE (("WHERE ([PostalCode],3)" BETWEEN [ZonesCanada]![PCStart] AND
[ZonesCanada]![PCEnd]));

You're comparing the PCStart and PCEnd fields to the literal text string
"WHERE ([PostalCode],3)"

which makes no sense at all.

Try

PARAMETERS PostalCode Text ( 255 );
SELECT ZonesCanada.Expedited
FROM ZonesCanada
WHERE Left([PostalCode],3) BETWEEN [ZonesCanada].[PCStart] AND
[ZonesCanada].[PCEnd];

John W. Vinson [MVP]

 

[Douglas J. Steele]

You sure your table has data corresponding to the postal code you entered?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks Douglas.

But I get a blank field with the column header "Expedited"

Regards

uberblick

I just assumed that you had a table of addresses, and you wanted to know
the
rates for each of them.

If you simply want to be prompted for a postal code, use

PARAMETERS PostalCode Text ( 255 );
SELECT ZonesCanada.Expedited
FROM ZonesCanada
WHERE Left([PostalCode],3) BETWEEN [PCStart] AND [PCEnd]



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

Thanks

My table contains the postal code range (Start and End) and Zones per
service.

You're telling me to have 5 fields in the table which is ok.

Than you said to join the 2 tables, which is where I am confused.

What is the 2nd table suppose to be ?

Thanks

uberblick


:

Have 5 fields in the table: PostalCodeStart, PostalCodeEnd, Express,
Expedited and Standard.

Create a query that joins the two tables, then go into the SQL of the
query
and change the Join criteria to:

FROM Table1 INNER JOIN UPSTable
ON Left(Table1.PostalCode, 3) BETWEEN UPSTable.PostalCodeStart AND
UPSTable.PostCodeEnd

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hello,

I got a table with Postal Codes and Zones from UPS Canada.

The data is like this:

'Postal Code' 'Express' 'Expedited' 'Standard'
'A0A-A9Z' '411' '311' '211'
'B0A-B2T' '411' '311' '211'

I want to determine the zone depending of the postal code.

In fact the 'A0A-A9Z' represents a range.

I want to build a select query in which we will copy the postal code
and
get the zone.

I got a hard time matching a postal code or the first 3 values of a
postal
code to the those ranges.


Thank You

uberblick