Criteria for column with number and text values

[jset818]

I'm trying to create a query that pulls any data (text or number) from the [Hyperion Code] field, but I want to exclude the data with 6 number digits. The issue I'm having is that the column from the source table is formatted as Text. Thus, making it difficult to set up a criteria for it. I tried reformatting source data format to Number, Text, and General but that usually causes the Text values to return as #Num.



So I want to exclude 356673, 463001, 917000, 842700 etc.... from the column. How would I set up a criteria for this?

[June7]

It looks like the 6-character values are all digits.

WHERE Len([Hyperion Code])<>6

[rpeare]

if it's important that the string be BOTH numeric AND 6 characters long to be excluded I would create a field like:

Ind: IIf(IsNumeric([hyperion code]) And Len([hyperion code])<>6,0,1)

then put in a criteria of 1 on the IND field to show all items that DO NOT match numeric/6 digits long

[June7]

Like it, but think need to = 6.

Ind: IIf(IsNumeric([hyperion code]) And Len([hyperion code])=6,0,1)

[rpeare]

you're absolutely right, screwed up the sign.

[jset818]

Thanks guys. The iif function worked great. I did made some little adjustments to it because sometimes the hyperion code can be a single digit like 6.

Hyperion Code: IIf(IsNumeric([hyperion code]) And Len([hyperion code])=6,"6 Digit Hyperion Code",[hyperion code])