date / time question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The time of posting this message is 13:51 (28th Sept) (GMT)

I have copied some recent records from a query

expr1: date() - [SLA_Date2]


SLA_Date2 Expr1

26/09/2007 08:34:21 1.64
26/09/2007 10:11:19 1.57
26/09/2007 10:18:43 1.57
26/09/2007 10:21:26 1.57
26/09/2007 10:17:25 1.57
26/09/2007 12:19:29 1.48

Shouldn't "Expr1" for the first record be at least 2 ?

Can someone tell me what the decimal represents?

Cheers
 
Hi,

Access stores dates as numbers. It then formats these number to look like
dates. For example:

Debug.Print CDbl(#26/09/2007 08:34:21#) = 39351.3571875

That's 39351 days since 12/30/1899 at midnight.

The decimal portion is the time. 0.5 is Noon.
Debug.Print CDate(.5) = 12:00:00 PM
Debug.Print CDate(.99999) = 11:59:59 PM

Still your numbers don't add up. It could be that SLA_Date2 isn't showing AM
or PM. Do the numbers look right for something in the evening or afternoon?

Then there is the Date() function. It depends on the internal setting of the
computer where Access is running. While in Access do a Ctrl + g to bring up
the VBA window. In the Immediate window put the following and press enter:

Debug.Print Date()

Is the result different than what you expect? If so, that's the problem.
 
The default for [SLA_Date2] is "now()" but I was using "date()" in the
calculation. No wonder it was wrong:

expr2 uses "now"

SLA_Date2 expr1 expr2
26/09/2007 08:34:21 1.64 2.25
26/09/2007 10:11:19 1.57 2.18
26/09/2007 10:18:43 1.57 2.18

and that is correct.

Access must use midnight in which case 1.64 is correct.

Jerry Whittle said:
Hi,

Access stores dates as numbers. It then formats these number to look like
dates. For example:

Debug.Print CDbl(#26/09/2007 08:34:21#) = 39351.3571875

That's 39351 days since 12/30/1899 at midnight.

The decimal portion is the time. 0.5 is Noon.
Debug.Print CDate(.5) = 12:00:00 PM
Debug.Print CDate(.99999) = 11:59:59 PM

Still your numbers don't add up. It could be that SLA_Date2 isn't showing AM
or PM. Do the numbers look right for something in the evening or afternoon?

Then there is the Date() function. It depends on the internal setting of the
computer where Access is running. While in Access do a Ctrl + g to bring up
the VBA window. In the Immediate window put the following and press enter:

Debug.Print Date()

Is the result different than what you expect? If so, that's the problem.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

scubadiver said:
The time of posting this message is 13:51 (28th Sept) (GMT)

I have copied some recent records from a query

expr1: date() - [SLA_Date2]

SLA_Date2 Expr1

26/09/2007 08:34:21 1.64
26/09/2007 10:11:19 1.57
26/09/2007 10:18:43 1.57
26/09/2007 10:21:26 1.57
26/09/2007 10:17:25 1.57
26/09/2007 12:19:29 1.48

Shouldn't "Expr1" for the first record be at least 2 ?

Can someone tell me what the decimal represents?

Cheers
Click to expand...
Click to expand...
 
Back
Top