Help with equation

[Guest]

I am trying to get this formula to work in my report. I have three number
fields:
1. Setuphr (already in hrs/minutes, i.e. 5.5)
2. Cyclemin (in minutes
3. Qty (quantity)

I want to be able to convert setuphr to minutes, then add to the results to
the cyclemin/qty field (which would be minutes) then divide by 60 to get
total hours.
This is what I wrote:
=Sum([set up hours]*60)+(sum[cycle mins]*[qty]))/60 (This results in a sum
of 516.6666…, should be 40.666) What am I doing wrong? I can get this
formula to work in Excel. Please help.

Thanks

 

[Bill]

Are Setuphr, Cyclemin and Qty Dim'd as Single?
I.e., single precision floating point numbers?

You'll have an easier time dealing with decimal numbers
if you use data-type "Single". (In your case, I don't see
any need to use "double" precision floating point.)

Bill

 

[Bill]

Ah! You caught me thinking Excel as well. We don't have
a Sum function in VBA. There is a DSum function that
adds up the given field amongst multiple records, but it
doesn't appear that is what you're doing?

The way you've written your equation suggests that
"set up hours" is an array or string of values. Likewise
with "cycle mins". If that's NOT THE CASE, then
you simply want:

([set up hours]* 60 + [cycle mins]*[qty])/60

Where each of the variables are "Single". (Variant can
sometimes cause ambiguities, so "Single" is the a
better choice when dealing with decimal numbers.)

Bill

Are Setuphr, Cyclemin and Qty Dim'd as Single?
I.e., single precision floating point numbers?

You'll have an easier time dealing with decimal numbers
if you use data-type "Single". (In your case, I don't see
any need to use "double" precision floating point.)

Bill

I am trying to get this formula to work in my report. I have three number
fields:
1. Setuphr (already in hrs/minutes, i.e. 5.5)
2. Cyclemin (in minutes
3. Qty (quantity)

I want to be able to convert setuphr to minutes, then add to the results
to
the cyclemin/qty field (which would be minutes) then divide by 60 to get
total hours.
This is what I wrote:
=Sum([set up hours]*60)+(sum[cycle mins]*[qty]))/60 (This results in a
sum
of 516.6666., should be 40.666) What am I doing wrong? I can get this
formula to work in Excel. Please help.

Thanks

 

[Bill]

Hummm! I might be wrong about the lack of a SUM
function. The HELP reference attempts to take me to
a restricted site, or at least that's the message.

The inference is that there's a SUM function that will
add the values in a string or the numeric elements of
an array. (I would have expected as much)

Access 2000 and/or 2003 HELP simply didn't yield
sufficient information.

Hopefully, one of the MVP's will pick up on your post
and we'll both learn something.

Bill

Ah! You caught me thinking Excel as well. We don't have
a Sum function in VBA. There is a DSum function that
adds up the given field amongst multiple records, but it
doesn't appear that is what you're doing?

The way you've written your equation suggests that
"set up hours" is an array or string of values. Likewise
with "cycle mins". If that's NOT THE CASE, then
you simply want:

([set up hours]* 60 + [cycle mins]*[qty])/60

Where each of the variables are "Single". (Variant can
sometimes cause ambiguities, so "Single" is the a
better choice when dealing with decimal numbers.)

Bill

Are Setuphr, Cyclemin and Qty Dim'd as Single?
I.e., single precision floating point numbers?

You'll have an easier time dealing with decimal numbers
if you use data-type "Single". (In your case, I don't see
any need to use "double" precision floating point.)

Bill

I am trying to get this formula to work in my report. I have three number
fields:
1. Setuphr (already in hrs/minutes, i.e. 5.5)
2. Cyclemin (in minutes
3. Qty (quantity)

I want to be able to convert setuphr to minutes, then add to the results
to
the cyclemin/qty field (which would be minutes) then divide by 60 to get
total hours.
This is what I wrote:
=Sum([set up hours]*60)+(sum[cycle mins]*[qty]))/60 (This results in a
sum
of 516.6666., should be 40.666) What am I doing wrong? I can get this
formula to work in Excel. Please help.

Thanks

 

[Guest]

Hi Bill: I took your suggestion and used this formula and it works!
=([set up hours]*60+[cycle mins]*[qty])/60 . I am pretty much a newbie, so
not sure wha tyou are taling about with arrays, etc. These fields are number
fields, double. I changed to single to see if that will make a difference.
Thanks for everything. (By the way - I learned today from you that I don't
need the word sum, that is interesting. Too bad Access creators couldn't be
more consistent with Excel formulas, but all is good.

Hummm! I might be wrong about the lack of a SUM
function. The HELP reference attempts to take me to
a restricted site, or at least that's the message.

The inference is that there's a SUM function that will
add the values in a string or the numeric elements of
an array. (I would have expected as much)

Access 2000 and/or 2003 HELP simply didn't yield
sufficient information.

Hopefully, one of the MVP's will pick up on your post
and we'll both learn something.

Bill

Ah! You caught me thinking Excel as well. We don't have
a Sum function in VBA. There is a DSum function that
adds up the given field amongst multiple records, but it
doesn't appear that is what you're doing?

The way you've written your equation suggests that
"set up hours" is an array or string of values. Likewise
with "cycle mins". If that's NOT THE CASE, then
you simply want:

([set up hours]* 60 + [cycle mins]*[qty])/60

Where each of the variables are "Single". (Variant can
sometimes cause ambiguities, so "Single" is the a
better choice when dealing with decimal numbers.)

Bill

Are Setuphr, Cyclemin and Qty Dim'd as Single?
I.e., single precision floating point numbers?

You'll have an easier time dealing with decimal numbers
if you use data-type "Single". (In your case, I don't see
any need to use "double" precision floating point.)

Bill



I am trying to get this formula to work in my report. I have three number
fields:
1. Setuphr (already in hrs/minutes, i.e. 5.5)
2. Cyclemin (in minutes
3. Qty (quantity)

I want to be able to convert setuphr to minutes, then add to the results
to
the cyclemin/qty field (which would be minutes) then divide by 60 to get
total hours.
This is what I wrote:
=Sum([set up hours]*60)+(sum[cycle mins]*[qty]))/60 (This results in a
sum
of 516.6666., should be 40.666) What am I doing wrong? I can get this
formula to work in Excel. Please help.

Thanks

 

[Bill]

Unless there's some special reference library in which
VBA finds a "Sum" function, there is none that is native
to VBA. I wrote some simple code this morning looking
for a possible explanation, but didn't find any.

Anyway, I'm glad you were able to accomplish your task.

By the way, it would be fairly simple to write your own
public function to perform summations.

Conceptually, Access grew out of the earlier days of
relational databases and SQL on mainframe computers,
Excel came later from a more commercial oriented group
of folks.....I doubt that they ever met each other until they
were forced upon one another. Intellectual pride and an
attitude of "not invented here" lie beneath much of what
went wrong in the evolution of software engineering.

Bill

Hi Bill: I took your suggestion and used this formula and it works!
=([set up hours]*60+[cycle mins]*[qty])/60 . I am pretty much a newbie, so
not sure wha tyou are taling about with arrays, etc. These fields are
number
fields, double. I changed to single to see if that will make a difference.
Thanks for everything. (By the way - I learned today from you that I
don't
need the word sum, that is interesting. Too bad Access creators couldn't
be
more consistent with Excel formulas, but all is good.

Hummm! I might be wrong about the lack of a SUM
function. The HELP reference attempts to take me to
a restricted site, or at least that's the message.

The inference is that there's a SUM function that will
add the values in a string or the numeric elements of
an array. (I would have expected as much)

Access 2000 and/or 2003 HELP simply didn't yield
sufficient information.

Hopefully, one of the MVP's will pick up on your post
and we'll both learn something.

Bill

Ah! You caught me thinking Excel as well. We don't have
a Sum function in VBA. There is a DSum function that
adds up the given field amongst multiple records, but it
doesn't appear that is what you're doing?

The way you've written your equation suggests that
"set up hours" is an array or string of values. Likewise
with "cycle mins". If that's NOT THE CASE, then
you simply want:

([set up hours]* 60 + [cycle mins]*[qty])/60

Where each of the variables are "Single". (Variant can
sometimes cause ambiguities, so "Single" is the a
better choice when dealing with decimal numbers.)

Bill





Are Setuphr, Cyclemin and Qty Dim'd as Single?
I.e., single precision floating point numbers?

You'll have an easier time dealing with decimal numbers
if you use data-type "Single". (In your case, I don't see
any need to use "double" precision floating point.)

Bill



I am trying to get this formula to work in my report. I have three
number
fields:
1. Setuphr (already in hrs/minutes, i.e. 5.5)
2. Cyclemin (in minutes
3. Qty (quantity)

I want to be able to convert setuphr to minutes, then add to the
results
to
the cyclemin/qty field (which would be minutes) then divide by 60 to
get
total hours.
This is what I wrote:
=Sum([set up hours]*60)+(sum[cycle mins]*[qty]))/60 (This results in
a
sum
of 516.6666., should be 40.666) What am I doing wrong? I can get
this
formula to work in Excel. Please help.

Thanks