Syntax error of missing operator when trying to use Inner Join (Access VBA)-CodePudding

I have tried for quite a while now to fix the missing operator error in the below code, which occurred after i added the INNER JOIN.

Any help is appreciated

Set qdf = CurrentDb.CreateQueryDef("", " SELECT PlantCode, PotSize, WeightToWatch, DateWatered, WeightAfter FROM ListWaterings " & _
    " WHERE DateWatered = (SELECT MAX(T1.DateWatered) FROM ListWaterings AS T1 WHERE T1.PlantCode = ListWaterings.PlantCode) " & _
    " INNER JOIN ListPlants ON ListPlants.PlantCode = ListWaterings.PlantCode "

The result i am trying to accomplish is the following

Table called ListPlants (simplified)

PlantCode	PotSize	WeightToWatch
18	15	1200
19	22	1450

A Table called ListWaterings

PlantCode	Date Watered	WeightAfter
18	23-03-2022 15:50	1250
19	23-03-2022 15:51	1500
18	23-03-2021 15:50	1300
19	23-03-2021 15:51	1550

And the result of my query would look like

PlantCode	DateWatered	WeightAfter	Potsize	WeightToWatch
18	23-03-2022 15:50	1250	15	1200
19	23-03-2022 15:51	1500	22	1450

CodePudding user response：

T2 is just an alias of the scalar value returned by

(SELECT MAX(T1.DateWatered) FROM ListWaterings AS T1 WHERE T1.PlantCode = ListWaterings.PlantCode)

You're not referring to this anywhere else so you really don't need it, and certainly not in the ON clause of your JOIN. I think this is just a typo. Perhaps what you meant to say is

SELECT PlantCode,
    DateWatered,
    WeightAfter
FROM ListWaterings
WHERE DateWatered =
    (SELECT MAX(T1.DateWatered)
    FROM ListWaterings AS T1
    WHERE T1.PlantCode = ListWaterings.PlantCode)
INNER JOIN ListPlants
ON  ListPlants.PlantCode = ListWaterings.PlantCode

EDIT I misplaced the WHERE before the JOIN. And OP added a few more fields to the output so here should be the correct syntax:

SELECT ListPlants.PlantCode,
    ListWaterings.[DateWatered],
    ListWaterings.WeightAfter,
    ListPlants.PotSize,
    ListPlants.WeightToWatch
FROM ListPlants
INNER JOIN ListWaterings
ON  ListPlants.PlantCode = ListWaterings.PlantCode
WHERE ListWaterings.[DateWatered]=
    (SELECT MAX(T1.DateWatered)
    FROM ListWaterings AS T1
    WHERE T1.PlantCode = ListWaterings.PlantCode)