Calculating between 2 date and time differences in a query

[chaan758]

I am having trouble in calculating the time difference in hours between 2 times and dates in a query. I have these times and dates in two different tables:

Table 1:
Injury Date : 16-May-07
Injury Time: 5:30:00 AM

Table 2:
CC Date: 17-May-07
CC Time: 7:00:00 PM

2) Is there a way to write the expression? And be accurate to say 2 decimal places? And displayed as hours only? If not, hours and minutes are quite alright too.

I was told to combine Date and Time in the actual tables, but I did not want to display it like that in my form for data entry.
Is it possible to write an expression using concatenation in the query? I guess there are many ways to approach this.

I would greatly appreciate any help.

 

[pbaldy]

IMHO, whoever told you to combine them was right. They can be presented any way you want. In any case, you can add them together inside a DateDiff function to get the difference in whatever interval you wish.

 

[chaan758]

HI Paul,

Thanks for that, but I have no idea how to write the expression, as I am just an amateur at Access. How would I do a DateDiff function to get the difference in whatever interval I wanted?.

Is there a way that I could combine the fields of Date and Time in the table, even though I have a separate data field in the form for Date and Table. I just dont like the way it looks on the form, having the 2 fields combined.

Cheers!

 

[pbaldy]

Start by looking in Help at DateDiff, which will give you the structure and options available. Post back if you get stuck.

I have older databases where I stored date and time separately, and newer ones where I store them together. If you're going to be doing comparisons and such, it's easier to have them together. It's a little more work on forms, but I put textboxes for each on the form for the user, and combine them when storing to the table. For editing, I pull the date and time out of the combined value and put the values into the separate textboxes for the user.