Prev: Custom toolbar icon prints form instead of report
Next: run-time error 2501
From: Bon43 on 23 Aug 2006 17:09
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

From: Bill on 23 Aug 2006 18:47
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



"Bon43" <Bon43(a)discussions.microsoft.com> wrote in message
news:9DBCFF6D-0472-48C9-9807-ED88EB831600(a)microsoft.com...
>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
>


From: Bill on 23 Aug 2006 18:47
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



"Bon43" <Bon43(a)discussions.microsoft.com> wrote in message
news:9DBCFF6D-0472-48C9-9807-ED88EB831600(a)microsoft.com...
>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
>


From: Bill on 23 Aug 2006 20:24
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





"Bill" <stanton(a)jps.net> wrote in message
news:7W4Hg.9720$Qf.3741(a)newsread2.news.pas.earthlink.net...
> 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
>
>
>
> "Bon43" <Bon43(a)discussions.microsoft.com> wrote in message
> news:9DBCFF6D-0472-48C9-9807-ED88EB831600(a)microsoft.com...
>>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
>>
>
>


From: Bill on 23 Aug 2006 20:41
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



"Bill" <stanton(a)jps.net> wrote in message
news:3l6Hg.835$bM.51(a)newsread4.news.pas.earthlink.net...
> 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
>
>
>
>
>
> "Bill" <stanton(a)jps.net> wrote in message
> news:7W4Hg.9720$Qf.3741(a)newsread2.news.pas.earthlink.net...
>> 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
>>
>>
>>
>> "Bon43" <Bon43(a)discussions.microsoft.com> wrote in message
>> news:9DBCFF6D-0472-48C9-9807-ED88EB831600(a)microsoft.com...
>>>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
>>>
>>
>>
>
>


 |  Next  |  Last
Pages: 1 2
Prev: Custom toolbar icon prints form instead of report
Next: run-time error 2501