Select Query does not see subform data

[splats]

This query is not recognizing the subform record
sfrmDCR Incidents]![IncidNumber]
although the record is there on the subform. Any suggestions would be appreciated. Thank you!

SELECT Employee.EMPNO, [DCR Incidents].IncidNumber, Employee.NAME, Employee.OCCUPATION, Employee.STARTDATE, [DCR Incidents].*, DateDiff("yyyy",[Employee.DOB],[DCR Incidents.IncidDate])+Int(Format([DCR Incidents.IncidDate],"mmdd")<Format([Employee.DOB],"mmdd")) AS Age
FROM Employee INNER JOIN [DCR Incidents] ON Employee.EMPNO = [DCR Incidents].Empno
WHERE (((Employee.EMPNO)=[Forms]![frmEmployee]![EMPNO]) AND (([DCR Incidents].IncidNumber)=[Forms]![sfrmDCR Incidents]![IncidNumber]))
ORDER BY Employee.NAME;

 

[PHV]

Perhaps this ?
[Forms]![frmEmployee]![sfrmDCR Incidents].Form![IncidNumber]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[splats]

Thank you PHV for your help. Unfortunately, I am getting an error as below. Any suggestions out there?

it is
"The specified field 'IncidNumber'could refer to more than one table listed in the from clause of your SQL statement."

Here is my SQL statement.

SELECT Employee.EMPNO, [DCR Incidents].IncidNumber, Employee.NAME, Employee.OCCUPATION, Employee.STARTDATE, [DCR Incidents].*
FROM Employee INNER JOIN [DCR Incidents] ON Employee.EMPNO = [DCR Incidents].Empno
WHERE ((([DCR Incidents].IncidNumber)=[Forms]![frmEmployee]![sfrmDCR Incidents].[Form]![IncidNumber]))
ORDER BY Employee.NAME;

I have the command button on both the subform (sfrmDCRIncidents) and the main form(frmEmployee). Unfortunately, it does not work on either. Is there something missing in the SQL statement to get it to work properly?

Thank you

 

[PHV]

Replace [DCR Incidents].* by the list of the needed remainning fields of [DCR Incidents].
Actually your select clause has two fields named IncidNumber thus the ambiguity in the where clause.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886