Joining date from one field with time from another field

[osi2301]

I have a rostering database that I need to join a time from one field to the date from another field.

Here is what I have. I have a form with drop down menus for various work shifts. Linked to the work shifts is the sign on and sign off time which doesn't change. I would like to join the sign on sign off times with the date of the assigned shift, which does change.

So if I'm assigning a work shift of "morning" in the drop down menu for the 15th of the month. I have a shift table that gives me the sign on and sign off times for each shift. When it finds the shift, it also gives me the sign on and sign off times. As an example, I need to have a way to enter the "Morning" shift that has a sign on time of 7:15 AM for the morning on the 25th of July 2005.

 

[TheAceMan1]

How are ya osi2301 . . . . .

Perhaps this ([blue]you[/blue] substitute proper names in [purple]purple[/purple]):

[blue]   = Format(Int(Me![purple][b]ShiftDate[/b][/purple]), "Short Date") & " " & _
     Format(Me![purple][b]SignOn[/b][/purple], "Medium Time") & " to " & _
     Format(Me![purple][b]SignOff[/b][/purple], "Medium Time")[/blue]

If not, post the format you wish to see . . .

See Ya! . . . . . .

 

[osi2301]

that works to put the time and date into the same field. However, my next step requires that the time difference between signoff and signon of the next day be calculated for fatigue management purposes. The combined date time fields created above doesn't allow for that.

 

[TheAceMan1]

[blue]However, [purple]my next step[/purple] requires that the time difference between signoff and signon of the next day be calculated . . .[/blue]

Have a look at the [blue]DateDiff Function![/blue]

See Ya! . . . . . .

 

[osi2301]

Here is what I have done: I've created an unbound field called "off1" and another unbound field called "on2".
"Off1" has this in the control source: =Format(Int([date1]),"Medium Date") & " " & Format([SignOff1],"Short Time").
"On2" has this in the control source: =Format(Int([date2]),"Medium Date") & " " & Format([SignOn2],"Short Time")
Both of these fields return the date and time. I have created another unbound field with this in the control source: =DateDiff([interval],[off1],[on2])this is the part that doesn't work. It returns ?Name error.

 

[TheAceMan1]

osi2301 . . . . .

and if you cut the spacing [blue]" "[/blue] from 5 to 1?

See Ya! . . . . . .

 

[osi2301]

sorry, makes no difference

 

[TheAceMan1]

osi2301 . . . . .

. . . and this:

[blue]=Format(Int(CDate([date1])),"Medium Date") & " " & Format(CDate([SignOff1]),"Short Time")

=Format(Int(CDate([date2])),"Medium Date") & " " & Format(CDate([SignOn2]),"Short Time")

=DateDiff("h",CDate([off1]),CDate([on2]))[/blue]

See Ya! . . . . . .

 

[osi2301]

I'm sorry for being so stupid but what is the "cdate

 

[TheAceMan1]

[blue]I'm sorry for being so stupid but what is the "cdate"[/blue]

Well . . . to me [blue]there's no such thing as sutpid[/blue] . . . just a need to know!

[blue][purple]CDate[/purple] is a conversion function that [purple]coerces[/purple] a valid Date expression into the [purple]Date DataType[/purple] (usuable by Access)![/blue]

Look for CDate in VBE help . . . . you'll fond many others listed as well!

See Ya! . . . . . .

 

[osi2301]

That works well. It gives me the information in whole hours between shift patterns. Just one problem. The field is not blank prior to selecting the shift patterns. The field displays a "#ERROR" message until both the first and second day shifts are selected. After that it displays the correct number of hours between those shifts.

The form is a continuous form where each line represents a selected employee's name. As soon as the first person is selected, the second line is displayed with the "#ERROR" message in the field where the hours between shifts will eventually be. Any ideas?

 

[PHV]

Have you tried to play with the IsNull or Nz functions ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[osi2301]

I've tried using the isnull but it treats the "#ERROR" message in the unbound control as entered data. It views the error message as valid data (not null).

Haven't used the nz function yet. However, if isnull views the error message as actual data (not isnull) then I'm fairly certain that the nz function will do the same.

 

[PHV]

Perhaps something like this:
=IIf(IsDate([off1]) And IsDate([on2]), DateDiff("h",CDate([off1]),CDate([on2])), "")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[osi2301]

YES YES, that worked great!!!!!!!