Hi, I have a database in access. I need the database to look through a
table which consists of many fields one of which is customer ID. It
needs to count how many times a certain customer ID appears (as given
by a form). The number of times the customer ID appears in the table
then needs to be displayed in a text box in the same form.

I would like to do this with a query if possible. If it needs VBA I
need specific code as not very good at it! Can give more info or make
database available online -just ask

thanks, matt

Hi Matt,

Say you have a form with two text boxes.

txtCustomerNumber - contains the Customer Number
txtBookingCount - the text box where you want the count to appear.

Set the Control Source for txtBookingCount to this:
=DCount("CustomerID","Booking","CustomerID = " &
CLng([txtCustomerNumber]))

This returns the count of CustomerID from the table called Booking
where the CustomerID matches the Customer Number entered on your form.

Additional Resources:
For a full explanation, look in Access Help under DCount.

- Hammer

---

Clarification of Answer by hammer-ga on 15 Apr 2003 07:42 PDT

Matt,

As usual, you need to unwrap the line with the Control Source.

- Hammer

---

Request for Answer Clarification by mattie54-ga on 15 Apr 2003 08:46 PDT

hi, I put in the code and modified it appropriately but I get #error
appearing in the box. I have tried messing around with it a bit and it
still comes up error or #name.

Can you suggest anything else? can i post it for you to check?

thanks, matt

---

Clarification of Answer by hammer-ga on 15 Apr 2003 08:51 PDT

If txtCustomerNumber is blank or does not contain a number, you will
get #Error. you get #Name if you use a different name than you text
box actually has.

- Hammer

---

Request for Answer Clarification by mattie54-ga on 15 Apr 2003 08:52 PDT

I tried this!

=DCount("CustomerID","Booking","CustomerID = [CustomerID]")

and this works a bit better but it always says 13!!! This also makes
the CustomerID field in the form dissapear sometimes!

thanks for your help,matt

---

Clarification of Answer by hammer-ga on 15 Apr 2003 08:57 PDT

Yours won't work. You are asking Access for a count where CustomerID
equals the literal text "[CustomerID]". The results will be
unpredictable.

- Hammer

---

Clarification of Answer by hammer-ga on 15 Apr 2003 08:58 PDT

Try this. I'm guessing because I'm not of the specifics of your form.

=DCount("CustomerID","Booking","CustomerID = " & [CustomerID])

Look at DCount and understand what the arguments are doing. That will
make it easier for you to adjust the names to your situation.

- Hammer

---

Request for Answer Clarification by mattie54-ga on 15 Apr 2003 09:03 PDT

Thank you, It seems to be working!! I'll give it a good test then come
and give rating!!

Thanks for your help (again!)

Matt

---

Clarification of Answer by hammer-ga on 15 Apr 2003 09:40 PDT

OK.

- Hammer

---

Request for Answer Clarification by mattie54-ga on 16 Apr 2003 04:00 PDT

Putting on another question now please keep your eye out :-)

---

Clarification of Answer by hammer-ga on 16 Apr 2003 04:29 PDT

Will do.

- Hammer

Excellent worked perfectly. thanks very much!!