Problem Comparing two times 2

[jpl458]

Have two tables, in the first, time is stored as 1/1/1900 5:34:23 PM (General Time). In the second, the time is stored as 5:34::23 PM Long Time). I am trying to get all the data where first date is greater than or equal to the second and the first time is greater than the second. There is both a date and a time column in both tables, and I can get the date part correct, the when ever I add the time compare I get nothing back.

Here is the SQL:

SELECT BayCounts.ID, BayCounts.Bayno, BayCounts.Count, BayCounts.AddDate, Sum(dbo_Transaction_Table.Balls) AS SumOfBalls, dbo_Transaction_Table.Time_of_Transaction
FROM BayCounts INNER JOIN dbo_Transaction_Table ON BayCounts.Bayno = dbo_Transaction_Table.Location_Id
WHERE (((dbo_Transaction_Table.Date_of_Transaction)>=[BayCounts]![AddDate]))
GROUP BY BayCounts.ID, BayCounts.Bayno, BayCounts.Count, BayCounts.AddDate, dbo_Transaction_Table.Time_of_Transaction
HAVING (((dbo_Transaction_Table.Time_of_Transaction)>[dbo_Transaction_Table]![Time_of_Transaction]>[BayCounts]![AddTime]))

I think I need to convert the first date (1/1/1900 5:34:23 PM) but can't figure out how to do that, easily.

What should I do to get the time compare to work?

Thanks in advance

jpl

 

[PHV]

Something like this ?
SELECT B.ID,B.Bayno,B.Count,B.AddDate,Sum(T.Balls) AS SumOfBalls,T.Time_of_Transaction
FROM BayCounts B INNER JOIN dbo_Transaction_Table T ON B.Bayno=T.Location_Id
WHERE T.Date_of_Transaction+T.Time_of_Transaction>=B.AddDate+B.AddTime
GROUP BY B.ID,B.Bayno,B.Count,B.AddDate,T.Time_of_Transaction

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jpl458]

PHV, your answer was partially right. Needed to add something like DateValue(T.Date-of_Transaction)+TimeValue(Time_of_Transaction).......Then it worked. I never would have thought of concatenation.
'
Thanks

jpl

 

[SkipVought]

I never would have thought of concatenation.

concatenation???

PHV did not concatenate the date and time. They are mathematically summed.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jpl458]

That makes a lot more sense, given how dates are really stored.

Thanks for the info.

jpl

 

[SkipVought]

Which is one reason I never like using [highlight]+[/highlight] for concatenation; rather [highlight]&[/highlight] in MS Access, VBA etc.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}