Trying to use two columns with datediff

[Capt'n Roy]

Hi,
I am usin vb6 and access 2000 for development.
if I place this syntax in a query in access it works, but when I place this
in my code to it returns an e-fail error. I assume it is how I am trying to
concatinate the date and time fields

in access:

(DateDiff("d",DateStarted & " " & TimeStarted, DateStopped & " " &
TimeStopped)) as ET

from Vb:

SHAPE {select TripName,StartDate,StopDate,autoid from Trip Order by
TripName} AS ParentCMD APPEND ({select
Destination,ColReg,DateStarted,TimeStarted,DateStopped,TimeStopped,
(DateDiff("d",DateStarted & " " & TimeStarted, DateStopped & " " &
TimeStopped)) as ET,
Notes,autoID,OrgLegID,TripID from LegsONTrip Order by DateStarted } AS
ChildCMD RELATE autoid TO TripID) AS ChildCMD


any suggestion on how to correct?

thanks,

roy

 

[Douglas J. Steele]

Unfortunately, when you're running queries from outside of Access, your only
connection to the database is through the Jet Engine, which doesn't know
anything about most VBA functions such as DateDiff.

There really isn't anyway around this limitation.

 

[Capt'n Roy]

Douglas,
Then why does this code work:
adoPrimaryRS.Open "SHAPE {select TripName,StartDate,StopDate,autoid from
Trip Order by TripName} AS ParentCMD APPEND " & _
"({select
Destination,ColReg,DateStarted,TimeStarted,DateStopped,TimeStopped," & _
"(DateDiff(""h"",DateStarted, DateStopped)) as ET, " & _
"Notes,autoID,OrgLegID,TripID from LegsONTrip Order by
DateStarted } AS ChildCMD RELATE autoid TO TripID) AS ChildCMD", db,
adOpenStatic, adLockOptimistic

this code returns the correct number of hours between two dates.

How does on concatenate two columns in a query?

Any help will be greatly appreciacted.

Roy

 

[Graham R Seach]

Roy,

What I see are incompatible arguments for the DateDiff function:

Assuming the fields are all in the correct format (for example, 10/10/2004,
and 10:10:00 AM), what you should have is this:

(DateDiff("d", CDate(DateStarted)+CDate(TimeStarted),
CDate(DateStopped)+CDate(TimeStopped)) As ET

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html

 

[Capt'n Roy]

Graham,
Thanks for the help. This fix my syntax.
Many thanks,
Roy