Using iif(iserror( or iif(isnumeric( in Access table Order By

[philqw78]

I am trying to sort a list of alphanumeric ID's in numeric order using the OrderBy property of a table. They are in the format of 2 letters followed by numbers. Like this "AA10199"
However the number is not an autonumber part, it is entered by users. Sometimes these users put too many letters in, like this "AAa10199" so my original sort for the table fails.
This was

 cint(mid([ar_id],3,8))

Sorted ascending.
But with extra letters added an error is returned and the sort fails, so I thought this may work.

 IIf((IsNumeric(CInt(Mid([ar_id],3,8)))),CInt(Mid([ar_id],3,8)),0)

It doesn't, it just produces an #Error value and will not sort. It fails with IsError as well. What am I doing wrong?

[NeoPa]

IIf() evaluates both options regardless of the result of the first parameter. Thus it will error if the value is wrong.

This is fundamentally down to the logic in your code being a bit back-to-front. Try instead :

CInt(Mid([ar_id],IIf(IsNumeric(Mid([ar_id],3)),3,4)))

I don't have your database to test in so I hope I have the parentheses balanced and I hope your data is as indicated. If tweaks are required then please post results and/or problems and we'll see what we can do to fine-tune it.

[philqw78]

Thanks Neo. Worked great. Then realised that they will find a better fool if you make something foolproof. Have solved the problem with validation.

ALike "[a-z]R[123456789]" Or ALike "[a-z]R[123456789][1234567890]" Or ALike "[a-z]R[123456789][1234567890][1234567890]" Or ALike "[a-z]R[123456789][1234567890][1234567890][1234567890]" Or ALike "[a-z]R[123456789][1234567890][1234567890][1234567890][1234567890]" Or ALike "[a-z]R[123456789][1234567890][1234567890][1234567890][1234567890][1234567890]"

A shorter version of that I couldn't figure out, again. But it will make them input the required coding. For the error message

Must Be: letter A to Z, "R", Number 1-999999

[NeoPa]

That's good thinking Phil. A helpful link on setting this type of think up can be found at Find Wildcard Characters in an Access Database.

At a rough guess I expect you could simplify that to :

ALike '[a-z]R[1-9]' Or ALike '[a-z]R[1-9]#' Or ALike '[a-z]R[1-9]##' Or ALike '[a-z]R[1-9]###' Or ALike '[a-z]R[1-9]####' Or ALike '[a-z]R[1-9]#####'

From your first post though (and I may be wrong here), you may want to use ...[a-zA-Z]... in place of the ...[a-z].... You decide which is more appropriate for you ;-)