What does LEN return? 2

[JS1521253]

I am trying to use the following formula within a query:

IIf(IsNumeric([AccountNumber]),IIf(Len([AccountNumber]=10),"1","0"),"0")

Ultimately, I am trying to create a case hit that shows that the accountnumber is numeric and is 10 numbers long. I continue to get a Data Match error. I want to count the number of characters in my AccountNumber. Any ideas?

Thanks

 

[PHV]

IIf(IsNumeric([AccountNumber]),IIf(Len([AccountNumber][!])[/!]=10,"1","0"),"0")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

Are you trying to use the results to count the number of account numbers that are formatted correctly? If so, you need to lose the quotes.


Randy

 

[JS1521253]

Thank you both. I had been staring at this forever, but couldn't see the goofy mistakes.

Just to complete the thread I've included the final code:

IIf(IsNumeric([PatientAccountNumber]),IIf(Len([PatientAccountNumber])=10,1,0),0)

Thanks again,

 

[PHV]

A shorter way:

IIf([PatientAccountNumber] Like '##########',1,0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886