Divide by zero

  • Thread starter Thread starter Henro
  • Start date Start date
H

Henro

I do in a query an calculation on 2 fields:

prcDEAL: = [TotaalDEAL]\[Totale werkdag]

The latter sometimes can be zero. In this case I get an #Error. How can I
convert this #Error to zero so I can do calculations on this field? I tried
NZ and IIF but they don't seem to work (so I suppose #Error is not a Null
value?)

Any ideas?

TIA Henro
 
I understand the intention of the code and it's a good idea but now, as soon
as [Totale Werkdag] <=13:00 I get the #error again. If i work more than
thirteen hours per day things work out fine. I hope you understand I don't
intend to work that much :p Maybe I should have told you that the fields
[Totale Werkdag] en [TotaalDEAL] are DATE/Time fields with hours put in with
the Short Time notation. Is that what is causing the havoc? Suggestions?

Totale werkdag: (NZ([Eind dag])-NZ([Start Dag]))+(NZ([Avond Eind])-NZ([Avond
Start]))-NZ([Lunch])-NZ([Aftrekbaar])

TotaalDEAL:
NZ([InstBSItijd])+NZ([Overige])+NZ([InstONtijd])+NZ([InstONEtijd])+NZ([SolBS
Itijd])+NZ([SolONtijd])+NZ([SolONEtijd])+NZ([ProjectDEAL])+NZ([Advies Sales
Telefonisch Consult])+NZ([Urenregistratie]![ReistijdDEALGerelateerd])

These are all hours and I need the percentage TotaalDEAL in [Totale Werkdag]

TIA Henro

Wayne Morgan said:
prcDeal: IIf([Totale werkdag]=0, 0, [TotaalDEAL]\[Totale werkdag])

--
Wayne Morgan
Microsoft Access MVP


Henro said:
I do in a query an calculation on 2 fields:

prcDEAL: = [TotaalDEAL]\[Totale werkdag]

The latter sometimes can be zero. In this case I get an #Error. How can I
convert this #Error to zero so I can do calculations on this field? I tried
NZ and IIF but they don't seem to work (so I suppose #Error is not a Null
value?)

Any ideas?

TIA Henro
Click to expand...
Click to expand...
 
Excuse me, that fault is my own!
Any one can explain to me why I get that error?


Henro

Henro said:
I understand the intention of the code and it's a good idea but now, as soon
as [Totale Werkdag] <=13:00 I get the #error again. If i work more than
thirteen hours per day things work out fine. I hope you understand I don't
intend to work that much :p Maybe I should have told you that the fields
[Totale Werkdag] en [TotaalDEAL] are DATE/Time fields with hours put in with
the Short Time notation. Is that what is causing the havoc? Suggestions?

Totale werkdag: (NZ([Eind dag])-NZ([Start Dag]))+(NZ([Avond Eind])-NZ([Avond
Start]))-NZ([Lunch])-NZ([Aftrekbaar])

TotaalDEAL:
Click to expand...
NZ([InstBSItijd])+NZ([Overige])+NZ([InstONtijd])+NZ([InstONEtijd])+NZ([SolBS
Itijd])+NZ([SolONtijd])+NZ([SolONEtijd])+NZ([ProjectDEAL])+NZ([Advies
Click to expand...
Sales
Telefonisch Consult])+NZ([Urenregistratie]![ReistijdDEALGerelateerd])

These are all hours and I need the percentage TotaalDEAL in [Totale Werkdag]

TIA Henro

Wayne Morgan said:
prcDeal: IIf([Totale werkdag]=0, 0, [TotaalDEAL]\[Totale werkdag])

--
Wayne Morgan
Microsoft Access MVP


Henro said:
I do in a query an calculation on 2 fields:

prcDEAL: = [TotaalDEAL]\[Totale werkdag]

The latter sometimes can be zero. In this case I get an #Error. How
Click to expand...
Click to expand...
can
I
Click to expand...
Click to expand...
 
I suspect that what you are running into now has to do with the way Access
handles dates and times. They are stored as numbers where the whole part of
the number is the day and the decimal portion is the time of day. You are
doing integer division (\) instead of (/). 12 hours would be half a day or
0.5. I suspect you are getting caught in a rounding problem where this is
rounding to zero. If Access does the rounding it will usually round .5 to
the nearest even number, in this case zero.

Where the problem comes in is that elapsed times are NOT times. A time is a
certain point during the day, such as 13:00 (or 1:00 pm in 12 hour format).
I would recommend, if you need hours and minutes, that you do all of your
calculations in minutes then change them back to hours and minutes when you
are done. The elapsed time in minutes will be an integer value, not a
date/time value.

--
Wayne Morgan
Microsoft Access MVP


Henro said:
I understand the intention of the code and it's a good idea but now, as soon
as [Totale Werkdag] <=13:00 I get the #error again. If i work more than
thirteen hours per day things work out fine. I hope you understand I don't
intend to work that much :p Maybe I should have told you that the fields
[Totale Werkdag] en [TotaalDEAL] are DATE/Time fields with hours put in with
the Short Time notation. Is that what is causing the havoc? Suggestions?

Totale werkdag: (NZ([Eind dag])-NZ([Start Dag]))+(NZ([Avond Eind])-NZ([Avond
Start]))-NZ([Lunch])-NZ([Aftrekbaar])

TotaalDEAL:
Click to expand...
NZ([InstBSItijd])+NZ([Overige])+NZ([InstONtijd])+NZ([InstONEtijd])+NZ([SolBS
Itijd])+NZ([SolONtijd])+NZ([SolONEtijd])+NZ([ProjectDEAL])+NZ([Advies
Click to expand...
Sales
Telefonisch Consult])+NZ([Urenregistratie]![ReistijdDEALGerelateerd])

These are all hours and I need the percentage TotaalDEAL in [Totale Werkdag]

TIA Henro

Wayne Morgan said:
prcDeal: IIf([Totale werkdag]=0, 0, [TotaalDEAL]\[Totale werkdag])

--
Wayne Morgan
Microsoft Access MVP


Henro said:
I do in a query an calculation on 2 fields:

prcDEAL: = [TotaalDEAL]\[Totale werkdag]

The latter sometimes can be zero. In this case I get an #Error. How
Click to expand...
Click to expand...
can
I
Click to expand...
Click to expand...
 
Do you read my mind? :-p I have found the solution to the problem you are
describing but now.

I ryun into the problem you describe. If I add all of my hours it counts
24hourand start over again so if I worked a total of 25 hours it gives me 1.
My boss will think that I am sitting at home doing nothing :-p

Can I put down everything in the table as minutes and still use the
'standard' time format in the forms?

We are supposed to keep track of our working hours in this thing for a
Management Information System so the totals are very important!

I'll see if I can find out how to translate everything to minutes, in the
meanwhile I would be very grateful if you would have some more suggestions.
Don't feel obliged, I am already grateful because of your suggestions so
far!

Grtz Henro


Wayne Morgan said:
I suspect that what you are running into now has to do with the way Access
handles dates and times. They are stored as numbers where the whole part of
the number is the day and the decimal portion is the time of day. You are
doing integer division (\) instead of (/). 12 hours would be half a day or
0.5. I suspect you are getting caught in a rounding problem where this is
rounding to zero. If Access does the rounding it will usually round .5 to
the nearest even number, in this case zero.

Where the problem comes in is that elapsed times are NOT times. A time is a
certain point during the day, such as 13:00 (or 1:00 pm in 12 hour format).
I would recommend, if you need hours and minutes, that you do all of your
calculations in minutes then change them back to hours and minutes when you
are done. The elapsed time in minutes will be an integer value, not a
date/time value.

--
Wayne Morgan
Microsoft Access MVP


Henro said:
I understand the intention of the code and it's a good idea but now, as soon
as [Totale Werkdag] <=13:00 I get the #error again. If i work more than
thirteen hours per day things work out fine. I hope you understand I don't
intend to work that much :p Maybe I should have told you that the fields
[Totale Werkdag] en [TotaalDEAL] are DATE/Time fields with hours put in with
the Short Time notation. Is that what is causing the havoc? Suggestions?

Totale werkdag: (NZ([Eind dag])-NZ([Start Dag]))+(NZ([Avond Eind])-NZ([Avond
Start]))-NZ([Lunch])-NZ([Aftrekbaar])

TotaalDEAL:
Click to expand...
Click to expand...
NZ([InstBSItijd])+NZ([Overige])+NZ([InstONtijd])+NZ([InstONEtijd])+NZ([SolBS
Itijd])+NZ([SolONtijd])+NZ([SolONEtijd])+NZ([ProjectDEAL])+NZ([Advies
Click to expand...
Sales
Telefonisch Consult])+NZ([Urenregistratie]![ReistijdDEALGerelateerd])

These are all hours and I need the percentage TotaalDEAL in [Totale Werkdag]

TIA Henro

Wayne Morgan said:
prcDeal: IIf([Totale werkdag]=0, 0, [TotaalDEAL]\[Totale werkdag])

--
Wayne Morgan
Microsoft Access MVP


I do in a query an calculation on 2 fields:

prcDEAL: = [TotaalDEAL]\[Totale werkdag]

The latter sometimes can be zero. In this case I get an #Error. How
Click to expand...
can
I
convert this #Error to zero so I can do calculations on this field? I
tried
NZ and IIF but they don't seem to work (so I suppose #Error is not a Null
value?)

Any ideas?

TIA Henro
Click to expand...
Click to expand...
Click to expand...
 
2 hours = 120 minutes, I believe the following will do what you want as long
as you don't go over 23 hours and 59 minutes.

Format(120/24/60, "Short Time") will return 02:00. The 24 and 60 come from
the number of hours in a day and the number of minutes in an hour.

If you may get results >= 24 hours, then you'll need to do some extra work.
If your value is in minutes, this should work:

Using 620 minutes as an example:
(620\24) & ":" & 620 Mod 24 will yield 25:20

--
Wayne Morgan
Microsoft Access MVP


Henro said:
Do you read my mind? :-p I have found the solution to the problem you are
describing but now.

I ryun into the problem you describe. If I add all of my hours it counts
24hourand start over again so if I worked a total of 25 hours it gives me 1.
My boss will think that I am sitting at home doing nothing :-p

Can I put down everything in the table as minutes and still use the
'standard' time format in the forms?

We are supposed to keep track of our working hours in this thing for a
Management Information System so the totals are very important!

I'll see if I can find out how to translate everything to minutes, in the
meanwhile I would be very grateful if you would have some more suggestions.
Don't feel obliged, I am already grateful because of your suggestions so
far!

Grtz Henro


Wayne Morgan said:
I suspect that what you are running into now has to do with the way Access
handles dates and times. They are stored as numbers where the whole part of
the number is the day and the decimal portion is the time of day. You are
doing integer division (\) instead of (/). 12 hours would be half a day or
0.5. I suspect you are getting caught in a rounding problem where this is
rounding to zero. If Access does the rounding it will usually round .5 to
the nearest even number, in this case zero.

Where the problem comes in is that elapsed times are NOT times. A time
Click to expand...
is
a
certain point during the day, such as 13:00 (or 1:00 pm in 12 hour format).
I would recommend, if you need hours and minutes, that you do all of your
calculations in minutes then change them back to hours and minutes when you
are done. The elapsed time in minutes will be an integer value, not a
date/time value.

--
Wayne Morgan
Microsoft Access MVP


Henro said:
I understand the intention of the code and it's a good idea but now,
Click to expand...
as
soon
as [Totale Werkdag] <=13:00 I get the #error again. If i work more than
thirteen hours per day things work out fine. I hope you understand I don't
intend to work that much :p Maybe I should have told you that the fields
[Totale Werkdag] en [TotaalDEAL] are DATE/Time fields with hours put
Click to expand...
in
with
the Short Time notation. Is that what is causing the havoc? Suggestions?

Totale werkdag: (NZ([Eind dag])-NZ([Start Dag]))+(NZ([Avond Eind])-NZ([Avond
Start]))-NZ([Lunch])-NZ([Aftrekbaar])

TotaalDEAL:
Click to expand...
Click to expand...
Click to expand...
NZ([InstBSItijd])+NZ([Overige])+NZ([InstONtijd])+NZ([InstONEtijd])+NZ([SolBS
Itijd])+NZ([SolONtijd])+NZ([SolONEtijd])+NZ([ProjectDEAL])+NZ([Advies
Click to expand...
Sales
Telefonisch Consult])+NZ([Urenregistratie]![ReistijdDEALGerelateerd])

These are all hours and I need the percentage TotaalDEAL in [Totale Werkdag]

TIA Henro

"Wayne Morgan" <[email protected]> schreef in
bericht prcDeal: IIf([Totale werkdag]=0, 0, [TotaalDEAL]\[Totale werkdag])

--
Wayne Morgan
Microsoft Access MVP


I do in a query an calculation on 2 fields:

prcDEAL: = [TotaalDEAL]\[Totale werkdag]

The latter sometimes can be zero. In this case I get an #Error.
Click to expand...
How
can
I
convert this #Error to zero so I can do calculations on this
Click to expand...
Click to expand...
field?
I
Click to expand...
Click to expand...
 
Hours*60=Minutes

If your hours are all whole hours or decimal hours (i.e. 1.5 hours for 1
hour and 30 minutes) you can do this in hours and skip the change to
minutes. If, however, you're going to be using various minutes as well, its
easier to do it all in minutes then convert back.
 
I see that in the code it mentions a specific table and a specific field.
Can I f.e. use it in a query as an expression?

Henro
 
I'm sorry, I don't understand what you are asking? What is "f.e."?

Can you use fields in your equations? Yes. Can you use the ones in the
article? Only if they exist in your database.
 
Wayne Morgan said:
I'm sorry, I don't understand what you are asking? What is "f.e."?

Can you use fields in your equations? Yes. Can you use the ones in the
article? Only if they exist in your database.
Click to expand...
f.e. = E.G. ?
 
Ok, thanks. I couldn't get Access out of my head and so the only thing that
came to mind was "front-end", but that didn't make sense.
 
Back
Top