Hi,

I need some advice.

One of the projects I have on the go has a list of names in a database. I need to be able to count the number of times a name appears within the database.

e.g.

the adodc is named adoPerson.

The names will be in a combo box (called cboPersonSearch)

When someone selects the name the text box will be populated with the number of time the name appears.

combobox person name selected = Fred

textbox text = 36

Can anyone help?

Please.

I am away on holiday for the next few days so I will check back later next week.

Thanks in advance guys.

This message was edited by doofusboy at 2005-7-29 6:0:39

---

: Hi,
:
: I need some advice.
:
: One of the projects I have on the go has a list of names in a database. I need to be able to count the number of times a name appears within the database.
:
: e.g.
:
: the adodc is named adoPerson.
:
: The names will be in a combo box (called cboPersonSearch)
:
: When someone selects the name the text box will be populated with the number of time the name appears.
:
: combobox person name selected = Fred
:
: textbox text = 36
:
: Can anyone help?
:
: Please.
:
: I am away on holiday for the next few days so I will check back later next week.
:
: Thanks in advance guys.
:
:
:
:
:
:
I'm going to assume you know how to connect to a database and return a recordset to your project that you can then use to populate your textbox.

The SQL query you need to return the number you are looking for should be something like this:

SELECT Count(name_field)
FROM database_name.table_name
WHERE name_field = [value from your combobox]

So you can build your SQL statement and assign it to a string variable that then gets executed by your connection object like this:

Dim strSQL As String

strSQL = "SELECT Count(name_field) FROM database_name.table_name WHERE name_field = '" & Combo1.List(Combo1.ListIndex) & "';"

The recordset returned when your strSQL is executed against the database by your connection object will contain the number you want to put in your textbox.

**********************************

Definitions for above:

database_name = name of the database file you are using
table_name = name of table in database that has list of names
name_field = column name in database that contains list of names
Combo1.List(Combo1.ListIndex) = name user selects in the combobox

: This message was edited by doofusboy at 2005-7-29 6:0:39

---

: : Hi,
: :
: : I need some advice.
: :
: : One of the projects I have on the go has a list of names in a database. I need to be able to count the number of times a name appears within the database.
: :
: : e.g.
: :
: : the adodc is named adoPerson.
: :
: : The names will be in a combo box (called cboPersonSearch)
: :
: : When someone selects the name the text box will be populated with the number of time the name appears.
: :
: : combobox person name selected = Fred
: :
: : textbox text = 36
: :
: : Can anyone help?
: :
: : Please.
: :
: : I am away on holiday for the next few days so I will check back later next week.
: :
: : Thanks in advance guys.
: :
: :
: :
: :
: :
: :
: I'm going to assume you know how to connect to a database and return a recordset to your project that you can then use to populate your textbox.
:
: The SQL query you need to return the number you are looking for should be something like this:
:
: SELECT Count(name_field)
: FROM database_name.table_name
: WHERE name_field = [value from your combobox]
:
: So you can build your SQL statement and assign it to a string variable that then gets executed by your connection object like this:
:
: Dim strSQL As String
:
: strSQL = "SELECT Count(name_field) FROM database_name.table_name WHERE name_field = '" & Combo1.List(Combo1.ListIndex) & "';"
:
: The recordset returned when your strSQL is executed against the database by your connection object will contain the number you want to put in your textbox.
:
: **********************************
:
: Definitions for above:
:
: database_name = name of the database file you are using
: table_name = name of table in database that has list of names
: name_field = column name in database that contains list of names
: Combo1.List(Combo1.ListIndex) = name user selects in the combobox
:
:
:
Thanks doofusboy .... I can see that will work for a single field, but unfortunatly the table I want to search will have the name fred in up to 15 diffrent fields.
Any idea ?

: : This message was edited by doofusboy at 2005-7-29 6:0:39

---

: : : Hi,
: : :
: : : I need some advice.
: : :
: : : One of the projects I have on the go has a list of names in a database. I need to be able to count the number of times a name appears within the database.
: : :
: : : e.g.
: : :
: : : the adodc is named adoPerson.
: : :
: : : The names will be in a combo box (called cboPersonSearch)
: : :
: : : When someone selects the name the text box will be populated with the number of time the name appears.
: : :
: : : combobox person name selected = Fred
: : :
: : : textbox text = 36
: : :
: : : Can anyone help?
: : :
: : : Please.
: : :
: : : I am away on holiday for the next few days so I will check back later next week.
: : :
: : : Thanks in advance guys.
: : :
: : :
: : :
: : :
: : :
: : :
: : I'm going to assume you know how to connect to a database and return a recordset to your project that you can then use to populate your textbox.
: :
: : The SQL query you need to return the number you are looking for should be something like this:
: :
: : SELECT Count(name_field)
: : FROM database_name.table_name
: : WHERE name_field = [value from your combobox]
: :
: : So you can build your SQL statement and assign it to a string variable that then gets executed by your connection object like this:
: :
: : Dim strSQL As String
: :
: : strSQL = "SELECT Count(name_field) FROM database_name.table_name WHERE name_field = '" & Combo1.List(Combo1.ListIndex) & "';"
: :
: : The recordset returned when your strSQL is executed against the database by your connection object will contain the number you want to put in your textbox.
: :
: : **********************************
: :
: : Definitions for above:
: :
: : database_name = name of the database file you are using
: : table_name = name of table in database that has list of names
: : name_field = column name in database that contains list of names
: : Combo1.List(Combo1.ListIndex) = name user selects in the combobox
: :
: :
: :
: Thanks doofusboy .... I can see that will work for a single field, but unfortunatly the table I want to search will have the name fred in up to 15 diffrent fields.
: Any idea ?
:
:
:

: : : This message was edited by doofusboy at 2005-7-29 6:0:39

---

: : : : Hi,
: : : :
: : : : I need some advice.
: : : :
: : : : One of the projects I have on the go has a list of names in a database. I need to be able to count the number of times a name appears within the database.
: : : :
: : : : e.g.
: : : :
: : : : the adodc is named adoPerson.
: : : :
: : : : The names will be in a combo box (called cboPersonSearch)
: : : :
: : : : When someone selects the name the text box will be populated with the number of time the name appears.
: : : :
: : : : combobox person name selected = Fred
: : : :
: : : : textbox text = 36
: : : :
: : : : Can anyone help?
: : : :
: : : : Please.
: : : :
: : : : I am away on holiday for the next few days so I will check back later next week.
: : : :
: : : : Thanks in advance guys.
: : : :
: : : :
: : : :
: : : :
: : : :
: : : :
: : : I'm going to assume you know how to connect to a database and return a recordset to your project that you can then use to populate your textbox.
: : :
: : : The SQL query you need to return the number you are looking for should be something like this:
: : :
: : : SELECT Count(name_field)
: : : FROM database_name.table_name
: : : WHERE name_field = [value from your combobox]
: : :
: : : So you can build your SQL statement and assign it to a string variable that then gets executed by your connection object like this:
: : :
: : : Dim strSQL As String
: : :
: : : strSQL = "SELECT Count(name_field) FROM database_name.table_name WHERE name_field = '" & Combo1.List(Combo1.ListIndex) & "';"
: : :
: : : The recordset returned when your strSQL is executed against the database by your connection object will contain the number you want to put in your textbox.
: : :
: : : **********************************
: : :
: : : Definitions for above:
: : :
: : : database_name = name of the database file you are using
: : : table_name = name of table in database that has list of names
: : : name_field = column name in database that contains list of names
: : : Combo1.List(Combo1.ListIndex) = name user selects in the combobox
: : :
: : :
: : :
: : Thanks doofusboy .... I can see that will work for a single field, but unfortunatly the table I want to search will have the name fred in up to 15 diffrent fields.
: : Any idea ?
: :
: :
: :
:
:
In that case, I would probably do something like this:

SELECT 1st_name_field, 2nd_name_field, 3rd_name_field
FROM database_name.table_name
WHERE 1st_name_field = [value from your combobox]
AND 2nd_name_field = [value from your combobox]
AND 3rd_name_field = [value from your combobox]

and then get the total number of records by stepping through the recordset that is returned and increasing a counter variable that you then assign to your textbox.