datediff calculation on Access Form

[p-rat]

i have this query on my back end SQL that runs fine:

CONVERT(MONEY,(DATEDIFF(N,StartTime,EndTime)/15)/4.0)

i'm wanting to put this calculation in a textbox on my form that when
the user enters in the StartTime and EndTime it places the correct
value in the textbox.

i'm getting error after error and can't figure out what I'm doing
wrong.

help. thanks.

 

[PJFry]

Can you post the exact error(s) you are getting?

From the syntax you posted, CONVERT(MONEY...) might be trying to use custom
function. I'm not terribly conversant in JET functions, but the syntax makes
me think it might be custom.

 

[Linq Adams via AccessMonster.com]

Convert() is not a native Access function. To be honest, the whole thing
looks somewhat confusing, but the interval, N in this case, has to be a
string, so

DateDiff(N,StartTime,EndTime)

needs to be

DateDiff("n",StartTime,EndTime)

 

[Linq Adams via AccessMonster.com]

Convert() is not a native Access function. To be honest, the whole thing
looks somewhat confusing, but the interval, N in this case, has to be a
string, so

DateDiff(N,StartTime,EndTime)

needs to be

DateDiff("n",StartTime,EndTime)

 

[Linq Adams via AccessMonster.com]

Sorry for the stuttering!

;0)>

 

[John W. Vinson]

i have this query on my back end SQL that runs fine:

CONVERT(MONEY,(DATEDIFF(N,StartTime,EndTime)/15)/4.0)

i'm wanting to put this calculation in a textbox on my form that when
the user enters in the StartTime and EndTime it places the correct
value in the textbox.

i'm getting error after error and can't figure out what I'm doing
wrong.

help. thanks.

You seem to be mixing T-SQL with native Access code. The Access expression for
this would be

CCur(DateDiff("n", [StartTime], [EndTime])/15)/4.0

or simply divide by 60 rather than doing it in two steps.

This assumes that you want to calculate the number of minutes between the two
times, divide that result by 15, convert that fraction to currency, and then
divide that value by 4.