Number to Text Format as hh:mm in Query

[Hoppag]

Hi,

I'm pretty new to Access (using 2003) and need some guidance.

I have 2 numeric fields in a table. They are both time stored as number format but the number 9 represents 09:00 and the number 17.3 respresents 17:30.

I want to create a new field which combines the two fields together to display: "09:00 - 17:30" rather than "9 - 17.3"

I can't really program (just starting out by playing in Query) so any programming advice will have to be in serious laymans terms.

Many thanks and I apologise if I don't make a lot of sense!

 

[raskew]

Hi -

Welcome to the forum!

Please don't take this personally but, in my opinion you've got a monster on your hands due to table design.

".....They are both time stored as number format but the number 9 represents 09:00 and the number 17.3 respresents 17:30..."

Don't understand why you are not using date/time fields, but I can assure you that it's making your job unnecessarily difficult.

The number 9 is not a big problem, but using 17.3 to represent 17:30 (5:30 pm) is going to do you in. A numbers field works in the digital mode, so 17.3 represents 17-3/10 hours with 3/10 of 60 minutes = 18 minutes, not 30 minutes.

It took me quite a while to come up with a system -- assuming you were to stick with the same table format -- that would convert your number 17.3 to 17:30 or 5:30 pm. It's gonna involve a fair amount of code and, in my opinion, it's just not necessary.

Suggest you copy/paste this to your debug (immediate) window and play around with it for a while. Then, give some thought to going to date/time fields.

'*********************************************************
x = 17.2 'represents 5:20 pm
y = int(x)'represents hours (17)

'the following string represents hours & minutes
z = str & ":" & round(ltrim(str((x - y) * 100)),2)
? z
17:20
? format(z,( "hh:nn"))
17:20


? timevalue(format(timevalue(z), "hh:nn"))
5:20:00 PM
' this is what you'd insert into a text field
? format(timevalue(z), "hh:nn")
17:20
'*********************************************************

Please post back when you've decided what direction you want to take.

Best wishes,

Bob

 

[Hoppag]

Thanks for that. Almost there...

The base data is stored on a server and accessed via an odbc connection; so I don't have any say in how it's stored.

I've got a query (then report) which displays working start times and end times for employees. The code works great except for:

1) Any time that has :00 minutes at the end e.g. 9:00(stored as "9" in the base data) creates an Error when I try to pull the start time and end time together into one text field.

This is in the query -
Hours: TimeValue(Format(TimeValue([St Time]),"hh:nn")) & " - " & TimeValue(Format(TimeValue([End Time]),"hh:nn"))

9.05 and 17.05 = 09:05:00 - 17:05:00
9 and 10 = #Error

2) Any value that has :x0 at the end comes out incorrect. e.g. 9.3 comes out as 09:03:00 instead of 09:30:00

3) Is it possible to loose the display of the number of seconds? They will always display as :00 so it seems a bit pointless to have them showng.

Sorry if all of this is relating to something I've cocked up or not tweaked in your code!

Thanks.

 

[Hoppag]

Found a simple way to do it in Query.

Format((Int([START_TIME])+(([START_TIME]-Int([START_TIME]))/0.6))/24,"Short Time")

Thanks

 

[raskew]

Hi -

Looks like you have it under control. Here's a little function, using your logic, which may make it a little easier to apply it in a query:

Public Function Num2Time(x As Double) As String
'Purpose: Convert a digital number to a time, e.g. 9.3 converts to 09:30
'Refer to: http://www.access-programmers.co.uk/forums/showthread.php?t=211129
'Example:
'? "between " & num2time(9.2)& " - " & num2time(13)
'   between 09:20 - 13:00
'*********************************************************

   Num2Time = Format((Int(x) + ((x - Int(x)) / 0.6)) / 24, "hh:nn")

'*********************************************************
End Function

Best wishes - Bob