If then query - BRAIN FRIED...PLEASE HELP!

[sparklegrrl]

My database here at the office is FINALLY up and running minus a few little kinks to be worked out.

The first one has to do with time cards. Our employees may work 14 hours in one day and it is broken out on the time cards and by job as 8 regular hours and 6 overtime hours however, at the end of the week if this employee took off the next day and he doesn't have 40 hours then he is paid regular time. Basically time on jobs is figured on a daily basis BUT employee paychecks are figured on weekly time.

So, I have my report totalling the regular hours and overtime hours for the week. I then have an unbound text box that calculates Text19 (regular hours) and Text21 (overtime) hours then subtracts 40 and displays the accurate overtime number.

I have 2 questions.

a) I need the overtime box described above to only display a number above zero. Right now it calculates it and displays -2.0 or whatever.

b) I need to add Text19 (regular time) and Text21 (overtime) and if it is over 40 hours, it needs to display 40 however, if it is under 40 hours, it needs to display that number instead.

Thanks for any help!

 

[dcx693]

Use some =IIf () functions as the controlsources for those textboxes. Something like: =IIf(RegularHours-40<0,0,RegularHours-40)

 

[sparklegrrl]

Thanks! It works! What about the overtime? If it is less than 0, it needs to say 0 and not -1.687?

 

[dcx693]

Just use a similar formula. I'm sure you can figure it out!

 

[sparklegrrl]

GOT IT BUT....if the value is 0 it's not showing anything and messing up other things calculating off that field, for example total hours.

If have...

=IIf([Text97]<0,0,[Text97])

How do I make it display 0.00 if the value is less than zero? Now it is just blank.

 

[dcx693]

You could set the Format property of the control to a number format with two fixed decimal places, but if it's messing up other calculations, it sounds like more than a formatting problem to me. Are other fields calculating off it? And what do those fields show?

 

[sparklegrrl]

Yup, I tried that but it's still not showing up. It is included in the total hours calculation and the overtime pay is calculating off of it which also calculates into the net check.

If they have 48 hours for example, it's working great but they guy who only has 10 hours for the week is showing 10 regular but the ot field is blank now which is better than -30...hehehehe

But making the other fields not calculate since it isn't showing 0.00.

 

[dcx693]

Wait a sec. I did not read your post carefully. What exactly is this expression calculating: =IIf([Text97]<0,0,[Text97]) ??

What is in Text97? You need something like: =IIf([Text97]-40<0,0,[Text97]-40)

 

[sparklegrrl]

WOHOO! GOT IT! THANKS SO MUCH for your help!!!!!

Do you have time for one more question? I need it to total Text97 for the whole report. I have a box on the bottom of the report that says =Sum([Text97]) but now it when I view it, it asks me what Text97 is?

Wierd!

 

[dcx693]

I think you need to sum over whatever field is feeding into Text97, but I haven't written a report in a while.

 

[sparklegrrl]

Ya know what? I got home and realized I forgot all about vacation/holiday pay which would mean it NOT split those amounts. How do you put 2 parameters like that in?

In other words, if vacation pay (Text29) and Holiday pay (text31) are <0 then [text91]>40,40,[Text91]

But if vacation pay (text29) and holiday pay (text31) are >0 then it needs to display text 19

YIPES! Basically the same thing for overtime.