473,324 Members | 2,246 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,324 software developers and data experts.

hours, minutes, and am/pm to a TIME variable

Good evening,

One more and I'm done - I promise. I have 3 variables - (hours,
minutes, and am or pm - 1 and 2, respectively). Is there some way that
I can combine these 3 into a single variable that displays the time in
either military or standard format (2:00 p.m. or 1400)?

Thank you in advance.

Mike

Feb 15 '06 #1
7 4519
air code ....
Public Function ShowTime( _
ByVal hours As Long, _
ByVal minutes As Long, _
ByVal AmorPm As Long) As String
Dim d As Date
d = TimeSerial(hours + (AmorPm - 1) * 12, minutes, 0)
ShowTime = Format(d, "hhnn")
' or
' ShowTime = Format(d, "hh:nn ampm")
End Function

Feb 15 '06 #2
Lyle,

Hey thanks a bunch for the help. I'm afraid I've bitten of more than I
can chew. I ashamed to say this, but I'm not sure what to do with this
code! I've been a SAS programmer all my life and am being forced to
learn Access. The good news is that I'm a quick learner and I actually
enjoy this stuff. So, if its not too much trouble, could you elaborate
a bit for this newbie.

Mike

Feb 15 '06 #3
I was born with this knowledge myself ... NOT ... I think I asked
something similar right here in CDMA eight or nine years ago. Now, can
I remember the answer?

1. Copy the code;
2. Open your database;
3. In the database window, select Modules;
4. Click New in the Toolbar at the top;
5. Paste the code in the new module window that appears ;
6. Save the module by clicking on the diskette icon.

Now you have it; how to use it?

Suppose you have a table with fields called hours, minutes, AmorPm:

1. In a query you can use the function as:
ShowTime(hours,minutes,AmorPm AS SomeFieldName;
2. On a form which has that table as its (ultimate) recordsource one
could have a control with control source set to =
ShowTime(hours,minutes,AmorPm);
3. Ditto for a report;
4. You can (back to in a module as above) have a Sub as
Public Sub ShowTheTime
Msgbox ShowTime(5, 17, 2)
' shows 1717
End Sub
etc ...

Feb 16 '06 #4
Hey man - I really enjoyed your humor and most of all your generosity.
I will try and let you know how things go.

Mike

Feb 16 '06 #5
Lyle - I created "TimeofHarvest" in a query as below and it worked
perfectly. The bad news is I ran into the following error message when
I tried to filter my query on the new TimeofHrvst variable:

TimeofHrvst: ShowTime([Hour],[Minute],[am_or_pm])

ERROR: DATATYPE MISMATCH IN CRITERIA EXPRESSION - this actually causes
Access to lock up on me. For what it is worth, I ran into the same
problem with the following "derived" variables
DateSerial((IIf([harvest_month]=1,2006,2005)),[harvest_month],[day])

If I type a date in - with or without quotes around it (e.g.
10/20/2005) I get the same error message.

Again, your help is greatly appreciated!

Mike

Feb 19 '06 #6
Seeing the whole query string and knowing the data types of the fields
Hour, Minute and am_or_pm.
As well, date time variables are unlikely to deal well with military
time (hhnn) as you specified, but might be able to guess hh:nn.

Feb 19 '06 #7
Lyle - All 3 variables have the same properties: Datatype = Number,
Field Size: Integer. Is there something else that I can send you to
help pinpoint the problem?

Feb 20 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: John McGinty | last post by:
Hello Chaps, Just a little sounding on removing out of hours from some datetime date that I have. Basically we have a helpdesk that logs calls when they are entered and when they are resolved....
2
by: J M | last post by:
I rebooted a network device just few minutes ago and getting device-up-time value 3191. How do I convert a time elapsed in days hours minutes and seconds for above value? Example: This device...
1
by: serge | last post by:
Right now the database I am working with is storing time in an Integer data type and is storing the time value in seconds. The application does not allow entering seconds. It accepts minutes and...
22
by: Drum2001 | last post by:
I have a table that tracks employee times. I have a column (Date/Time). Users, through a form, enter how long it takes them to complete a task. For example, 03:45 = 3 hours and 45 mins. I am...
3
by: Stephen Chaplin | last post by:
I'm summing up time spent on jobs over a week, some of these jobs last greater than 24 hours and when access sums these up it appears that it starts again at 00:00 once it gets past 23:59. Is it...
8
by: King | last post by:
Hi I have following MS Acess query Here is the query ID Name Prgm ID Client ID Date Start Time End Time Minutes C4 Trisha TIP DEK0703 7 /7 /2006...
3
by: Nigel Heald | last post by:
Hi Folks, We have a form that records flight times in hours and minutes, for example a 1 hour 15 minute flight is recorded as 1:15 Does anyone know how to get Access 2003 to calculate a total...
12
by: remya1000 | last post by:
how can i select time (hours,minutes,second) in updown arrow buttons in VB.NET. as we have current time displayed in date and time properties in our system. in date and time properties, while...
6
by: richbneal | last post by:
I really like the site so far and this is my first post. I have looked through some of the archives with no luck. I have also read the posting guidelines and will do my best to be clear and accurate...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.