Junction table query textbox

[UltimateNeo]

Hello I have a report and made a textbox with the control source =dsum("QTY","[JobsT]", "InspectionID = 1") so QTY is in the JobsT and in a junction table i have InspectionID that is linked by jobno. If i set the report recordsource with the fields it returns the result. When i use the formula it does not work. I have also tried =dsum("QTY","ReportName", "InspectionID = 1") still to an error it cant find the InspectionID field. Any ideas would be greatly appreciated.

 

[mike60smart]

Hello I have a report and made a textbox with the control source =dsum("QTY","[JobsT]", "InspectionID = 1") so QTY is in the JobsT and in a junction table i have InspectionID that is linked by jobno. If i set the report recordsource with the fields it returns the result. When i use the formula it does not work. I have also tried =dsum("QTY","ReportName", "InspectionID = 1") still to an error it cant find the InspectionID field. Any ideas would be greatly appreciated.

Can you upload your database?

 

[UltimateNeo]

This is a guide to what I have done, i have not got my database from work here. As it is hard to get it back into work. Hope this helps

 

[mike60smart]

This is a guide to what I have done, i have not got my database from work here. As it is hard to get it back into work. Hope this helps

Hi

See the amended Report

I added a field to the JobT called Stage.

Then amended your DSum to read as follows:-

=DSum("QTY","[JobsT]","[Stage] =1")

 

[Gasman]

The inspectionID needs to be in the same domain.
Create query that brings in both, and dlookup that.

 

[UltimateNeo]

Hi

See the amended Report

I added a field to the JobT called Stage.

Then amended your DSum to read as follows:-

=DSum("QTY","[JobsT]","[Stage] =1")

I am not sure that would work. As say the job comes in 2 x for a stage inspection, then it comes in for it's final inspection. I am just wanting to count the qty to give a total of stage inspections and a total for final inspections.