Excel Formula into Access Query

[dr4ke]

Morning!

I currently use a formula in Excel to convert a time into a readable format. I've recently moved over to using Access, as it's much better placed for the work i'm trying to achieve. The problem is; I don't know how to replicate the formula into an Access query.

Could anybody help? The excel formula is

=FLOOR(A1,1) & ":" & ROUND(60*(A1-FLOOR(A1,1)),0)

I've tried changing 'A1' to the column name ("Start Time"), but that doesn't work.

Regards,
Dr4ke

[recyan]

Do not know whether I have understood things correctly :

Just check out if below gives some guidelines :

SELECT 
    myTable.[StartTime], 
    Int([StartTime]) AS Expr2, 
    [StartTime]-[Expr2] AS Expr3, 
    60*[Expr3] AS Expr4, 
    Round([Expr4],0) AS Expr5, 
    [Expr2] & ":" & [Expr5] AS Expr6
FROM 
    myTable;

Thanks

[dr4ke]

Perfect! Thank you!!

[recyan]

Glad you found it helpful.

Thanks

[dr4ke]

One more quick question, I need to do this for more than 1 column; can I do multiple SELECT's? I've tried to no avail... The ones I would require are...

SELECT 
    myTable.[Resp Time], 
    Int([Resp Time]) AS Expr2, 
    [Resp Time]-[Expr2] AS Expr3, 
    60*[Expr3] AS Expr4, 
    Round([Expr4],0) AS Expr5, 
    [Expr2] & ":" & [Expr5] AS Expr6
FROM 
    myTable;

SELECT 
    myTable.[Start Time], 
    Int([Start Time]) AS Expr2, 
    [Start Time]-[Expr2] AS Expr3, 
    60*[Expr3] AS Expr4, 
    Round([Expr4],0) AS Expr5, 
    [Expr2] & ":" & [Expr5] AS Expr6
FROM 
    myTable;

SELECT 
    myTable.[Fix Time], 
    Int([Fix Time]) AS Expr2, 
    [Fix Time]-[Expr2] AS Expr3, 
    60*[Expr3] AS Expr4, 
    Round([Expr4],0) AS Expr5, 
    [Expr2] & ":" & [Expr5] AS Expr6
FROM 
    myTable;

Can you help me

[recyan]

1) Avoid using spaces in Column Names.

2) Your question is a perfect candidate for a User made Function. Make a function, which uses the logic & then call the function whenever you want to use it on any field, something like below.

Try saving a function like below :

Function TheOutputTime(TheInputTime As Double)
    TheOutputTime = Int(TheInputTime) & ":" & Round(60 * (TheInputTime - Int(TheInputTime)), 0)
End Function

& then
use the function in your query

SELECT 
	myTable.StartTime, 
	myTable.EndTime, 
	TheOutputTime([StartTime]) AS TheStartTime, 
	TheOutputTime([EndTime]) AS TheEndTime
FROM 
	myTable;

Note : My VBA skills are not worth speaking about.

Thanks

[dr4ke]

Have tried this, but it's throwing up errors; below is what I tried

Function TheOutputTime(TheInputTime As Double)
    TheOutputTime = Int(TheInputTime) & ":" & Round(60 * (TheInputTime - Int(TheInputTime)), 0)
End Function
SELECT 
 DaisyMSR.StartTime, 
DaisyMSR.RespTime,
 DaisyMSR.EndTime, 
 TheOutputTime([StartTime]) AS TheStartTime, 
TheOutputTime([RespTime]) AS TheRespTime, 
 TheOutputTime([EndTime]) AS TheEndTime
FROM 
 DaisyMSR;

I presume this is suppose to have all gone in the same query? I must admit I've never heard of 'adding a function' :-)

The report comes from a third party, who have the columns like this; Is it a big risk if there are spaces in the column names?

You're knowledge is far superior to mine :-)!

Thanks again

[recyan]

1) What error is it throwing up ?
2) Have you saved the function using the Create Module ?
3) If your table columns currently have spaces in them, then you will have to retain them in your query,
like
DaisyMSR.[Start Time],
DaisyMSR.[End Time],

Thanks