473,221 Members | 2,205 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,221 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 2842
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: perplexed | last post by:
How do you convert a user inputted date to a unix timestamp before insterting it into your database? I have a form, with a textfield for a date that the user inputs in the format mm-dd-yyyy and...
1
by: Benoit BESSE | last post by:
Hi, I try to write a fonction which take a date and time and convert it into a NTP time. I have to use mktime but I did not work at all. Here is my code dans the exécution. Please help. Thanks...
2
by: James P. | last post by:
Hello, In my Access report, I have a minutes field in the detail line. How do I convert that minute to hour and minute. The problem for me now is if I take the minutes, say 75 divide by 60, it...
5
by: ECVerify.com | last post by:
I am trying to convert a computers local time to Pacific time, I can get it back to UTC pretty easy but I actually want to convert whatever the local time is on the computer I want to calculate...
2
by: yxq | last post by:
Hello, I found there are some date formats in the email header, for example: Fri, 23 Sep 2005 08:51:56 +0800 Sat, 17 Sep 2005 09:08:07 Wed Oct 19 13:40:23 2005 19 Oct 2005 13:40:23 +0000...
14
by: Michael Barrido | last post by:
I have this for example: Dim iSeconds as int32 = 3600 '3600 seconds is one hour How do i convert it to "01:00:00" ? Please help. Thanks in advance!
3
by: Laguna | last post by:
Hi, I have an XML file in a single long string. How can I convert it into the nicely indented format as displayed by Internet Explorer using Python? Thanks, Laguna Input XML file (one...
4
by: msosno01 | last post by:
I have Java client that connects to C++ server. The client sends integer in binary using DataOutputStream write function. I am reading these data into buffer. I have to convert this buffer back...
1
by: sebasC | last post by:
the code below is used on a site to show time in multiple time zones on the website what i need is to convert the php code below to asp so that it will work. because the clock shows on the site but...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.