can't change data type?

[merlin777]

I've backed myself into a corner and I'm having trouble getting out.

In my table I have two fields for time (a start time and end time). I used the time/date data type but when I came to use the lookup wizard to add a lookup of a list of time slots I got the error 'the lookup wizard doesn't apply to fields of this data type' so to get my lookup I changed the data type to 'number' and I was able to add the lookup successfully. I used a time format for the fields to display as time.

Now I've come to design a query which needs to work out the duration from start to finish. I used the query wizard and added a field 'appduration: [append]-[appstart]'. The result is not what I expected - an appointment from 13:45:00 to 16:30:00 gives an answer of '11' in general number format and '12:00AM' in medium time format and I'd hoped an answer of 2 and 3/4 hours.

I reasoned this might be because my original data type should have been time/date so I went to my table in design view and tried to change the data type and received the error 'can't change because it's part of a relationship'. Well, I removed the relationships using the relationship view days ago becaus they were getting in the way.

So, I guess my questions are:

• what have I done wrong?
• how do I put it right without losing data?
• how come you can't have a lookup with a time field?

 

[burrina]

Unsure of your field format and data types: But you can try this for appduration under format type. If you can post back with more details, maybe we can provide a better suggestion.
hh:nn:ss

 

[merlin777]

OK - I'm part of the way there. It seems I've used lookups in a table which is taboo (table-oo?) and so I've changed them to use id fields and after some adjustments eveything seems to behaving itself and I now have the short time format (thanks for that tip - needed hh:mm rather than hh:mm:ss and access converted it to short time).

However, my duration is still not displaying a valid time. If I subtract an earlier time from a later time should I expect a valid time as a result?

 

[gemma-the-husky]

time addition/subtracting will give you a result that is time modulo 24 hours. It probably will work ok, but may not, and you cannot exceed 24 hours.

19 hours + 15 hours is 10 hours (ie 1 day, 10 hours)
subtracting gives you similar issues.
7hrs less 3 hours, gives 4hours
but 3 hours less 7 hours might give 20hours (I think - although it might give 4 hours)

you need to convert the time into integer minutes, say, and work that way.

what subtraction are you doing that is not working correctly?

 

[merlin777]

I've tried to provide a duration for an appointment on a form by subtracting a 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.

I think i'm subtracting the right way around and as appointments are no more than an hour or two, mod 24 shouldn't be an issue.

 

[merlin777]

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.

 

[merlin777]

i;ve worked out where the figure 15 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 want i need.

still not sure why datediff isn't working though.

 

[burrina]

Me.appduration = [cappend] - [appstart]

Just use your format for the desired display results.

HTH

 

[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?

 

[burrina]

tblAppointment
cappend Date/Time
appstart Date/Time
Format: Medium Time
Input Mask: 99:00\ >LL;0;_

Why would you change to a general number when you want a time difference?

 

[gemma-the-husky]

not sure, but 96/60 may be something to do with decimal fractions of an hour. ie 96 is 1.6 times 60.

eg 15 minutes is 1/4 of an hour, and therefore would be 0.25 of an hour. It possibly depends exactly how your calculations are working.

 

[merlin777]

I was told that a duration didn't format correctly as time. When I did use time every result displayed as 00:00 (the value and the format).

appendid and appstartid are number data in my original table and they refer to a table of appointment times.

 

[gemma-the-husky]

without seeing what you have, it's difficult - but you can definitely manipulate your data to manage times.

Access date and times just have a few foibles though that you need to be aware of.

it's all just data. A "datetime" is just a double number

eg take a "date" of 12.1234

12 is twelve days
.1234 is 1234/10000 part of day

multiple it by 24, and then by 60 and you get the answer in minutes.

 

[merlin777]

Wow, what a pita. I'm sure they have a good reason for it but it doesn't make it very user-friendly!

Hmmn... 11:45 to 12:15 is returning 2880, *24*60 is 4147200. I'm not sure I understood you correctly?