Query problem

  • Thread starter Thread starter Justin
  • Start date Start date
J

Justin

I have entered the following SQL into a query as suggested by Tom
Ellison. When I try to run the query, I get the following error:

Invalid use of '.', '!', '()'. in query expression 'T1.[Truck No] =
T2.[Truck No] AND T1.[Date] = (SELECT MAX([Date]) FROM [Route Sheet]
T3 WHERE t3.[Truck No] = T1.[Truck No] AND t2.[Date] = (SELECT
MAX([Date]) FROM [Route Sheet] T4 WHERE T4.[Truck No] = T2.[Truck No]
AND T2.[Date] < T1.[Date]'.


SELECT T1.[Truck No], T1.Date AS Date1, T2.Date AS Date2, T1.[Ending
Mileage] AS Mileage1, T2.[Ending Mileage] AS Mileage2
FROM [Route Sheet] AS T1, [Route Sheet] AS T2
WHERE T1. [Truck No] = T2. [Truck No] AND T1. [Date] = (SELECT MAX
([Date]) FROM [Route Sheet] T3 WHERE T3. [Truck No] = T1.[Truck No])
AND T2. [ Date] = (SELECT MAX ([Date]) FROM [Route Sheet] T4 WHERE T4.
[Truck No] = T2. [Truck No] AND T2. [Date] < T1.[Date]);

Could someone point out where the problem is?

TIA
Justin
 
[Truck No] = T2. [Truck No] AND T2. [Date] < T1.[Date]);

Could someone point out where the problem is?
Click to expand...

Right there. Blanks between T2. and [.

You should also change the name of the field Date. That's a reserved
word, and Access WILL get it confused with the Date() function.
 
John Vinson said:
[Truck No] = T2. [Truck No] AND T2. [Date] < T1.[Date]);

Could someone point out where the problem is?
Click to expand...

Right there. Blanks between T2. and [.

You should also change the name of the field Date. That's a reserved
word, and Access WILL get it confused with the Date() function.
Click to expand...

I guess that is why it now requests a parameter for "T2.Date"?
I'll rename this field and come back to the group if I have any other problems.

Thanks, John

Justin
 
Back
Top