471,579 Members | 1,622 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,579 software developers and data experts.

Convert to Half Hour of Day

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+(Time()-[TimeField])*24)

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

Thanks in advance,

Dave

Mar 9 '06 #1
2 2745
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(yourDatefld) 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 ***
Mar 9 '06 #2
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", "tblHalfHourLookup", "Field2 = #" & DMax(""Field2"",
""tblHalfHourLookup"", ""Field2<= #"" & [TimeField] & ""#"") & "#")

--
Wayne Morgan
MS Access MVP
<sp***********@gmail.com> wrote in message
news:11*********************@p10g2000cwp.googlegro ups.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+(Time()-[TimeField])*24)

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

Thanks in advance,

Dave

Mar 9 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Benoit BESSE | last post: by
5 posts views Thread by ECVerify.com | last post: by
3 posts views Thread by Laguna | last post: by
4 posts views Thread by msosno01 | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by Vinnie | last post: by
1 post views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.