How to strip out leading zeroes in query?

[FrankBooth]

Hello,

I would appreciate some help with this one, please.

I have a query that gathers time values from a table and I'm putting
those in an excell sheet to do some charts with them. The problem is
that the query returns time values with leading zeroes.

So for example a time of 9:00 AM, is returned as 09:00 AM, and the
charts read that as 0 so nothing is charted.

here is my query:
SELECT Employees.FirstName, Format(Avg([Events].[StartTime]),"Short
Time") AS AvgTime
FROM Employees INNER JOIN Events ON Employees.EmployeeID =
Events.EmployeeID
WHERE (((Events.EmployeeID)<>0))
GROUP BY Employees.FirstName;


Can that be somehow modified to not return those leading zeroes??

OR Perhaps if the table itself can be modified so it does not store
the value with a leading zero, that would be good too.

Thank you in advance,
--FB

 

[Van T. Dinh]

Access / Jet does not store the time as you see in the
idsplay. It stores the time as a decimal number < 1.0
which is the the fraction of the day. For example 0.25 is
6am and 0.5 is midday.

The Format() function actually convert the time value to
Variant of Text type and possibly Microsoft Graph doesn't
recognise this String as a time value.

Try remove the Format() function and simply use:

Avg([Events].[StartTime]) AS AvgTime

HTH
Van T. Dinh
MVP (Access)