Count

[Tom Nowak]

I am wrting some VB in an Access 2003 database, and I want to get a COUNT to
display in a msgbox. Here is a portion of my code:

'Loop through recordset
Do Until rst1.EOF
str1 = "'%" & rst1("phrase") & "%'"
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = _
"SELECT Count(*) FROM dec2007 " & _
"WHERE prob_summ LIKE " & str1
.CommandType = adCmdText
.Execute
End With

MsgBox ("The number of records that contain " & rst1("phrase") & _
" is " & int1)

I am not sure how to get the number of records found into the int1 variable.
Please Help.

 

[Dirk Goldgar]

I am wrting some VB in an Access 2003 database, and I want to get a COUNT
to
display in a msgbox. Here is a portion of my code:

'Loop through recordset
Do Until rst1.EOF
str1 = "'%" & rst1("phrase") & "%'"
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = _
"SELECT Count(*) FROM dec2007 " & _
"WHERE prob_summ LIKE " & str1
.CommandType = adCmdText
.Execute
End With

MsgBox ("The number of records that contain " & rst1("phrase") & _
" is " & int1)

I am not sure how to get the number of records found into the int1
variable.
Please Help.

Probably the simplest method ios to use the DCount function instead of
messing with ADO. This approach would look like this:

int1 = DCount("*", "dec2007", _
"prob_summ LIKE '*' & rst1("phrase") & "*'")

Note that I changed the wild-card character from '%' to '*' based on the
assumption that this is happening in an .mdb or .accdb file with default
settings (ADO uses '%' as a wild-card). However, if this code is running in
an .adp, I think you'll have to change the wild-card back to '%'.

 

[Tom Nowak]

When I use this I receive Compile Error, Expected List Seperator or )

 

[Dirk Goldgar]

When I use this I receive Compile Error, Expected List Seperator or )

D'oh! I had a single quote in one spot where I needed a double quote. Try
this:


int1 = DCount("*", "dec2007", _
"prob_summ LIKE '*" & rst1("phrase") & "*'")

 

[Tom Nowak]

Thank you so much for the help. Worked great.

I have one more question. In the next part of the routine, I want to use
the count and one more field and add these to a table. The code I'm using is
as follows:

With cmd2
.ActiveConnection = CurrentProject.Connection
.CommandText = "INSERT INTO stats (topic, ticketamt)
VALUES(rst1!phrase, int1.ToString)"
.CommandType = adCmdText
.Execute
End With

When I use this I receive:

No value given for one or more required parameters.

I think I am not specifing the VALUES correctly. Please help.

 

[Dirk Goldgar]

Thank you so much for the help. Worked great.

I have one more question. In the next part of the routine, I want to use
the count and one more field and add these to a table. The code I'm using
is
as follows:

With cmd2
.ActiveConnection = CurrentProject.Connection
.CommandText = "INSERT INTO stats (topic, ticketamt)
VALUES(rst1!phrase, int1.ToString)"
.CommandType = adCmdText
.Execute
End With

When I use this I receive:

No value given for one or more required parameters.

I think I am not specifing the VALUES correctly. Please help.

Try this:

.CommandText = _
"INSERT INTO stats (topic, ticketamt) VALUES('" & _
rst1!phrase & "', " & int1 & ")"

That assumes that rst1!phrase is text and won't contain a single-quote
character ('). If the phrase might contain a single-quote, but will not
contain a double-quote ("), then use this:

.CommandText = _
"INSERT INTO stats (topic, ticketamt) VALUES(" & _
Chr(34) & rst1!phrase & Chr(34) & ", " & int1 & ")"

If it might contain either of those two quotes, you can use code like this:

Const Q As String = """"
Const QQ AS String = Q & Q

.CommandText = _
"INSERT INTO stats (topic, ticketamt) VALUES(" & _
Q & Replace(rst1!phrase, Q, QQ) & Q & ", " & int1 & ")"

As you can see, you need to embed the literal values of your variables into
the SQL string, and text values have to be properly quoted, which can get
tricky if they contain quote characters. Note that VBA doesn't require
calling a .ToString method to convert the value int1 to a string. Just
concatenating it to another string is sufficient (though a CStr() function
and a Str() function exist to force such conversions, with slightly
different behaviors).

 

[Tom Nowak]

That worked. Thanks again for all your help.

Try this:

.CommandText = _
"INSERT INTO stats (topic, ticketamt) VALUES('" & _
rst1!phrase & "', " & int1 & ")"

That assumes that rst1!phrase is text and won't contain a single-quote
character ('). If the phrase might contain a single-quote, but will not
contain a double-quote ("), then use this:

.CommandText = _
"INSERT INTO stats (topic, ticketamt) VALUES(" & _
Chr(34) & rst1!phrase & Chr(34) & ", " & int1 & ")"

If it might contain either of those two quotes, you can use code like this:

Const Q As String = """"
Const QQ AS String = Q & Q

.CommandText = _
"INSERT INTO stats (topic, ticketamt) VALUES(" & _
Q & Replace(rst1!phrase, Q, QQ) & Q & ", " & int1 & ")"

As you can see, you need to embed the literal values of your variables into
the SQL string, and text values have to be properly quoted, which can get
tricky if they contain quote characters. Note that VBA doesn't require
calling a .ToString method to convert the value int1 to a string. Just
concatenating it to another string is sufficient (though a CStr() function
and a Str() function exist to force such conversions, with slightly
different behaviors).

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Jamie Collins]

'Loop through recordset
Do Until rst1.EOF
str1 = "'%" & rst1("phrase") & "%'"
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = _
"SELECT Count(*) FROM dec2007 " & _
"WHERE prob_summ LIKE " & str1
.CommandType = adCmdText
.Execute
End With

MsgBox ("The number of records that contain " & rst1("phrase") & _
" is " & int1)

Instead of messing about in loops, use SQL code to create a JOIN
between your table 'dec2007' and your other table (the one with the
column named 'phrase') e.g. something like:

SELECT T1.phrase, COUNT(*)
FROM YourOtherTable AS T1, dec2007 AS T2
WHERE T2.prob_summ LIKE '%' & T1.phrase & '%'
GROUP BY T1.phrase;

Jamie.

--