Convert to Half Hour of Day

[spoonerstreet]

I am trying to take standard MS Time:

2/22/2006 8:56:37 AM (stored in MS Decimal Format)

and convert it to the half hour of the day it occurred in:

2/22/2006 8:56:37 AM would be in the 18th half hour of the day (48 in
all).

Anyone know of a way to do this within a query?

I have tried this:

Fix(0.999999+(T ime()-[TimeField])*24)

and all it returns is the actual half hour of the year, I believe.

Thanks in advance,

Dave

[Rich P]

Add this function to a standard Access Code module.

Function getHalfHour(d1 As Date) As Integer
Dim d2 As Date
d2 = Format(d1, "Short Date")
getHalfHour = CInt((DateDiff( "n", d2, d1)) / 30)
End Function

Then, in your query you can use it like this:

SELECT gethalfhour(you rDatefld) AS d1
FROM yourTable;

In the Access Query Builder just specify:

d1:getHalfHour( yourDatefld)

in the query field.

This will return 18 for the half hour for
#2/22/06 8:56:37 AM#

Rich

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[Wayne Morgan]

It may be easiest to set up a lookup table with the 48 entries. It would
have 2 columns, the first column would have 1 through 48, the second would
have the times of each half hour. You would then find the value in column 1
associated with the maximum value in column 2 that is equal to or less than
the time you are testing.

Example:
DLookup("Field1 ", "tblHalfHourLoo kup", "Field2 = #" & DMax(""Field2"" ,
""tblHalfHourLo okup"", ""Field2<= #"" & [TimeField] & ""#"") & "#")

--
Wayne Morgan
MS Access MVP
<sp***********@ gmail.com> wrote in message
news:11******** *************@p 10g2000cwp.goog legroups.com...

I am trying to take standard MS Time:

2/22/2006 8:56:37 AM (stored in MS Decimal Format)

and convert it to the half hour of the day it occurred in:

2/22/2006 8:56:37 AM would be in the 18th half hour of the day (48 in
all).

Anyone know of a way to do this within a query?

I have tried this:

Fix(0.999999+(T ime()-[TimeField])*24)

and all it returns is the actual half hour of the year, I believe.

Thanks in advance,

Dave