Update Query Has my Number

[Justin H]

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?

 

[raskew via AccessMonster.com]

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

 

[KARL DEWEY]

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],"-")))));