Adding field values within Query

Question

I have the following Query:

I want the QTotal to show the total of the Q1 to Q4 fields but it comes up blank.

I have tried:

 Qtotal: Nz([Q1])+Nz([Q2])+Nz([Q3])+Nz([Q4])

but this throws and exepction the first time the query is run and then it works.

Any thoughts?

Obviously, that's a VBA error, not an SQL one. Share the relevant VBA. How are you calling this query? — Erik A
– Erik A, Commented Feb 24, 2020 at 12:53

NautMeg · Accepted Answer · 2020-02-24 12:53:57Z

1

You need to re-write that query using IIf(IsNull... instead of Nz(... Pretty sure there are some incompatibility issues with the Nz function.

answered Feb 24, 2020 at 12:53

NautMeg

1416 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

RGriffiths Over a year ago

Perfect. Changed to Qtotal: IIf(IsNull([Q1]),0,[Q1])+IIf(IsNull([Q2]),0,[Q2])+IIf(IsNull([Q3]),0,[Q3])+IIf(IsNull([Q4]),0,[Q4]) and all good. I guess that is what you meant Thanks

Collectives™ on Stack Overflow

Adding field values within Query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related