Problem with sum

[Erictsang]

Dear all

SELECT Registration.[ID], Registration.[Appl], Registration.[C_FULLNAME], Registration.[Lunch], Registration.[Culture], Registration.[Youth], Registration.[Opening], Registration.[Multimeida], Registration.[Wel_BNQ], Registration.[Lunch]+[Culture]+[Youth]+[Opening]+[Multimeida]+[Wel_BNQ] AS Total


FROM Registration;


I have problem in adding all these fields

What's wrong?
Eric

[Bob Fitz]

Try:

SELECT Registration.[ID], Registration.[Appl], Registration.[C_FULLNAME], Registration.[Lunch], Registration.[Culture], Registration.[Youth], Registration.[Opening], Registration.[Multimeida], Registration.[Wel_BNQ], Registration.Nz([Lunch],0)+Nz([Culture],0)+Nz([Youth],0)+Nz([Opening],0)+Nz([Multimeida],0)+Nz([Wel_BNQ],0) AS Total
FROM Registration;

[Erictsang]

Bob

Error

[Bob Fitz]

Maybe:

SELECT Registration.[ID], Registration.[Appl], Registration.[C_FULLNAME], Registration.[Lunch], Registration.[Culture], Registration.[Youth], Registration.[Opening], Registration.[Multimeida], Registration.[Wel_BNQ], Nz([Lunch],0)+Nz([Culture],0)+Nz([Youth],0)+Nz([Opening],0)+Nz([Multimeida],0)+Nz([Wel_BNQ],0) AS Total
FROM Registration;

[Erictsang]

Bob

How does it work . it is done

Nz([Lunch],0)+Nz([Culture],0)+Nz([Youth],0)+Nz([Opening],0)+Nz([Multimeida],0)+Nz([Wel_BNQ],0) ?!

NZ is a operator ? did i did anything wrong

as the data are all imported so i have no control

Eric

[Erictsang]

Bob

Can you teach me one more formula to count if no blank

See attached

I can sum the Nites but for the room type, i only want to count those has records 雙床 (兩張單人床)"" / or better
not blank .. as another hotel will have different names

Thanks

[Bob Fitz]

I believe the problem you had was that one of your fields contained Null values. AFAIK, a number + Null = Null
Nz() is a function which looks for a null value in the value supplied as the first argument and returns the value of the second argument if the first is null.
See: http://www.fmsinc.com/microsoftaccess/vba/nz.htm

[Erictsang]

Bob

Thanks but sorry for my ignorance, i a fresh hand

How can i handle it, perherps i go to Queries and base the report on Query ?

AFAIK, a number + Null = Null, to me i do not understand

Eric

[Bob Fitz]

Bob

Can you teach me one more formula to count if no blank

See attached

I can sum the Nites but for the room type, i only want to count those has records 雙床 (兩張單人床)"" / or better
not blank .. as another hotel will have different names

Thanks

Sorry, but I don't understand your requirement.

[Erictsang]

Bob

In that case i want to count only if the field got the word 雙床 (兩張單人床)""
The answer should be 5 instead of 9

Eric

[June7]

Simple arithmetic with multiple fields when any field is null will return null as result regardless of value in other fields.

The aggregate calculation to conditionally exclude records with null field:

=Count(IIf(IsNull([Room_Type]), Null, 1))

or

=Count(IIf([Room_Type] Is Null, Null, 1))

or

=Sum(IIf(IsNull([Room_Type]), 0, 1))

or

=Sum(IIf([Room_Type] Is Null, 0, 1))