Update Query Has my Number

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Justin H - 29 Nov 2007 16:31 GMT

Hello and thanks in advance for your time.

I'm putting together a database of the City and Zip Codes some of our
shippers ship to, but I've run into a bit of a situation. Some of the zip
codes I've received from them(around 4500 entries) are in a range format (ex.
80030-35) and I hoping there is a way to code the update query to separate
that out into 80030 80031 80032 80034 80035 but I can't seem to figure out
the correct code.

Any Ideas?

Reply to this Message

raskew - 29 Nov 2007 17:37 GMT

Hi -

The Val() function will return a number up to the first non-decimal delimiter
(in this case the hypen, if it exists).

The Str() function converts a number to a string. Example from the debug
window:
x = "80034-1234"
? val(x)
80034
y = str(val(x))
? y
80034
To verify that y is in text format. Look up vartype() for the various codes.
? vartype(y)
8

HTH - Bob

>Hello and thanks in advance for your time.
>
[quoted text clipped - 6 lines]
>
>Any Ideas?

Reply to this Message

KARL DEWEY - 29 Nov 2007 17:41 GMT

This is assuming that all ranges are stated in the same fashion - 80030-35.
Create a table named CountNumber with field named CountNUM containing
numbers from 0 (zero) through your maximum range.

INSERT INTO Your_Table ( Zip )
SELECT Val(Left([Zip_Range],5)+[CountNUM]) AS Expr2
FROM CountNumber, Your_Table
WHERE (((Len([Zip_Range]))>5) AND
((Val(Left([Zip_Range],5)+[CountNUM]))<=Val(Left([Zip_Range],Len([Zip_Range])-InStr([Zip_Range],"-")+1)
& Right([Zip_Range],Len([Zip_Range])-InStr([Zip_Range],"-")))));

Signature

KARL DEWEY
Build a little - Test a little

> Hello and thanks in advance for your time.
>
[quoted text clipped - 6 lines]
>
> Any Ideas?

Reply to this Message