formula help

[Guest]

Hello, I need help with a formula that I have been trying to get with no
luck. I need to get a percent for delay. Oh and this data isnt stored in a
tbl its just usless data. Ok I have 7 text boxes named Delay1 Delay2 ect. and
they are all in short time format. I need a total from these boxes giving me
my total delay in a text box called text200 (lets say 8:59). Then I have a
text box called text201 which I manulay enter how many hours was worked (lets
say 39.5). Last I have a text box that will give me my percent. This text box
is called text203 and I need it to divide text200 by text201 wich would give
me (22.74%) I have tried it and its giving me goofy numbers? can someone
please help...

Thanks,
Chad

 

[Joseph Meehan]

Hello, I need help with a formula that I have been trying to get with
no luck. I need to get a percent for delay. Oh and this data isnt
stored in a tbl its just usless data. Ok I have 7 text boxes named
Delay1 Delay2 ect. and they are all in short time format. I need a
total from these boxes giving me my total delay in a text box called
text200 (lets say 8:59). Then I have a text box called text201 which
I manulay enter how many hours was worked (lets say 39.5). Last I
have a text box that will give me my percent. This text box is called
text203 and I need it to divide text200 by text201 wich would give me
(22.74%) I have tried it and its giving me goofy numbers? can someone
please help...

Thanks,
Chad

I am going to take a wild guess. I suspect you will need to use the
formula used to compute text203 rather than text203 in the final formula.

 

[Guest]

the final formula is text203

I am going to take a wild guess. I suspect you will need to use the
formula used to compute text203 rather than text203 in the final formula.

 

[Joseph Meehan]

the final formula is text203

I guess I am a little confused with what you are doing. I do suggest
that if you are using formulas to compute numbers displayed on a form that
you don't want to reference the results of those formulas to compute an
additional result, but rather use the same formulas as _part of_ a new
formula for the final result

That is

A 1+1=2 ("2" displayed in box A)
B 2+ 3=5 ("5" displayed in box B)
C A+B= 7 Do not use rather use:
C (1+1)+(2+3)=[7 Displayed in box C]

 

[Guest]

text box 200 (text200) would use this control source: (example 8hr 59min)
=[Delay1]=[Delay2]+[Delay3]+[Delay4]+[Delay5]+[Delay6]+[Delay7]

text box 201 (text201) would be entered manualy (Example 39.5 hours)

text box 203 (text203) would use this control source:
=[text200]/[text201] (example total 22.74%)

my trouble is its giving me a weird % like 1003.54% and Im also having
trouble with text200 because sometimes there isnt a value in one of the delay
text boxes im puling the numbers from and text200 is blank untill i enter
0:00 into the empty text box. I hope this clarifys things up!!



:

the final formula is text203

I guess I am a little confused with what you are doing. I do suggest
that if you are using formulas to compute numbers displayed on a form that
you don't want to reference the results of those formulas to compute an
additional result, but rather use the same formulas as _part of_ a new
formula for the final result

That is

A 1+1=2 ("2" displayed in box A)
B 2+ 3=5 ("5" displayed in box B)
C A+B= 7 Do not use rather use:
C (1+1)+(2+3)=[7 Displayed in box C]

 

[Douglas J. Steele]

To solve the problem with text200, use

=Nz([Delay1],0)+Nz([Delay2],0)+Nz([Delay3],0)+Nz([Delay4],0)+Nz([Delay5],0)+Nz([Delay6],0)+Nz([Delay7],0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

text box 200 (text200) would use this control source: (example 8hr 59min)
=[Delay1]=[Delay2]+[Delay3]+[Delay4]+[Delay5]+[Delay6]+[Delay7]

text box 201 (text201) would be entered manualy (Example 39.5 hours)

text box 203 (text203) would use this control source:
=[text200]/[text201] (example total 22.74%)

my trouble is its giving me a weird % like 1003.54% and Im also having
trouble with text200 because sometimes there isnt a value in one of the
delay
text boxes im puling the numbers from and text200 is blank untill i enter
0:00 into the empty text box. I hope this clarifys things up!!



:

the final formula is text203

I guess I am a little confused with what you are doing. I do suggest
that if you are using formulas to compute numbers displayed on a form
that
you don't want to reference the results of those formulas to compute an
additional result, but rather use the same formulas as _part of_ a new
formula for the final result

That is

A 1+1=2 ("2" displayed in box A)
B 2+ 3=5 ("5" displayed in box B)
C A+B= 7 Do not use rather use:
C (1+1)+(2+3)=[7 Displayed in box C]

:

oxicottin wrote:
Hello, I need help with a formula that I have been trying to get
with no luck. I need to get a percent for delay. Oh and this data
isnt stored in a tbl its just usless data. Ok I have 7 text boxes
named Delay1 Delay2 ect. and they are all in short time format. I
need a total from these boxes giving me my total delay in a text
box called text200 (lets say 8:59). Then I have a text box called
text201 which I manulay enter how many hours was worked (lets say
39.5). Last I have a text box that will give me my percent. This
text box is called text203 and I need it to divide text200 by
text201 wich would give me (22.74%) I have tried it and its giving
me goofy numbers? can someone please help...

Thanks,
Chad

I am going to take a wild guess. I suspect you will need to use
the formula used to compute text203 rather than text203 in the final
formula.

 

[Guest]

Ok!! now im totaling time so would I use this to get a whole number?

=Nz([Delay1],0)+Nz([Delay2],0)+Nz([Delay3],0)+Nz([Delay4],0)+Nz([Delay5],0)+Nz([Delay6],0)+Nz([Delay7],0)*24))

To solve the problem with text200, use

=Nz([Delay1],0)+Nz([Delay2],0)+Nz([Delay3],0)+Nz([Delay4],0)+Nz([Delay5],0)+Nz([Delay6],0)+Nz([Delay7],0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

text box 200 (text200) would use this control source: (example 8hr 59min)
=[Delay1]=[Delay2]+[Delay3]+[Delay4]+[Delay5]+[Delay6]+[Delay7]

text box 201 (text201) would be entered manualy (Example 39.5 hours)

text box 203 (text203) would use this control source:
=[text200]/[text201] (example total 22.74%)

my trouble is its giving me a weird % like 1003.54% and Im also having
trouble with text200 because sometimes there isnt a value in one of the
delay
text boxes im puling the numbers from and text200 is blank untill i enter
0:00 into the empty text box. I hope this clarifys things up!!



:

oxicottin wrote:
the final formula is text203

I guess I am a little confused with what you are doing. I do suggest
that if you are using formulas to compute numbers displayed on a form
that
you don't want to reference the results of those formulas to compute an
additional result, but rather use the same formulas as _part of_ a new
formula for the final result

That is

A 1+1=2 ("2" displayed in box A)
B 2+ 3=5 ("5" displayed in box B)
C A+B= 7 Do not use rather use:
C (1+1)+(2+3)=[7 Displayed in box C]



:

oxicottin wrote:
Hello, I need help with a formula that I have been trying to get
with no luck. I need to get a percent for delay. Oh and this data
isnt stored in a tbl its just usless data. Ok I have 7 text boxes
named Delay1 Delay2 ect. and they are all in short time format. I
need a total from these boxes giving me my total delay in a text
box called text200 (lets say 8:59). Then I have a text box called
text201 which I manulay enter how many hours was worked (lets say
39.5). Last I have a text box that will give me my percent. This
text box is called text203 and I need it to divide text200 by
text201 wich would give me (22.74%) I have tried it and its giving
me goofy numbers? can someone please help...

Thanks,
Chad

I am going to take a wild guess. I suspect you will need to use
the formula used to compute text203 rather than text203 in the final
formula.

 

[Douglas J. Steele]

Time is simply a number. To avoid problems with having a specific box Null,
you use the Nz function to convert it to a zero. I'm not sure what you mean
by "get a whole number".

Access stores Date/Time values as 8 byte floating point numbers where the
integer portion is the date relative to 30 Dec, 1899, and the decimal
portion represents the time as a fraction of a day (in other words, time
ALWAYS has a date associated with it, so it's not really appropriate to use
a Date field to store a time duration).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok!! now im totaling time so would I use this to get a whole number?

=Nz([Delay1],0)+Nz([Delay2],0)+Nz([Delay3],0)+Nz([Delay4],0)+Nz([Delay5],0)+Nz([Delay6],0)+Nz([Delay7],0)*24))

To solve the problem with text200, use

=Nz([Delay1],0)+Nz([Delay2],0)+Nz([Delay3],0)+Nz([Delay4],0)+Nz([Delay5],0)+Nz([Delay6],0)+Nz([Delay7],0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

text box 200 (text200) would use this control source: (example 8hr
59min)
=[Delay1]=[Delay2]+[Delay3]+[Delay4]+[Delay5]+[Delay6]+[Delay7]

text box 201 (text201) would be entered manualy (Example 39.5 hours)

text box 203 (text203) would use this control source:
=[text200]/[text201] (example total 22.74%)

my trouble is its giving me a weird % like 1003.54% and Im also having
trouble with text200 because sometimes there isnt a value in one of the
delay
text boxes im puling the numbers from and text200 is blank untill i
enter
0:00 into the empty text box. I hope this clarifys things up!!



:


oxicottin wrote:
the final formula is text203

I guess I am a little confused with what you are doing. I do
suggest
that if you are using formulas to compute numbers displayed on a form
that
you don't want to reference the results of those formulas to compute
an
additional result, but rather use the same formulas as _part of_ a new
formula for the final result

That is

A 1+1=2 ("2" displayed in box A)
B 2+ 3=5 ("5" displayed in box B)
C A+B= 7 Do not use rather use:
C (1+1)+(2+3)=[7 Displayed in box C]



:

oxicottin wrote:
Hello, I need help with a formula that I have been trying to get
with no luck. I need to get a percent for delay. Oh and this data
isnt stored in a tbl its just usless data. Ok I have 7 text boxes
named Delay1 Delay2 ect. and they are all in short time format. I
need a total from these boxes giving me my total delay in a text
box called text200 (lets say 8:59). Then I have a text box called
text201 which I manulay enter how many hours was worked (lets say
39.5). Last I have a text box that will give me my percent. This
text box is called text203 and I need it to divide text200 by
text201 wich would give me (22.74%) I have tried it and its giving
me goofy numbers? can someone please help...

Thanks,
Chad

I am going to take a wild guess. I suspect you will need to
use
the formula used to compute text203 rather than text203 in the
final
formula.

 

[Guest]

ok the first part is working but my percent isnt coming out right here is
what I have:
text200 =10:14 (format is short time decimal is auto this is working
correctly now)
text201 =40.00 (format is standard with 2 decimal and this is entered
maunully)
text203 is giving me the percent of 0.010659722 which it should be 25.6

What am I doing wrong?

Thanks,
Chad

Time is simply a number. To avoid problems with having a specific box Null,
you use the Nz function to convert it to a zero. I'm not sure what you mean
by "get a whole number".

Access stores Date/Time values as 8 byte floating point numbers where the
integer portion is the date relative to 30 Dec, 1899, and the decimal
portion represents the time as a fraction of a day (in other words, time
ALWAYS has a date associated with it, so it's not really appropriate to use
a Date field to store a time duration).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok!! now im totaling time so would I use this to get a whole number?

=Nz([Delay1],0)+Nz([Delay2],0)+Nz([Delay3],0)+Nz([Delay4],0)+Nz([Delay5],0)+Nz([Delay6],0)+Nz([Delay7],0)*24))

To solve the problem with text200, use

=Nz([Delay1],0)+Nz([Delay2],0)+Nz([Delay3],0)+Nz([Delay4],0)+Nz([Delay5],0)+Nz([Delay6],0)+Nz([Delay7],0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


text box 200 (text200) would use this control source: (example 8hr
59min)
=[Delay1]=[Delay2]+[Delay3]+[Delay4]+[Delay5]+[Delay6]+[Delay7]

text box 201 (text201) would be entered manualy (Example 39.5 hours)

text box 203 (text203) would use this control source:
=[text200]/[text201] (example total 22.74%)

my trouble is its giving me a weird % like 1003.54% and Im also having
trouble with text200 because sometimes there isnt a value in one of the
delay
text boxes im puling the numbers from and text200 is blank untill i
enter
0:00 into the empty text box. I hope this clarifys things up!!



:


oxicottin wrote:
the final formula is text203

I guess I am a little confused with what you are doing. I do
suggest
that if you are using formulas to compute numbers displayed on a form
that
you don't want to reference the results of those formulas to compute
an
additional result, but rather use the same formulas as _part of_ a new
formula for the final result

That is

A 1+1=2 ("2" displayed in box A)
B 2+ 3=5 ("5" displayed in box B)
C A+B= 7 Do not use rather use:
C (1+1)+(2+3)=[7 Displayed in box C]



:

oxicottin wrote:
Hello, I need help with a formula that I have been trying to get
with no luck. I need to get a percent for delay. Oh and this data
isnt stored in a tbl its just usless data. Ok I have 7 text boxes
named Delay1 Delay2 ect. and they are all in short time format. I
need a total from these boxes giving me my total delay in a text
box called text200 (lets say 8:59). Then I have a text box called
text201 which I manulay enter how many hours was worked (lets say
39.5). Last I have a text box that will give me my percent. This
text box is called text203 and I need it to divide text200 by
text201 wich would give me (22.74%) I have tried it and its giving
me goofy numbers? can someone please help...

Thanks,
Chad

I am going to take a wild guess. I suspect you will need to
use
the formula used to compute text203 rather than text203 in the
final
formula.

 

[Douglas J. Steele]

The problem is that to Access, a time of 10:14 is stored as .42638889
(remember, I said that time is stored as a fraction of a day). Rather than
dividing by 40, you need to divide by 1.666667 (40/24):

?#10:14#/40
1.06597222222222E-02
?#10:14#/(40/24)
0.255833333333333

As I said before, it isn't really appropriate to Date fields to work with
durations.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

ok the first part is working but my percent isnt coming out right here is
what I have:
text200 =10:14 (format is short time decimal is auto this is working
correctly now)
text201 =40.00 (format is standard with 2 decimal and this is entered
maunully)
text203 is giving me the percent of 0.010659722 which it should be 25.6

What am I doing wrong?

Thanks,
Chad

Time is simply a number. To avoid problems with having a specific box
Null,
you use the Nz function to convert it to a zero. I'm not sure what you
mean
by "get a whole number".

Access stores Date/Time values as 8 byte floating point numbers where the
integer portion is the date relative to 30 Dec, 1899, and the decimal
portion represents the time as a fraction of a day (in other words, time
ALWAYS has a date associated with it, so it's not really appropriate to
use
a Date field to store a time duration).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok!! now im totaling time so would I use this to get a whole number?

=Nz([Delay1],0)+Nz([Delay2],0)+Nz([Delay3],0)+Nz([Delay4],0)+Nz([Delay5],0)+Nz([Delay6],0)+Nz([Delay7],0)*24))



:

To solve the problem with text200, use

=Nz([Delay1],0)+Nz([Delay2],0)+Nz([Delay3],0)+Nz([Delay4],0)+Nz([Delay5],0)+Nz([Delay6],0)+Nz([Delay7],0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


text box 200 (text200) would use this control source: (example 8hr
59min)
=[Delay1]=[Delay2]+[Delay3]+[Delay4]+[Delay5]+[Delay6]+[Delay7]

text box 201 (text201) would be entered manualy (Example 39.5 hours)

text box 203 (text203) would use this control source:
=[text200]/[text201] (example total 22.74%)

my trouble is its giving me a weird % like 1003.54% and Im also
having
trouble with text200 because sometimes there isnt a value in one of
the
delay
text boxes im puling the numbers from and text200 is blank untill i
enter
0:00 into the empty text box. I hope this clarifys things up!!



:


oxicottin wrote:
the final formula is text203

I guess I am a little confused with what you are doing. I do
suggest
that if you are using formulas to compute numbers displayed on a
form
that
you don't want to reference the results of those formulas to
compute
an
additional result, but rather use the same formulas as _part of_ a
new
formula for the final result

That is

A 1+1=2 ("2" displayed in box A)
B 2+ 3=5 ("5" displayed in box B)
C A+B= 7 Do not use rather use:
C (1+1)+(2+3)=[7 Displayed in box C]



:

oxicottin wrote:
Hello, I need help with a formula that I have been trying to
get
with no luck. I need to get a percent for delay. Oh and this
data
isnt stored in a tbl its just usless data. Ok I have 7 text
boxes
named Delay1 Delay2 ect. and they are all in short time format.
I
need a total from these boxes giving me my total delay in a
text
box called text200 (lets say 8:59). Then I have a text box
called
text201 which I manulay enter how many hours was worked (lets
say
39.5). Last I have a text box that will give me my percent.
This
text box is called text203 and I need it to divide text200 by
text201 wich would give me (22.74%) I have tried it and its
giving
me goofy numbers? can someone please help...

Thanks,
Chad

I am going to take a wild guess. I suspect you will need to
use
the formula used to compute text203 rather than text203 in the
final
formula.

 

[Guest]

I fixed it! I just created a hidden text box and divided by 24. Thanks for
the help!!!

The problem is that to Access, a time of 10:14 is stored as .42638889
(remember, I said that time is stored as a fraction of a day). Rather than
dividing by 40, you need to divide by 1.666667 (40/24):

?#10:14#/40
1.06597222222222E-02
?#10:14#/(40/24)
0.255833333333333

As I said before, it isn't really appropriate to Date fields to work with
durations.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

ok the first part is working but my percent isnt coming out right here is
what I have:
text200 =10:14 (format is short time decimal is auto this is working
correctly now)
text201 =40.00 (format is standard with 2 decimal and this is entered
maunully)
text203 is giving me the percent of 0.010659722 which it should be 25.6

What am I doing wrong?

Thanks,
Chad

Time is simply a number. To avoid problems with having a specific box
Null,
you use the Nz function to convert it to a zero. I'm not sure what you
mean
by "get a whole number".

Access stores Date/Time values as 8 byte floating point numbers where the
integer portion is the date relative to 30 Dec, 1899, and the decimal
portion represents the time as a fraction of a day (in other words, time
ALWAYS has a date associated with it, so it's not really appropriate to
use
a Date field to store a time duration).

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Ok!! now im totaling time so would I use this to get a whole number?

=Nz([Delay1],0)+Nz([Delay2],0)+Nz([Delay3],0)+Nz([Delay4],0)+Nz([Delay5],0)+Nz([Delay6],0)+Nz([Delay7],0)*24))



:

To solve the problem with text200, use

=Nz([Delay1],0)+Nz([Delay2],0)+Nz([Delay3],0)+Nz([Delay4],0)+Nz([Delay5],0)+Nz([Delay6],0)+Nz([Delay7],0)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


text box 200 (text200) would use this control source: (example 8hr
59min)
=[Delay1]=[Delay2]+[Delay3]+[Delay4]+[Delay5]+[Delay6]+[Delay7]

text box 201 (text201) would be entered manualy (Example 39.5 hours)

text box 203 (text203) would use this control source:
=[text200]/[text201] (example total 22.74%)

my trouble is its giving me a weird % like 1003.54% and Im also
having
trouble with text200 because sometimes there isnt a value in one of
the
delay
text boxes im puling the numbers from and text200 is blank untill i
enter
0:00 into the empty text box. I hope this clarifys things up!!



:


oxicottin wrote:
the final formula is text203

I guess I am a little confused with what you are doing. I do
suggest
that if you are using formulas to compute numbers displayed on a
form
that
you don't want to reference the results of those formulas to
compute
an
additional result, but rather use the same formulas as _part of_ a
new
formula for the final result

That is

A 1+1=2 ("2" displayed in box A)
B 2+ 3=5 ("5" displayed in box B)
C A+B= 7 Do not use rather use:
C (1+1)+(2+3)=[7 Displayed in box C]



:

oxicottin wrote:
Hello, I need help with a formula that I have been trying to
get
with no luck. I need to get a percent for delay. Oh and this
data
isnt stored in a tbl its just usless data. Ok I have 7 text
boxes
named Delay1 Delay2 ect. and they are all in short time format.
I
need a total from these boxes giving me my total delay in a
text
box called text200 (lets say 8:59). Then I have a text box
called
text201 which I manulay enter how many hours was worked (lets
say
39.5). Last I have a text box that will give me my percent.
This
text box is called text203 and I need it to divide text200 by
text201 wich would give me (22.74%) I have tried it and its
giving
me goofy numbers? can someone please help...

Thanks,
Chad

I am going to take a wild guess. I suspect you will need to
use
the formula used to compute text203 rather than text203 in the
final
formula.