Need Query field to be Numeric, not String Based

[Guest]

I have a query with one field that calls a function. The function performs a
time difference calculation and converts it to time in days (i.e. 0.0125). I
checked to make sure the output was numeric by "isnumeric(Calculation)" and
it always comes back as "True". So the function should be returning a
number, yet when a sum is done on the field in the report, it errors out as:

"Microsoft Jet database engine could not execute the SQL statement because
it contains a field that has an invalid data type."

Therefore, it must be switching to a string field. If I export it into
Excel or perform a make-table query it is a string as well. How can I keep
it a number to perform my sum? Thanks for the help!!

 

[Guest]

Is it possible for the function to return a null value?
In that case you can use nz(FunctionName(),0)

Also you should declare the function as a number
Function FunctionName() as double

End Function

 

[Guest]

Could you post your query? I'm not sure whether you mean that it returns only
a single field or whether there is a list of fields returned but only one of
those is based on a function. What is the function declared to return? Where
is the isnumeric() test performed?

 

[Guest]

I defined the function as double and that did it. I should have thought of
that. Thanks for the help!!

Chaim,

Just an FYI, here is the query with the function listed below it. Thanks
for the help!

SELECT NewToolReworkOrderTable.Date, NewToolReworkOrderTable.RecordNumber,
NewToolReworkOrderTable.Toolmaker, NewToolReworkOrderTable.TimeIn,
NewToolReworkOrderTable.StartTime, NewToolReworkOrderTable.FinishTime,
[FinishTime]-[StartTime] AS [Elapsed Time],
CalculateTime([StartTime],[FinishTime]) AS [True Time],
NewToolReworkOrderTable.Count, MachineDataTable.Location, ToolroomTable.Shift
FROM ToolroomTable INNER JOIN (NewToolReworkOrderTable INNER JOIN
MachineDataTable ON NewToolReworkOrderTable.MachineNumber =
MachineDataTable.MachineNumber) ON ToolroomTable.EmployeeNumber =
NewToolReworkOrderTable.Toolmaker
WHERE (((NewToolReworkOrderTable.Date)>=#8/1/2005# And
(NewToolReworkOrderTable.Date)<=#8/6/2005#) AND ((MachineDataTable.Location)
Like "HI*"))
ORDER BY NewToolReworkOrderTable.Toolmaker;


Function CalculateTime(Starttime, Endtime) As Double
If Starttime > Endtime Then
CalculateTime = DateDiff("n", 0, Endtime) + DateDiff("n", Starttime, 1)
Else
'CalculateTime = Endtime - Starttime
CalculateTime = DateDiff("n", Starttime, Endtime)
End If
CalculateTime = CalculateTime / 60 / 24
testthis = IsNumeric(CalculateTime)
'MsgBox IsNumeric(CalculateTime)
End Function