Count to display on form

[emzipoo4u]

Hi there, I have 2 tables.

Vehicle table - gives vehicle details
Vehicle defect table - lists defect reports for vehicles

One vehicle can have many defect reports.

I have a form that is linked to the vehicle table.

On this form I want to display a count of the number of defect reports in the last month. How can I do this ?

 

[apr pillai]

Write =DCount() Function in a Textbox on the Vehicle Form. Sample expression (run successfully on Branch Visit Table in one of my databases) is given below:

=DCount([[B][I]PlannedDt[/I][/B]],"[B][I]BRVISIT[/I][/B]","[B][I]EMPCOD[/I][/B]=" & [[B][I]EMPCOD[/I][/B]] & " AND " & "(Format([PlannedDt],'yyyymm'))='" & Format(Date()-Day(Date()),"yyyymm") & "'")

Replace the Field and Table Names with your own. Use the punctuations/parenthsis etc. correctly. Replace EMPCOD with Vehicle CHASSIS Number or Vehicle Identification Number. Use the Logical operator AND as shown, don't combine it with the next expression.