Filter out rows based on number of characters (1 Viewer)

[BukHix]

I have an address database where people have been allowed to type in any characters they want in place of just leaving a missing field blank. For instance the Zip code was not a required field (don't as me why because I don't know. It just wasn't) so over the years when the person entering the data didn't know what the zip code was they would put a ?, a 0, a 000 or a period or what ever else they could think of at the time.

I would like to add a filter in my query where I could eliminate any thing with less then a certain amount of characters.

I thought something like this would do the trick

Zip: IIf(Len([V_ZIP]<5),[V_ZIP],"0")

But I am getting a data type mismatch in criteria expression. Any ideas of what I am doing wrong?

 

[KenHigg]

ZipCode: IIf(Len([V_ZIP])<5,"0",[V_ZIP])

or

ZipCode: IIf(Len(str([V_ZIP]))<5,"0",[V_ZIP])


???

 

[BukHix]

Oops I had the syntax wrong and backwards.

I am still getting a data mismatch error when it comes to some rows. Not sure which cause there are 50,000 records and it the quesry will run for 3 or 4 seconds before it runs into the troublesome fields. I am thinking as a work around to just add a ZipCheck field to the end and then filter from that.

Like this: ZipCk: Len([V_ZIP]) and then filter using >5 as the criteria.

 

[KenHigg]

You may simply need to check for nulls:

IIf(Len([V_ZIP] & "")<5,"",[V_ZIP])

???

 

[BukHix]

Thanks Ken that took care of it.