By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,080 Members | 1,268 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,080 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
7 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.