Subtracting a query result from a constant.

[quattda]

I have a beginning date/time field and an end date/time field in long form in
one table. For example 5/24/2007 11:35 AM and 5/24/2007 12: 40 PM. I created
a subtraction query expression to find the difference (i.e., 1:05:00 AM)
shown in short time form. I would like to subtract this value from a 1 hour
15 minute constant value. However, when I set this up I get an invalid synax
error message when I enter a 1:15 value (I assume because of the colon) and
an incorrect value (i.e., 12/31/1899 4:55:00 AM displayed as 4:55 AM) when I
use 1.25. How do I create this equation to get the correct value (in this
case 5 minues)?

 

[Steve]

1:05AM is a time value while 1 Hour and 15 minutes is a numerical value. You
can not subtract a time value from a numerical value. To start, you need to
convert 1 hour and 15 minutes to 75 minutes for data consistency. Next you
need to get the number of minutes between the two date time fields by:
DateDiff("n",Me!EndDateTime - Me!StartDateTime). This expression will give
you 65 minutes. You then subtract that from the 75 minutes to get 10
minutes. Your generalized formula is:
75 - DateDiff("n",Me!EndDateTime - Me!StartDateTime)

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)