Is it possible to do a If this and this then that?

[Talismanic]

I am stuck on the last step of a very complicated DB. I have a Wage Calc Form that is based on information that is entered from a Time Sheet form. The person who enters the time selects weather it is ST-Straight Time, OT-OverTime or DT-DoubleTime and what class the work is like Carpenter, Plasterer, etc...

In the Wage Calc Form there are DLookUps that go to the Rate Table for class and wage information, the tables has info similar to this:

Carpenter

Time = 22.77
Time&Half = 31.63
Double Time = 40.49

Plasterer

Time = 23.30
Time&Half = 32.36
Double Time = 41.52

This table is based on a set rate that comes from the State (prevailing wage) and varies depending on the county the work is done in. If you do the math you will see that the Time&Half rate does not = 1.5 of the time rate and that is my problem. Becuase if it did, the control would be very simple.

The Wage Calc Form needs to do something like this: If time is OT and Class isCarpenter then Rate= 31.63.

Is this possible with out VBA? If so, please point me in the right direction.

[This message has been edited by Talismanic (edited 06-09-2000).]

 

[RpbertS]

all right from what I understand this may help you out.

on your form you have at least 3 or more fields one which says "OT" or not and the second says the class adn teh third says the rate.

in one of the events maybe you'd use the "on Load Event"

put
If fieldnameforOT = "OT" and fieldnameforClass="Carpenter" then
fieldnameforrate="31.63"


the italic fileds have to be on one line,
if this is not what you need let me know I just looked quickly as Im at work

 

[Talismanic]

I think you have the idea, There are three choices for Time ST, OT and DT. And there are eight choices for Class and both choices are selected from a another form and are read only in the (this) Wage Calc Form.

This action has to take place in the details section of the form for every record in the details section.

Line 1 (Name)Bob (Hours)4 Hrs (TimeCode)OT (Class)Carpenter (Rate)31.63 (answer)=[Hours]*[Rate]
Line 2 (Name)Bob (Hours)4 Hrs (TimeCode)ST (Class)Carpenter (Rate)22.77 (answer)=[Hours]*[Rate]

IF (TimeCode)=OT and (Class)=Carpenter then (Rate)= 31.63

IF (TimeCode)=ST and (Class)=Carpenter then (Rate)= 22.37

 

[Talismanic]

The more I think about this the more complicated it gets. The problem is that the rate is not static it is coming from a table. So there has to be a rate look up for the then statement. I do have all the possible combinations listed at the bottom of the form (with DLookUps) so I do have that part done. Those fields are all named so maybe I need to do it like this. If [ST] and [Carpenter] then [field Value].

Now the problem is that there are 36 possibilites total and I don't think that can be done with just a Control Source or a macro.

 

[Pat Hartman]

If the rate table were normalized, you could do this without code.
The rate table should be:
County (part1 of primary key)
WorkClass (part2 of primary key)
RateClass (part3 of primary key)
Rate

If your rate table is structured as:
County (part1 of primary key)
WorkClass (part2 of primary key)
STRate
OTRate
DTRate

you are stuck with writing code.

A case statement might be easier:
Select Case RateClass
..Case "ST"
....PayAmt = STRate * Hours
..Case "OT"
....PayAmt = OTRate * Hours
..Case "DT"
....PayAmt = DTRate * Hours
End Select

 

[Talismanic]

Here is a screen shot of one record in the Wage Calc to get an idea of what I am talking about. The dark blue is the details and the black is header/footer.

All the data I need is right there in front of me I just cant figure out how to get it from the footer to a field in the details section based on multiple criteria.

I am beginning to think I am going to have to redesign the time sheet and have the person entering put in an extra two pieces of redundant info so that I can do a DLookup from the form. One thing for sure this has made my hair hurt!

Image

[This message has been edited by Talismanic (edited 06-09-2000).]