Query won't total numerals

[Nona]

tI hope I'm not coming to the well too often, but I have another, different
question.

In a query for a form, I have fields that total hours assigned for each day
of the week. The total results, calculated in the query, are then displayed
on the form and also on a report.

However, the query won't calculate unless a numberal has been entered for
each day. In other words, if there are no assigned hours for a day, it cannot
be left blank or it won't calculate the total. This is in spite of the fact
that the default for each day's hours has been set at zero.

Is there a solution for this? Do I use the If Null function for each day's
hours?

I'd appreciate your advice.

 

[KARL DEWEY]

Use the Nz function to return a 0 (zero) if the field is null (left blank).
Nz([YourField], 0)

 

[Nona]

Thanks, Karl, I appreciate your help. I’ve added the Nz function to most of
the fields and can see that it does indeed populate the fields.

However, there is a new problem. It still doesn’t total correctly, but not
for the earlier reason. It’s almost as though the new figures are not
considered numbers. The properties won’t allow me to format the control, but
it does allow decimals. That doesn’t really help because when I run the
query, the return is a list of the same numerals I had entered (not totaled).

Here is one of the fields with the Nz function added: ThTLS: Nz([ThHrsTLS],0)
Here is the total field (adding entries for each day of the week):
TotTLS1: ([ThTLS]+[FrTLS]+[SaTLS]+[SuTLS]+[MoTLS]+[TuTLS]+[WeTLS])

The return I get with the total is: “2100111†which are the numerals I
entered individually for each day of the week.

I hope you can help me understand what’s going on with this. I’m stumped!

Thank you.

--
Nona

Use the Nz function to return a 0 (zero) if the field is null (left blank).
Nz([YourField], 0)
--
KARL DEWEY
Build a little - Test a little

tI hope I'm not coming to the well too often, but I have another, different
question.

In a query for a form, I have fields that total hours assigned for each day
of the week. The total results, calculated in the query, are then displayed
on the form and also on a report.

However, the query won't calculate unless a numberal has been entered for
each day. In other words, if there are no assigned hours for a day, it cannot
be left blank or it won't calculate the total. This is in spite of the fact
that the default for each day's hours has been set at zero.

Is there a solution for this? Do I use the If Null function for each day's
hours?

I'd appreciate your advice.

 

[Nona]

Thanks, Karl, I appreciate your help. I’ve added the Nz function to most of
the fields and can see that it does indeed populate the fields.

However, there is a new problem. It still doesn’t total correctly, but not
for the earlier reason. It’s almost as though the new figures are not
considered numbers. The properties won’t allow me to format the control, but
it does allow decimals. That doesn’t really help because when I run the
query, the return is a list of the same numerals I had entered (not totaled).

Here is one of the fields with the Nz function added: ThTLS: Nz([ThHrsTLS],0)
Here is the total field (adding entries for each day of the week):
TotTLS1: ([ThTLS]+[FrTLS]+[SaTLS]+[SuTLS]+[MoTLS]+[TuTLS]+[WeTLS])

The return I get with the total is: “2100111†which are the numerals I
entered individually for each day of the week.

I hope you can help me understand what’s going on with this. I’m stumped!

Thank you.

--
Nona

Use the Nz function to return a 0 (zero) if the field is null (left blank).
Nz([YourField], 0)
--
KARL DEWEY
Build a little - Test a little

tI hope I'm not coming to the well too often, but I have another, different
question.

In a query for a form, I have fields that total hours assigned for each day
of the week. The total results, calculated in the query, are then displayed
on the form and also on a report.

However, the query won't calculate unless a numberal has been entered for
each day. In other words, if there are no assigned hours for a day, it cannot
be left blank or it won't calculate the total. This is in spite of the fact
that the default for each day's hours has been set at zero.

Is there a solution for this? Do I use the If Null function for each day's
hours?

I'd appreciate your advice.

 

[Nona]

Thanks, Karl, I appreciate your help. I’ve added the Nz function to most of
the fields and can see that it does indeed populate the fields.

However, there is a new problem. It still doesn’t total correctly, but not
for the earlier reason. It’s almost as though the new figures are not
considered numbers. The properties won’t allow me to format the control, but
it does allow decimals. That doesn’t really help because when I run the
query, the return is a list of the same numerals I had entered (not totaled).

Here is one of the fields with the Nz function added: ThTLS: Nz([ThHrsTLS],0)
Here is the total field (adding entries for each day of the week):
TotTLS1: ([ThTLS]+[FrTLS]+[SaTLS]+[SuTLS]+[MoTLS]+[TuTLS]+[WeTLS])

The return I get with the total is: “2100111†which are the numerals I
entered individually for each day of the week.

I hope you can help me understand what’s going on with this. I’m stumped!

Thank you.

--
Nona

Use the Nz function to return a 0 (zero) if the field is null (left blank).
Nz([YourField], 0)
--
KARL DEWEY
Build a little - Test a little

tI hope I'm not coming to the well too often, but I have another, different
question.

In a query for a form, I have fields that total hours assigned for each day
of the week. The total results, calculated in the query, are then displayed
on the form and also on a report.

However, the query won't calculate unless a numberal has been entered for
each day. In other words, if there are no assigned hours for a day, it cannot
be left blank or it won't calculate the total. This is in spite of the fact
that the default for each day's hours has been set at zero.

Is there a solution for this? Do I use the If Null function for each day's
hours?

I'd appreciate your advice.

 

[Nona]

I apologize for those multiple entries. After I sent the first one, I got an
error message that there was a network problem and I should try again. I did
and each time got an error message. Imagine my surprise when I saw I was
hounding the system! So sorry!
--
Nona

Thanks, Karl, I appreciate your help. I’ve added the Nz function to most of
the fields and can see that it does indeed populate the fields.

However, there is a new problem. It still doesn’t total correctly, but not
for the earlier reason. It’s almost as though the new figures are not
considered numbers. The properties won’t allow me to format the control, but
it does allow decimals. That doesn’t really help because when I run the
query, the return is a list of the same numerals I had entered (not totaled).

Here is one of the fields with the Nz function added: ThTLS: Nz([ThHrsTLS],0)
Here is the total field (adding entries for each day of the week):
TotTLS1: ([ThTLS]+[FrTLS]+[SaTLS]+[SuTLS]+[MoTLS]+[TuTLS]+[WeTLS])

The return I get with the total is: “2100111†which are the numerals I
entered individually for each day of the week.

I hope you can help me understand what’s going on with this. I’m stumped!

Thank you.

--
Nona

Use the Nz function to return a 0 (zero) if the field is null (left blank).
Nz([YourField], 0)
--
KARL DEWEY
Build a little - Test a little

tI hope I'm not coming to the well too often, but I have another, different
question.

In a query for a form, I have fields that total hours assigned for each day
of the week. The total results, calculated in the query, are then displayed
on the form and also on a report.

However, the query won't calculate unless a numberal has been entered for
each day. In other words, if there are no assigned hours for a day, it cannot
be left blank or it won't calculate the total. This is in spite of the fact
that the default for each day's hours has been set at zero.

Is there a solution for this? Do I use the If Null function for each day's
hours?

I'd appreciate your advice.

 

[KARL DEWEY]

I sounds like your fields are not numbers but text. Open the table in design
view and check the properties of the fields. Change datatype to number -
single.

BTW you should not use a separate field for each entry but a new record like
this --
Employee WorkDate Hours
Bill Jones 1/2/2009 4.5
Bill Jones 1/3/2009 6.7
Fred Rex 1/2/2009 7.9
Fred Rex 1/3/2009 8.0
--
KARL DEWEY
Build a little - Test a little

I apologize for those multiple entries. After I sent the first one, I got an
error message that there was a network problem and I should try again. I did
and each time got an error message. Imagine my surprise when I saw I was
hounding the system! So sorry!
--
Nona

Thanks, Karl, I appreciate your help. I’ve added the Nz function to most of
the fields and can see that it does indeed populate the fields.

However, there is a new problem. It still doesn’t total correctly, but not
for the earlier reason. It’s almost as though the new figures are not
considered numbers. The properties won’t allow me to format the control, but
it does allow decimals. That doesn’t really help because when I run the
query, the return is a list of the same numerals I had entered (not totaled).

Here is one of the fields with the Nz function added: ThTLS: Nz([ThHrsTLS],0)
Here is the total field (adding entries for each day of the week):
TotTLS1: ([ThTLS]+[FrTLS]+[SaTLS]+[SuTLS]+[MoTLS]+[TuTLS]+[WeTLS])

The return I get with the total is: “2100111†which are the numerals I
entered individually for each day of the week.

I hope you can help me understand what’s going on with this. I’m stumped!

Thank you.

--
Nona

Use the Nz function to return a 0 (zero) if the field is null (left blank).
Nz([YourField], 0)
--
KARL DEWEY
Build a little - Test a little


:

tI hope I'm not coming to the well too often, but I have another, different
question.

In a query for a form, I have fields that total hours assigned for each day
of the week. The total results, calculated in the query, are then displayed
on the form and also on a report.

However, the query won't calculate unless a numberal has been entered for
each day. In other words, if there are no assigned hours for a day, it cannot
be left blank or it won't calculate the total. This is in spite of the fact
that the default for each day's hours has been set at zero.

Is there a solution for this? Do I use the If Null function for each day's
hours?

I'd appreciate your advice.

 

[Nona]

Thanks, Karl, I appreciate your help. I’ve added the Nz function to most of
the fields and can see that it does indeed populate the fields.

However, there is a new problem. It still doesn’t total correctly, but not
for the earlier reason. It’s almost as though the new figures are not
considered numbers. The properties won’t allow me to format the control, but
it does allow decimals. That doesn’t really help because when I run the
query, the return is a list of the same numerals I had entered (not totaled).

Here is one of the fields with the Nz function added: ThTLS: Nz([ThHrsTLS],0)
Here is the total field (adding entries for each day of the week):
TotTLS1: ([ThTLS]+[FrTLS]+[SaTLS]+[SuTLS]+[MoTLS]+[TuTLS]+[WeTLS])

The return I get with the total is: “2100111†which are the numerals I
entered individually for each day of the week.

I hope you can help me understand what’s going on with this. I’m stumped!

Thank you.

--
Nona

Use the Nz function to return a 0 (zero) if the field is null (left blank).
Nz([YourField], 0)
--
KARL DEWEY
Build a little - Test a little

tI hope I'm not coming to the well too often, but I have another, different
question.

In a query for a form, I have fields that total hours assigned for each day
of the week. The total results, calculated in the query, are then displayed
on the form and also on a report.

However, the query won't calculate unless a numberal has been entered for
each day. In other words, if there are no assigned hours for a day, it cannot
be left blank or it won't calculate the total. This is in spite of the fact
that the default for each day's hours has been set at zero.

Is there a solution for this? Do I use the If Null function for each day's
hours?

I'd appreciate your advice.

 

[Nona]

I checked the table, and they are all set as single numbers, so that's not
the problem, altho that's the way it's behaving.

Any other ideas as to what is causing this? Does it have anything to do with
using the Nx function? I want to keep the Nz function, but it wasn't a
problem until I added those in the query. I just don't know how to fix the
problem. It's totally screwed up now.

Ann









--
Nona

I sounds like your fields are not numbers but text. Open the table in design
view and check the properties of the fields. Change datatype to number -
single.

BTW you should not use a separate field for each entry but a new record like
this --
Employee WorkDate Hours
Bill Jones 1/2/2009 4.5
Bill Jones 1/3/2009 6.7
Fred Rex 1/2/2009 7.9
Fred Rex 1/3/2009 8.0
--
KARL DEWEY
Build a little - Test a little

I apologize for those multiple entries. After I sent the first one, I got an
error message that there was a network problem and I should try again. I did
and each time got an error message. Imagine my surprise when I saw I was
hounding the system! So sorry!
--
Nona

Thanks, Karl, I appreciate your help. I’ve added the Nz function to most of
the fields and can see that it does indeed populate the fields.

However, there is a new problem. It still doesn’t total correctly, but not
for the earlier reason. It’s almost as though the new figures are not
considered numbers. The properties won’t allow me to format the control, but
it does allow decimals. That doesn’t really help because when I run the
query, the return is a list of the same numerals I had entered (not totaled).

Here is one of the fields with the Nz function added: ThTLS: Nz([ThHrsTLS],0)
Here is the total field (adding entries for each day of the week):
TotTLS1: ([ThTLS]+[FrTLS]+[SaTLS]+[SuTLS]+[MoTLS]+[TuTLS]+[WeTLS])

The return I get with the total is: “2100111†which are the numerals I
entered individually for each day of the week.

I hope you can help me understand what’s going on with this. I’m stumped!

Thank you.

--
Nona


:

Use the Nz function to return a 0 (zero) if the field is null (left blank).
Nz([YourField], 0)
--
KARL DEWEY
Build a little - Test a little


:

tI hope I'm not coming to the well too often, but I have another, different
question.

In a query for a form, I have fields that total hours assigned for each day
of the week. The total results, calculated in the query, are then displayed
on the form and also on a report.

However, the query won't calculate unless a numberal has been entered for
each day. In other words, if there are no assigned hours for a day, it cannot
be left blank or it won't calculate the total. This is in spite of the fact
that the default for each day's hours has been set at zero.

Is there a solution for this? Do I use the If Null function for each day's
hours?

I'd appreciate your advice.

 

[Nona]

Thanks, Karl, I appreciate your help. I’ve added the Nz function to most of
the fields and can see that it does indeed populate the fields.

However, there is a new problem. It still doesn’t total correctly, but not
for the earlier reason. It’s almost as though the new figures are not
considered numbers. The properties won’t allow me to format the control, but
it does allow decimals. That doesn’t really help because when I run the
query, the return is a list of the same numerals I had entered (not totaled).

Here is one of the fields with the Nz function added: ThTLS: Nz([ThHrsTLS],0)
Here is the total field (adding entries for each day of the week):
TotTLS1: ([ThTLS]+[FrTLS]+[SaTLS]+[SuTLS]+[MoTLS]+[TuTLS]+[WeTLS])

The return I get with the total is: “2100111†which are the numerals I
entered individually for each day of the week.

I hope you can help me understand what’s going on with this. I’m stumped!

Thank you.

--
Nona

Use the Nz function to return a 0 (zero) if the field is null (left blank).
Nz([YourField], 0)
--
KARL DEWEY
Build a little - Test a little

tI hope I'm not coming to the well too often, but I have another, different
question.

In a query for a form, I have fields that total hours assigned for each day
of the week. The total results, calculated in the query, are then displayed
on the form and also on a report.

However, the query won't calculate unless a numberal has been entered for
each day. In other words, if there are no assigned hours for a day, it cannot
be left blank or it won't calculate the total. This is in spite of the fact
that the default for each day's hours has been set at zero.

Is there a solution for this? Do I use the If Null function for each day's
hours?

I'd appreciate your advice.

 

[David W. Fenton]

Here is one of the fields with the Nz function added: ThTLS:
Nz([ThHrsTLS],0) Here is the total field (adding entries for each
day of the week): TotTLS1:
([ThTLS]+[FrTLS]+[SaTLS]+[SuTLS]+[MoTLS]+[TuTLS]+[WeTLS])

The return I get with the total is: ƒ o2100111ƒ which are the
numerals I entered individually for each day of the week.

The problem is that Nz() sometimes doesn't retain the original data
type, so you may end up with strings out of Nz() instead of numeric
values (though I wouldn't expect that to happen since you say in a
later post that the underlying fields are singles).

Try this:

Val(Nz([ThHrsTLS],0))

for your aliased fields and see if that helps.

 

[John W. Vinson]

Here is one of the fields with the Nz function added: ThTLS: Nz([ThHrsTLS],0)
Here is the total field (adding entries for each day of the week):
TotTLS1: ([ThTLS]+[FrTLS]+[SaTLS]+[SuTLS]+[MoTLS]+[TuTLS]+[WeTLS])

Rather than a calculated field for the ThTLS - and then summing all the
calculated fields - just do the sum once:

TotTLS1: (NZ([ThHrsTLS]) + NZ([FrHrsTLS]) + NZ([SaHrsTLS]) +NZ([SuHrsTLS]) ...

The 0 is the default and is optional.

 

[austris]

I also had once a similar problem and the David's suggestion:
Val(Nz...
did the trick.

 

[Nona]

Thank you all very much! I used John's suggestion and it worked beautifully.

I appreciate all the help!

 

[James A. Fortune]

TotTLS1: (NZ([ThHrsTLS]) + NZ([FrHrsTLS]) + NZ([SaHrsTLS]) +NZ([SuHrsTLS]) ...

The 0 is the default and is optional.

I don't trust the default in queries.

In:

http://groups.google.com/group/comp.databases.ms-access/msg/ccba9695cc7b470c

I said:

David makes an excellent distinction about Nz acting differently based
on whether it is used in VBA or in SQL.

....

I've had situations where I've had to use Nz(This, 0) instead of
Nz(This) in queries, wondering why Nz(This) didn't return 0 when 'This'
was Null. Now I know why.

The entire thread:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/466fbbaaf7723fff

James A. Fortune
(e-mail address removed)

 

[John W. Vinson]

TotTLS1: (NZ([ThHrsTLS]) + NZ([FrHrsTLS]) + NZ([SaHrsTLS]) +NZ([SuHrsTLS]) ...

The 0 is the default and is optional.

I don't trust the default in queries.

In:

http://groups.google.com/group/comp.databases.ms-access/msg/ccba9695cc7b470c

I said:

David makes an excellent distinction about Nz acting differently based
on whether it is used in VBA or in SQL.

...

I've had situations where I've had to use Nz(This, 0) instead of
Nz(This) in queries, wondering why Nz(This) didn't return 0 when 'This'
was Null. Now I know why.

The entire thread:

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/466fbbaaf7723fff

James A. Fortune
(e-mail address removed)

Thanks, James... wasn't aware of this nasty little "gotcha"!