IIF condition -Newbie

[bldrc]

Here's a current IIF string i'm trying to change/modify for Access 2007.



X: Sum(IIf(IsNumeric(Left([Prod],1)),1,0)

I'm trying to get it to add up only if [Prod] is 11 characters instead of the above in this format: "1234-A-56.7" (numbers, dashes, letters and decimal). I've tried using "Len" instead of "IsNumeric" but since it contains different characters, it gives me a syntax error. Any point in the right direction would be greatly appreciated.

Kind Regards

[pbaldy]

By my count you're missing the ending parentheses of the Sum() function.

[bldrc]

My apologies, I didn't copy the entire thing.

It should be

X: Sum(IIf(IsNumeric(Left([Prod],1)),1,0))

Thanks

[pbaldy]

You get a syntax error when trying to create it or when trying to run it? Could the Prod field ever be Null?

[bldrc]

The syntax error happens when i try run it. the [Prod] field is never null and always in the same format.

The above code works great for other items we have but I want to modify it to work for the 11 character "1234-A-56.7".

I have no clue on where to begin.

Thanks

[pbaldy]

Can you post the db, or a representative sample? I just did a brief test and a query with that formula did not error on that value.

[bldrc]

Here's a sample list of product numbers. I don't need it to give me a sum for the 6 digit numbers.

Thanks for your help.

[pbaldy]

This returns 33 for that data:

X: Sum(IIf(Len([Prod])=11,1,0))

[bldrc]

That did the trick, pbaldy. Exactly what I was looking for. Thanks for all your help.

[pbaldy]

No problem, and welcome to the site.