Working with 16-digit Text numbers.

[Robeen]

I have a Text field named 'Code' that has 2, 3, 4 & 16 digit 'numbers'.


These are codes - so I will never do math with them.

BUT . . . I want to be able to return a range of codes [say 1000 - 1999, inclusive] in a query. Only with 4-digit code ranges . . .

How do I get the query to correctly do the equivalent of this:
IIf ((Code > "999" and Code < "2000"), DisplaySomething, DisplaySomethingElse)

OR

[How] can I successfully convert the field to a numeric field that will handle 16-digit numbers and then let me do the numeric query?

Thanks!!

[June7]

Access is not going to play nice with 16 digits so converting to number with functions like Val() will likely fail.

Need placeholder zeros, like: 0000000000000999, 0000000000002000, 0567254526352564. Use Format() function.

IIf(Format([Code], "0000000000000000") > "0000000000000999" AND Format([Code], "0000000000000000") < "0000000000002000"), this, else this)

Review https://support.microsoft.com/en-us/kb/210573

[ssanfu]

Hey, Robeen.... long time....

Totally untested... maybe:
EDIT: Just tested. Seems to work correctly

Convert all "numbers" to 16 characters and test.

SomeName: IIf ((Right("000000000000000" & code,16) > "0000000000000999" and Right("000000000000000" & code,16)< "0000000000002000"), DisplaySomething, DisplaySomethingElse)