Calculating Time FIELDS

[MarionD]

Hi all, I hope someone can help me here!

I have the following fields in a table:
Appointment% =100% (standard number)
Appointment hours =39:00 (Date/Time field)??

WorkTime =32:00 hours (date/time field)??
PrepTime=7:00 hours (date/Time field)??

Now in my form I would like to enter 39:00 hours automatically in the Appointment Hours, if 100 is entered in the Appoinrment% (if 50% then 19:50, if 75% then 29:15)

Is it correct to define these fields as date/time fields?
Thanks
Marion

 

[plog]

Wow, there's a lot wrong all over the place in what you posted. Let's work in order in which they appear:

Appointment% is a bad field name because it uses a non-alphanumeric character (%), I'd rename it.

100% is not a standard number. You've displayed it as a percentage. The standard number equivalent is 1.

39:00 in Appointment hours (and all your other times) is not a Date/Time field. You need to find out what all your fields really are and post back.

Now in my form I would like to enter 39:00 hours automatically in the Appointment Hours, if 100 is entered in the Appoinrment% (if 50% then 19:50, if 75% then 29:15)

What? What does it mean that you 'enter 39:00 automatically', either you enter it, or its automatic.

Additionally, I have no idea what fields you are talking about or what you want to occur.

19:50 is not 50% of 39 hours, 19:30 is.

If one of those fields is a calculation of the other 2, then you don't need that calculated field in your table, you just calculate it with the data you have whenever you need it.

Lastly, to answer you're question with the best guess I can make: no, probabally not. I bet those Date/Time fields should be numeric.

 

[jdraw]

I would not name a field with a special character "%" nor embedded spaces. I recommend only alphas, numerics and underscore.

As for your Date/Time field question- Do you ever use the fields as Dates? Ever calculate DateDiff values?

It seems the fields could just be numerics.
I say this because you representation 100% 39:00 means 39 hours 0 seconds,
but 50% would be 19:30 not 19:50.
So from what you are showing, I think it is best represented as
100% = 39.0 hours and 50% = 19.5 hours, and I would not use these as Date/Time data types.

 

[MarionD]

OK sorry - I didn't explain that very well, did I!

I want to enter 100 in the Appointment (%) is not part of the field name - just put it there for clarity. Then the Appointment_Hrs should calculate 39:00. If I enter 75 then 75% of 39 Hrs etc. My problem is that I need to be specific in the minutes not as a numerical of an hour. Not 19,5 hrs but 19:30 Nineteen hrs and thirty minutes. 22,5%=8hrs and 46 minutes not 8,77Hrs.
Then I need to enter the worktime and preptime in hrs. eg. 32:00 hrs - then calculate what % this is of the appointment time. eg. 32:00 is 82.05% of 39:00 hrs.
If I have 8:46 (8hrs 46 min) in the appointment time and enter 1:30 (1hr 30 min) in the preptime I need to come up with an answer of 17,1%
Hope this makes more sense!
Sorry and thanks for the time (no pun intended)


Marion

 

[plog]

Ok, what specifically do you need help with?

I'd start with a function that takes in a number for your percentage (1-100) and and calculates the percent of 39 hours that is, it would then return a string in the format you want (HH:mm). This isn't too hard, you apply the percentage to 39, take the whole number divisor as the hours and multiply the decimal remainder by 60 to calculate minutes.

The other time calculations would work similarly.

 

[MarionD]

Hi there,
So in the table the field must be a numeric (double) field? So I store 39 or 19,50 etc. and just display it on the form as 19:30 in an unbound form?

 

[plog]

If the percentage is always calculated off of 39 hours, all you need to store in the table is the percentage. I'd use a double for that.

The form itself can be bound so that it updates the percentage, but the calculated hours would be an unbound control.

 

[MarionD]

I think I do need to store the working week (39 hrs) as I later need to subtract hrs. worked from this. I also need to split this time into 2 sections (actual work time and preparation time) and subtract worked hrs. from this to get a weekly overtime or - time report.

Getting more and more complicated. Thanks again
Marion

 

[RainLover]

Hi all, I hope someone can help me here!

I have the following fields in a table:
Appointment% =100% (standard number)
Appointment hours =39:00 (Date/Time field)??

WorkTime =32:00 hours (date/time field)??
PrepTime=7:00 hours (date/Time field)??

Now in my form I would like to enter 39:00 hours automatically in the Appointment Hours, if 100 is entered in the Appoinrment% (if 50% then 19:50, if 75% then 29:15)

Is it correct to define these fields as date/time fields?
Thanks
Marion

Marion

Did you test the Date/Time format to see if you could enter 39 hours.

Give it a try. It will help with some of your questions.