Datediff using combos

[Ade F]

I'm working on a rough sketching out of a timesheets DB and have the following problem. It seems that I'm getting wacky result from the datediff function.

I have the following structure

Main timesheet table

Sub timesheet

Time In table

Time Out table

I would just like fot the datediff to calculate the difference between the first combo box( TIME_ID ) and the second (TIME_ID_OUT). It seems that if I change the table format and have the input fields directly within the timesheet sub table (making the user manually type in 00:00 etc) then datediff works. However if I link to the timein table via a combo the calculations go ape.

Here is a link to the picture for more clarity (this is by no way finished it is an outright lash job at the mo)

Any ideas from the pro's.

Cheers

Ade

 

[neileg]

I don't understand why you are selecting times from a combo. Surely you need the user to input the actual time and store this in your underlying table?

 

[Ade F]

I did it so the user could just select a time rather than type it in. The time entry is stored in the Timesheet sub table but provided from another time related table. Yes I could go for the tried and tested (myself) method and it works a treat. I just wished for the user to be able to select a time rather than typing one (plus it would cut down on fat finger syndrome). P.s before anyone asks the text boxes scattered around are for pay rate dependant on day and time.

Here are the current relationships

Some fields will be removed for completetion it is just easier for the maths if I have fixed figures for reference purposes.

Cheers Neil

Ade

I don't understand why you are selecting times from a combo. Surely you need the user to input the actual time and store this in your underlying table?

 

[Ade F]

Yes the combo is bound to a date/field row source shows a date/time but it does give me wacky figures when datadiff executes. I have changed it now anyway to a small button type interface creating a 00:00 to 23:30 style (in the main form) that applies the time to time in time out fields. I have ditched the combo idea although it would have been brill to get it working. I really dont like users typing unless they have to as I said previously fat fingers syndrome strike without warning

Cheers anyway pat although If you have lashed something similar together then I would very much appreciate a small sample DB only showing this working combo trick.

Ade

DateDiff() is a Date function. As such, it ONLY works on fields defined as date/time datatype. If the combo is bound to a date/time field and the combo's RowSource shows a date/time field, you should be able to use the combo.

 

[Ade F]

By the way Pat do you know of a way of testing for nulls in continuous forms? I have been searching the forums to no avail. I would like to make sure that a user has definitely put data within all required fields. yes I have ref.integrity and required fields set but is there a coding way?

If not is it possible to display a custom msgbox in place of the default system error message displayed for a field not having data input (the standard ref integ message).

Any ideas?

DateDiff() is a Date function. As such, it ONLY works on fields defined as date/time datatype. If the combo is bound to a date/time field and the combo's RowSource shows a date/time field, you should be able to use the combo.

 

[Ade F]

Thanks very much Pat I have these in place now and all working very well I now have to put my thinking cap on and approach the overtime situation. For example

Mon - Friday 8 - 4.30 flat rate
Before 8 time and a half after 4.30 time and a half
Sat am time and a half
Sat pm double time
Sun double time

I'm not sure what the best way to approach this working is The datediff will take me so far but the workings if it is before 8 or after 4.30 is a little tricky. If anyone has come across this sceanrio then I would be grateful of some enlightenment.

Everybody on this forum is most helpful and kind and I shall always return in order to view or comment on posts.

Cheers

Ade

You can put your final edits in the BeforeUpdate event of the form. It doesn't matter whether the form is in single, continuous, or datasheet view. The BeforeUpdate event fires for EVERY updated record immediately before the record is saved. You can trap the null value errors in this event (or in the form's error event - I'm not sure which, you'll need to test both) and display your own message.

 

[Ade F]

Thanks Unc I will have a look into it. I have been bashing away with this overtime situation all afternoon and after numerous if's and a bit of jiggery pokery it looks as if I have worked out how to calculate overtime over 8 hours and by day. And all from my head (No copying and modifying)... amazing what a little C can do for you.

Cheers

Adrian

>>>I just wished for the user to be able to select a time rather than typing one (plus it would cut down on fat finger syndrome)<<<

Have you thought about using the date time picker control? this is an extra control available with some versions of access / V. B. if it's not part of your version I believe you can buy it.


Otherwise there's this form available that you can download from my website, it's not elegant, but it will allow your users to select a time by pressing buttons.

http://www.hotware.co.uk/tonysweb/ms_access.htm and click on:- Time Clock (Enter "Time" in to a field)