DateDiff

[Taff82]

Hi all,

Bit stuck with this one.

I have a table called appointments with the following fields
AppDate, AppTime, AppComments and these are also on my form called frmAppointments.

What I am trying to do is check how much time there is between the appointment that is currently open on my form and the next due appointment for that day. If there is no next due appointment then I'm trying to work out the difference in time between my currently selected appointment and the time 17:30:00.

Have tried the DateDiff function and have gotten it to work, however when there are no more scheduled appointments for that day I get completely lost.

Any help is greatly appreciated.

Thanks

Taff.

 

[PHV]

Have tried the DateDiff function and have gotten it to work
Can you please post the relevant code ?

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

 

[earthandfire]

Try combining DateDiff with IIf

Hope this helps

 

[Taff82]

Thanks both for replying.

The statement in my query is:

SELECT tblAppointments.AppDate, tblAppointments.AppTime, DateDiff("n",[Forms]![FrmAppointments]![AppTime],[AppTime]) AS TimeDifference
FROM tblAppointments
WHERE (((tblAppointments.AppDate)=[Forms]![FrmAppointments]![AppDate]) AND ((tblAppointments.AppTime)>[Forms]![FrmAppointments]![AppTime]));

However if no results are returned for my query this is where I get stuck.

Taff.

 

[PHV]

And how is this query used in your checking process ?

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

 

[Taff82]

It will be used to tell me what the maximum length of the appointment can be.

Taff.

 

[PHV]

Some thing like this in the AfterUpdate event procedure of the AppTime control ?
stCrit = "AppDate=#" & Format(Me!AppDate, "yyyy-mm-dd") & "# AND AppTime>#" & Format(Me!AppTime, "hh:nn:ss") & "#"
myTimediff = DateDiff("n", Me!AppTime, Nz(DLookUp("AppTime", "tblAppointments", stCrit), #17:30:00#))

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