basic maths with time

[merlin777]

I've tried to provide a duration for an appointment on a form by subtracting an start time from an end time e.g.
appstartid = 13:15
appendid = 16:30

appduration: tblAppointment.[appendid]-[appstartid] returns either 00:00 if formatted as time and 13 if formatted as a general number.

Both appstartid and appendid display correctly as the times above on the form.



Can anyone spot where I'm going wrong, please?

[June7]

The default unit is DAY when directly subtracting two date/time values. For other units must use DateDiff() function.

Suggest you get familiar with all the date manipulation functions.

[merlin777]

that's good advice.

i've tried the datediff function:

dur: DateDiff("n",[appstartid],[appendid])

(i don't know why it adds in the square brackets)

11:45 to 12:15 is returning 2880 in general number format and 00:00 in short time.

[June7]

It adds the brackets because Access presumes the references are valid db entities, like a field or textbox, used in the expression.

Using a date format on elapsed time doesn't work.

My calc with those times returns 30.

Those are date/time type fields?

[merlin777]

datediff is supposed to work with mins to ("n")?
how would you format elapsed time? In general number format i get 2880 in my example above which I can't match to anything
What did you use to get 30?
i;ve worked out where the figure 13 is coming from. The table i;ve set up to list appointment times is in 15 min intervals and access is returning the number of 15 min blocks -so i'm just x by 15 to get what i need.


still not sure why datediff isn't working though - it's supposed to be the 'proper way' to do this?

[June7]

I tested DateDiff with literal values:

DateDiff("n", "11:45", "12:15")

Elapsed time is just a number, like:

30 (for minutes)

or

DateDiff("h", "7:45", "12:15")

4.5 (for decimal hours)

If you want to show as hours:minutes (4:30) that requires code to build a text string, because 4:30 would be text, not time. This is a useless structure if you want to sum the elapsed calcs.

Displaying elapsed time as HH:MM has been topic of numerous threads.

[merlin777]

Thanks for this. I changed to general number and it seems i need to divide the result by 96 to get the number of minutes. I have no idea why it's 96 -thats not a multiple of 60 etc?

[June7]

I don't know why your calc is doing that. The "n" parameter should result in total minutes, 30 for the example. It works for me.

Would have to review your data.

[merlin777]

I don't know why your calc is doing that. The "n" parameter should result in total minutes, 30 for the example. It works for me.

Would have to review your data.

The original appointment times are in their own table and in the form HH:MM. Data type is time/date. They are 15 mins apart e.g.:
08:00
08:15
08:45 etc

This links to my main table using fields called appstartid and appendid which are number fields.

The problem is in a query I am writing which dispalys an appointment duration in a field called dur using this:

dur: (DateDiff("n",[appstartid],[appendid]))/96

the 96 is a fiddle factor that gives me the right answer but I don't know why.

I have also tried a field called appduration using:

appduration: 15*([tblAppointment].[appendid]-[appstartid])

This appears to just calculate the number of 15 min slots between start and end because the appointment times in the example above returns 2 so I've just been multiplying it by 15 to get the time period.

Is there anything else I can tell you?

[June7]

Sorry, doesn't help me understand that behavior. If you want to provide db for analysis, follow instructions at bottom of my post.

[merlin777]

hope I've done this ok well under 2meg, data is fictitious.

What I'm trying to achieve is an application (which doesn't look like access) for my physiotherapist and her special home support team (for early discharged stroke victims) to record their home visits and and at the end of the week generate a timetable to give to each patient. When they make appointments they need visibility of the other team members' appointments and the patients' appointments for that day. (This is the part I am having trouble with generating the appointment duration, tblappointmentquery3). There are around 20 patients and 10 therapists.

Currently it takes a couple of hours because they put their appointments in a shared outlook calendar and then generate timetables manually.

Please be gentle with me - I've only been learning access a couple of weeks. My plan is to use this project to learn how to use access and then scrap it and do it again from scratch using best practice and making sure my learning mistakes have all gone.clare211.zip

[June7]

Because the field names have ID as component I suspected they were not date/time so I did ask earlier if the fields were date/time type. The answer is - No, they are not, they are number type. You are saving the ID value from tblTimes. This means the DateDiff calc is using the ID, not the times. As example look at record in query, 13:15 is the 23rd period and 16:30 is the 36th period. Missing record for ID 2 in tblTimes so these periods are off by 1.

15*(36 - 23) = 195

So the DateDiff() function is using 36 and 23 as date/time values which are 2/4/1900 and 1/22/1900.

You have used Lookups in table - I NEVER do that. http://access.mvps.org/access/lookupfields.htm

Your options:

1. change the fields to date/time type and save the time values, not ID

2. include tblTimes in the query twice (1 each join to appstartid and appendid) and use the apptimes field for calcs

3. continue as you are

[merlin777]

Thanks for taking time to do that. I found out the hard way why lookups in table are bad bad bad. That's how I ended up with id numbers instead of lookups for the start and end time - now I have to replace the others!

I understand now that the data type is of the field you are working with, not the original data (as in tblTimes). That helps me a lot.

I won't use option 1 because I want my user to pick specified times from a list. I'd like to use option 2 if I can get my head around it because it looks like best practice but I'll fall back on 3 if needed.

Many, many, thanks.

[June7]

You can use option 1 and still provide the users with a fixed list to choose from. Just use the ID field for sort criteria in combobox RowSource.

I would agree with reasoning for option 2 if the descriptive info were long text but these short time values would be a lot simpler to work with. Saving the time would mean no need to include tblTimes in queries to retrieve time values.

[merlin777]

what do you mean by 'saving the time'? If you mean putting the appointment time list in the query, isn't that kind of duplication frowned upon?