forms reference not working at form level but does at query level?

[redtux]

Hi I am very confused by this issue - here is the SQL

SELECT lk_stages.stage, lk_stages.stage & " -" & [stage_cat] & " - " &
[stage_desc] AS [desc]
FROM lk_stages INNER JOIN tb_progress ON lk_stages.stage = tb_progress.Stage
WHERE
(((tb_progress.complaint_id)=[forms]![fm_client_tracking]![complaint_id]) AND
((Left([lk_stages]![stage],1))=Left([tb_progress]![stage],1)))
GROUP BY lk_stages.stage, lk_stages.stage & " -" & [stage_cat] & " - " &
[stage_desc];


This works if I run from a query(and form is open) - but if I try to run it
from the form I get a prompt for
)=[forms]![fm_client_tracking]![complaint_id] (which is the form)

Any ideas/explanations for this appreciated

 

[Klatuu]

You have a syntax problem. Anything is quotes is taken as literally what is
in the quotes. To use the references, they have to be outside the quotes.

SELECT lk_stages.stage, lk_stages.stage & " -" & [stage_cat] & " - " &
[stage_desc] AS [desc]
FROM lk_stages INNER JOIN tb_progress ON lk_stages.stage = tb_progress.Stage
WHERE
(((tb_progress.complaint_id)= '" &
[forms]![fm_client_tracking]![complaint_id]) & "' AND
((Left([lk_stages]![stage],1))=Left([tb_progress]![stage],1)))
GROUP BY lk_stages.stage, lk_stages.stage & " -" & [stage_cat] & " - " &
[stage_desc];

If tb_progress.complaint_id is a numeric field, leave out the single quotes.