Passing parameter form value to a query result

[OtakiriLad]

I am attempting to execute a query which will be used as the basis for a report which extracts records from a table where the date in the table is less than or equal to a supplied parameter form date.



I want to use the parameter date in the header of the report so need to pass it in the query result (yes every record in the query result will contain the same value).

The query I have created works beautifully in generating the correct set of records but the value of the parameter in each record is blank (or null?)

The SQL is as follows . . .

SELECT tblDocument.Number, tblDocument.Title, tblDocument.StatusID, tblPerson.FirstName & " " & tblPerson.LastName AS AuthorName, tblActivity.Version, tblActivity.Description, tblActivity.Date, [Forms]![frmParm1a]![ReviewDueDate] AS ReviewDueDate, tblDocument.DateNextReview
FROM tblStatus INNER JOIN (tblPerson INNER JOIN (tblDocument INNER JOIN tblActivity ON tblDocument.SysGenID = tblActivity.DocumentID) ON tblPerson.SysGenID = tblDocument.AuthorID) ON tblStatus.SysGenID = tblDocument.StatusID
WHERE (((tblDocument.DateNextReview)<=[Forms]![frmParm1a]![ReviewDueDate]) AND ((tblStatus.Name)="Existing")) OR (((tblStatus.Name)="Proposed")) OR (((tblDocument.DateNextReview)<=[Forms]![frmParm1a]![ReviewDueDate]) AND ((tblStatus.Name)="Draft"))
ORDER BY tblDocument.Number, tblDocument.Title, tblActivity.Version;

Why is ‘ReviewDueDate’ blank?

Thanks in advance
Dave

[CJ_London]

is it blank in the query or in the form or report?

do you have a field called ReviewDueDate in one of the tables in your query?

[OtakiriLad]

Thanks Ajax for the response. The result rows in the query are blank and that field name is unique to the parameter form - it does not exist in any other table or query.

A curious thing is that if I run the query direct and supply the parameter from the query prompt rather than from the form the field populates correctly.

I have also generated a workaround by referencing the form field ( [Forms]![frmParm1]![ReviewDueDate ) directly in the report and that works. However I would prefer a solution to populate the query result as there is no guarantee that in the future the parameter form will still exist at the time of the report generation.

Cheers
Dave

[Micron]

If you cannot guarantee that the parameter form will be open, you will have to try to assign the form control value to a variable. If it fails, you can direct execution to a part of the routine that supplies it some other way. As for why your work around works but the posted sql does not, perhaps it is the extra "a"? [Forms!frmParm1a.ReviewDueDate]