#error after 2003 to 2010 Access upgrade

[kheneasar]

Hi Everyone,

We recently upgraded Access to 2010 and one of our queries now returns #error.



=IIf(IIf(Month(Date())=1,13,Month(Date()))<2," ",Count(IIf(([OR_Test_Type_1]='ID Data' Or [OR_Test_Type_1]='Local / ID Data' Or [OR_Test_Type_1]='Local / MAC' Or [OR_Test_Type_1]='Topical / ID Data' Or [OR_Test_Type_1]='MAC / Topical / ID Sed' Or [OR_Test_Type_1]='Data Block' Or [OR_Test_Type_1]='Data Mac') And [Expr1]='01',[OR_Test_Type_1])))

If we remove the text highlighted in red, it runs just fine. I'm guessing the Count is not returning a value when it looks for 'Data Block and Mac'? Does anyone know how to use HasData or maybe change the count to a sum operator to get this to work again?

Thanks,
Khen

[ItsMe]

This is SQL within an SQL statement? Where is your expression used, in a form, in a query object? Is Data Block and or Data Mac literal text? Perhaps you should test for null fields before applying results to your function.

[kheneasar]

Yes it's a SQL query tab in Access, used in a query object. Data Block and Data Mac both represent literal text.

If a NULL field exists, does that mean it will always return #error in 2010 Access?

Thanks

[June7]

No idea why including those two parameters would cause an issue. Null field should not cause issue. I just tested a similar Count expression. I don't understand need for: AND Expr1='01'.

Try:

Sum(IIf(([OR_Test_Type_1]='ID Data' Or [OR_Test_Type_1]='Local / ID Data' Or [OR_Test_Type_1]='Local / MAC' Or [OR_Test_Type_1]='Topical / ID Data' Or [OR_Test_Type_1]='MAC / Topical / ID Sed' Or [OR_Test_Type_1]='Data Block' Or [OR_Test_Type_1]='Data Mac') And [Expr1]='01', 1, 0)))

But I suspect will generate error as well if the issue caused by those two parameters.

[kheneasar]

Thank you. I'll give it a shot and let you know what happens.

[kheneasar]

I tried...

Sum(IIf(([OR_Test_Type_1]='ID Data' Or [OR_Test_Type_1]='Local / ID Data' Or [OR_Test_Type_1]='Local / MAC' Or [OR_Test_Type_1]='Topical / ID Data' Or [OR_Test_Type_1]='MAC / Topical / ID Sed' Or [OR_Test_Type_1]='Data Block' Or [OR_Test_Type_1]='Data Mac') And [Expr1]='01', 1, 0)))

and it's still returning #error.

Anyone happen to have any other ideas? Thank you

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[NTC]

to trouble shoot you should de-nest the nested IIF statements... temporarily...put each iif into its own field in query design mode...with some plugged filler values too....

also I note seeing: [Expr1]

while it might be your field name - it is often the default value applied to a missing field - so this could be an issue...