Showing Count in form field

[randolphoralph]

I have a form with a Total field and I am needing to show a count of all the records with the same Name and Date as the record that is currently selected



I am not sure where to began to get the total or have it change as I go from record to record.

[pbaldy]

You can probably use a DCount:

DLookup Usage Samples

[randolphoralph]

Please pardon my ignorance. I looked at the samples and I am not sure how to get it to work for both a date field and a text field.

[pbaldy]

Did you notice the "mix and match" sample at the bottom? All you need to do is delete the numeric part.

[randolphoralph]

I tried the mix and match and here is what I tried with no luck

=DCount("Name", "EmployeeTable", "[Name] = '" & Forms![EmployeeTable]![Name] & "'" _
& " AND [Date] =#" & Forms![EmployeeTable]![Date] & "#")

[pbaldy]

That looks okay offhand, though I would eliminate the extra concatenation and put the trailing single quote in the next line (personal preference, plus a tic faster). Name and Date are not good field names, since they can conflict with built-in properties and functions. Not sure if that's the problem here, but if it's not too late to change them I would. Can you post the db?

[randolphoralph]

Here is the sample database.

I have changed the names and here is the new code.

=DCount("Employee_Name", "Employee_Table", "[Employee_Name] = '" & Forms![Employee_Table]![Employee_Name] & "'" _
& " AND [Todays_Date] =#" & Forms![Employee_Table]![Todays_Date] & "#")

[pbaldy]

Your names don't contain underscores, they contain spaces, which are also a bad idea. This works as the control source of the textbox:

=DCount("Employee_Name","Employee Table","[Employee Name] = '" & [Forms]![Employee Table]![Employee Name] & "' AND [Todays Date] =#" & [Forms]![Employee Table]![Todays Date] & "#")

[randolphoralph]

ok thanks! I did input that into the Control Source of the textbox and I am receiving a #Error in the text box.

Any thoughts as to why it is giving a error?

[pbaldy]

Not offhand. Here's the working sample back.

[randolphoralph]

I see why I was getting the error. I had not taken all the underscores out. Thank you so much.

[pbaldy]

No problemo.