query search

[fLum0x]

i want to take zip codes in a field that are either 5 or more digits long and shrink them all down to 5 digits. and i also want to search those 5 digits to see if they match one of 50 numbers. how do i do it? i can probably figure the 2nd part out, but the first part i am struggling with.

 

[MrChad]

What DBMS? What do you mean "5 or more digits long"?

 

[fLum0x]

Microsoft Access 2003 is the software. And 5 or more digits long meaning that i basically want to concatinate(sp?) it. If it is 9 digits or 10 digits, i just want the FIRST 5 of it. So basically i was to be able to search all of the zip codes (with extensions) to see if the zip codes match specific 5 digit numbers. Anything more than 5 digits has to be excluded.

 

[EagleKeeper]

Are you using a string to contain the digits. Are the digits in a zip+4 format or a 9 digit number.

You first will need to boil the digits down to a five digit package.
If they are stored as a string, then just look at the left 5 characters.

Left(<field name>,5)

Left is a function built into Access

/edit - Clarificaiton of format

 

[fLum0x]

It is a text field in Access, so i assume that means it is a string. I know i have to look at the left 5 characters...but how do i do that? that is the question at hand.

 

[MrChad]

You can use LEFT(zipcode, 5), where zipcode is the field containing your zip codes.

 

[fLum0x]

thanks