What is wrong with this query?

  • Thread starter Thread starter Opal
  • Start date Start date
O

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!
 
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!
Click to expand...

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
 
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.
 
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.



Opal said:
I am trying to sum records in a query and I am not getting
a value for the following:
Click to expand...
SELECT TotalOnline.Date, TotalOnline.Shift, [SM2]+[Susp]+[Drs]+[QC]+
[Ins]+[Rep]+[Zone]+[SM3]+[TB]+[Fr] AS TotalOnline
FROM TotalOnline;
Click to expand...
What am I missing, I am stumped!- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thank you!
 
Back
Top