I like to make a query that will pull information based off of the value placed inside a control on my form. For instance, I want to see all of the records in my subform's table that match it's parent table's autonumber.
I found this in my Google travels:
SELECT A.tblAnasaziStaff_LAST_NAME, A.STARTDATE
FROM tbl2DaysAppts A
INNER JOIN tbl2DaysAppts B ON A.tblAnasaziStaff_LAST_NAME = B.tblAnasaziStaff_LAST_NAME AND A.STARTDATE = B.STARTDATE
WHERE A.tblAnasaziStaff_LAST_NAME In ('SCARDINO','WRIGHT'))
ORDER BY A.STARTDATE;
...and it seems to be a good start however I have to use a LEFT JOIN as opposed to an inner join. I'm at a standstill as to where to go from here. Any suggestions would be great.
My code lies below:
SELECT [ECN Table].[ECN#], [BOM TrackingTBL].ID, [BOM TrackingTBL].Action, [BOM TrackingTBL].[Bill Number], [BOM TrackingTBL].[Projects Affected]
FROM [ECN Table] LEFT JOIN [BOM TrackingTBL] ON [ECN Table].[ECN#] = [BOM TrackingTBL].ID;
I've found the answer for anyone else's future reference. If you want to query a table based on a control on a form, use this setup:
([Forms]![MyForm]![MyObject]) as the criteria in the query field of the parent table. The query will only run correctly from the open form, which is perfect, at least for me, because the form needs to be open.