Divide by zero

[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

 

[Wayne Morgan]

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

 

[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 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

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 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

 

[Henro]

Excuse me, that fault is my own!
Any one can explain to me why I get that error?


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 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

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

can

I

 

[Wayne Morgan]

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

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 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

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

can

I

 

[Henro]

Do you read my mind? 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

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

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

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 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

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

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

 

[Wayne Morgan]

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

Do you read my mind? 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

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

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

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 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" <[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.

How
can

I
convert this #Error to zero so I can do calculations on this

field?

I

 

[Wayne Morgan]

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.

 

[Henro]

http://support.microsoft.com/default.aspx?scid=kb;EN-US;210604

That was what I am looking for

 

[Wayne Morgan]

Good article, I'll have to remember that one. Thanks.

 

[Henro]

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

 

[Wayne Morgan]

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.

 

[Mike Painter]

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.

f.e. = E.G. ?

 

[Dirk Goldgar]

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

"For example", I'd guess.

 

[Henro]

yep. it's For Example. Sorry. I am Dutch.

Henro

"For example", I'd guess.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Wayne Morgan]

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.

 

[Henro]

Like me, goto bed with it and awake with it whether you want or not?