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