Criteria in query

[D Willett]

Hi.
Can I use the following expression in a query criteria:

Not In ([forms]![frmEditVehicle]![jobD])

I'm trying to return records via a listbox where the current Registration exists in tblEst but not the current jobID on the form.

Code: Select all

SELECT tblEST.Registration, tblEST.Manufacturer, tblEST.Model, tblEST.VehBody, tblEST.VehSize, tblEST.VehYear, tblEST.[VIN-Chassis], tblEST.EngineNumber, tblEST.PaintType, tblEST.PaintCode, tblEST.VehColour, tblEST.JobID
FROM tblEST
WHERE (((tblEST.Registration)=[forms]![frmEditVehicle]![Registration]) AND ((tblEST.JobID) Not In ([forms]![frmEditVehicle]![jobD])));

Explanation.
When a user creates a Registration it is saved in tblEst, the next form to load is frmEditVehicle which has a listbox with the above SQL. I want to return any other record where the Registration matches but not the current JobID.

[HansV]

Try

...
WHERE (((tblEST.Registration)=[forms]![frmEditVehicle]![Registration]) AND ((tblEST.JobID) <> [forms]![frmEditVehicle]![jobD]));

[D Willett]

Got it.
WHERE (((tblEST.Registration)=[forms]![frmEditVehicle]![Registration]) AND ((tblEST.JobID) <> [forms]![frmEditVehicle]![jobD]));

There's an I missing in JobID........ ( my mistake )