What is wrong with this query?

[Opal]

I am trying to sum records in a query and I am not getting
a value for the following:

SELECT TotalOnline.Date, TotalOnline.Shift, [SM2]+[Susp]+[Drs]+[QC]+
[Ins]+[Rep]+[Zone]+[SM3]+[TB]+[Fr] AS TotalOnline
FROM TotalOnline;

What am I missing, I am stumped!

 

[fredg]

I am trying to sum records in a query and I am not getting
a value for the following:

SELECT TotalOnline.Date, TotalOnline.Shift, [SM2]+[Susp]+[Drs]+[QC]+
[Ins]+[Rep]+[Zone]+[SM3]+[TB]+[Fr] AS TotalOnline
FROM TotalOnline;

What am I missing, I am stumped!

1) I would suspect that one or more of the fields may contain a Null
value. A value plus a Null is Null.
Use the Nz() function. (See VBA help for more information).

Select .... ,Nz([SM2])+Nz([Susp]) + Nz([Drs]) ... etc... AS
TotalOnline

2) Date is a reserved Access/VBA/Jet word and should not be used as a
field name.
For additional reserved words, see the Microsoft KnowledgeBase article
for your version of Access:

109312 'Reserved Words in Microsoft Access' for Access 97
209187 'ACC2000: Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'
321266 'ACC2002: Microsoft Jet 4.0 Reserved Words'

For an even more complete list of reserved words, see:
http://www.allenbrowne.com/AppIssueBadWord.html

 

[Jerry Whittle]

Any null, empty string, or non-number values in any of those fields? That
could do it.

Probably nulls or you'd most likely see an error message. In that case
surround each field with the NZ function to set nulls to 0 like so:

NZ([SM2],0)+NZ([Susp],0)+ and so on.

 

[Opal]

Any null, empty string, or non-number values in any of those fields? That
could do it.

Probably nulls or you'd most likely see an error message. In that case
surround each field with the NZ function to set nulls to 0 like so:

NZ([SM2],0)+NZ([Susp],0)+  and so on.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am trying to sum records in a query and I am not getting
a value for the following:

SELECT TotalOnline.Date, TotalOnline.Shift, [SM2]+[Susp]+[Drs]+[QC]+
[Ins]+[Rep]+[Zone]+[SM3]+[TB]+[Fr] AS TotalOnline
FROM TotalOnline;

What am I missing, I am stumped!- Hide quoted text -

- Show quoted text -

Thank you!