Solved

Need sql to count records in a table

Posted on 2008-10-28
23
360 Views
Last Modified: 2012-05-05
I have a table (tblCustomers) with a field "DomainName". I want to be able to create a function that will return an integer with the number of records in tblCustomers where the criteria is a specific DomainName value. In other words, for any specified domain name, I want to be able to have the function return the number of records containing that specific domain name within the field DomainName.
0
Comment
Question by:M_Epstein
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 7
  • 5
23 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22827211
This should do the trick:
SELECT COUNT(*)
FROM tblCustomers
WHERE DomainName = 'DomainNameValue'

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22827223
If need to account for variations then, like this and I forgot to name the response.
Broke up the string so you can see how to concatenate to a parameter like @DomainNameValue where the literal value 'DomainNameValue' is.
SELECT COUNT(*) AS CountOfRecords
FROM tblCustomers
WHERE DomainName LIKE '%' + 'DomainNameValue' + '%'

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22827397
" I want to be able to have the function return the number of records containing that specific domain name within the field DomainName."

Since you say function:

This is mwvisa1's code from http:#22827211 and http:#22827223, slightly modified (with the parameter) and wrapped in a function.
--if you want LIKE searches
create function fn_CustomerDomainCount (@Search nvarchar(255))
returns int
as
begin
return (
  SELECT COUNT(*) AS CountOfRecords
  FROM tblCustomers
  WHERE DomainName LIKE '%' + @Search + '%'
  )
end
go
 
 
--if you want LIKE searches
create function fn_CustomerDomainCount (@Search nvarchar(255))
returns int
as
begin
return (
  SELECT COUNT(*) AS CountOfRecords
  FROM tblCustomers
  WHERE DomainName = @Search
  )
end
go

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22827470
Thanks, Brandon.  Wasn't feeling up to all the typing and wasn't sure if the function was in Visual Basic side or DB side.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22827491
No prob.  Just helping out.
0
 

Author Comment

by:M_Epstein
ID: 22835899
I must be missing something. This is for VB6, with an Access 2003 db on the back end, connected by ADO.

I get an error message when I use this code: (no value given for one or more parameters)


Dim ssql As String
Products_con.Open ProductsConString  ' Opens the connection to the db.
ssql = "(SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = msn.com)"

MsgBox Products_con.Execute(ssql)
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22835934
It thinks msn.com is a parameter versus a string literal as you have not put in quotes.

Additional information helps.  Guess I should have asked, but I had a feeling why I didn't post you function code. ;)

Try like this:
Dim ssql As String
Products_con.Open ProductsConString  ' Opens the connection to the db.
ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = ""msn.com"""
 
MsgBox Products_con.Execute(ssql)

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22835947
You can throw in a:

MsgBox ssql

And ensure that the output SQL looks like this:

SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = "msn.com"
0
 

Author Comment

by:M_Epstein
ID: 22836007
The string came out perfect. When the Select Count executed, I get a type mismatch error.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22837011
You don't want to do the msgbox on the execute, just the SQL.



Dim ssql As String
dim rs as new adodb.recordset
Products_con.Open ProductsConString  ' Opens the connection to the db.
ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = ""msn.com"""
 
MsgBox ssql
rs=Products_con.Execute(ssql)
if not rs.eof and not rs.bof then
msgbox rs("CountOfRecords")
end if

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22837110
Good catch, Brandon.

I just copied M_Epstein's code on that paying attention to the quotes on domain name. ;)

Definitely want it how Brandon has it.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22837411
I didn't change anything with the way quotes are handled.  I just assumed that they were using access or something that wanted values in doubles instead of singles.  I change the msgbox to "msgbox ssql" instead of "msgbox products_con.execute (ssql)"

products_con.execute (ssql) returns a recordset object which, I don't believe, can be a msgbox, hence the type mismatch.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22837449
I know.  That is what was wrong with the code before.  It need the double quotes and I added it.  You caught the next error location. ;)
0
 

Author Comment

by:M_Epstein
ID: 22838155
The above code works, thank you. I now want to encase this in a function, so I will replace msn.com with a string variable. I have tried to do this, but I cannot get the syntax corect,and get error messages.

Dim DM As String
DM = "msn.com"

Dim ssql As String
Dim rs As New adodb.Recordset
ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = 'DM'"
MsgBox DM
MsgBox ssql
Set rs = Products_con.Execute(ssql)
If Not rs.EOF And Not rs.BOF Then
MsgBox rs("CountOfRecords")
End If
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22840559
I assume that your connection (products_con) is declared somewhere else in your app and available to this function.  This should be what you need.
public function GetRecordCount(byval DM as string)
 
Dim ssql As String
Dim rs As New adodb.Recordset
ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = '" & DM & "'"
 
Set rs = Products_con.Execute(ssql)
If Not rs.EOF And Not rs.BOF Then
GetRecordCount = rs("CountOfRecords")
End If
rs.close
set rs=nothing
end function

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22840588
You will need to have the DM outside of the quotes SQL statement.
(check the ssql display in message box to make sure the number of quotes are correct, but this should get it)
Dim DM As String
DM = "msn.com"
 
Dim ssql As String
Dim rs As New adodb.Recordset
ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = """ & DM & """"
MsgBox DM
MsgBox ssql
Set rs = Products_con.Execute(ssql)
If Not rs.EOF And Not rs.BOF Then
MsgBox rs("CountOfRecords")
End If

Open in new window

0
 

Author Comment

by:M_Epstein
ID: 22840623
You are both correct. It worked both ways.

Can you explain this syntax to me? I get very confused with the full and half quotes...
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 250 total points
ID: 22840680
To me, it's a matter of what engine you are running against.  If it's access, you need double quotes.

If it's SQL Server, then single quotes will always work.  Double quotes will work, as long as quoted_identifiers are turned off (but you can override that).

So I always use singles against a SQL connection.


I'll walk through the function below.
'To use it
'This will msgbox the record count
msgbox GetRecordCount ("PA")
 
'This will pull the value into a variable
dim i as integer
i= GetRecordCount ("PA")
 
public function GetRecordCount(byval DM as string)
 
Dim ssql As String
'you need a recordset to capture the output of your SQL
Dim rs As New adodb.Recordset
'Your SQL
ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain = '" & DM & "'"
 
'populates rs with the result of products_con.execute
'another method would be -- > rs.open, ssql, products_con
Set rs = Products_con.Execute(ssql)
'tests to ensure that a record was retrieved
If Not rs.EOF And Not rs.BOF Then
'gets the value of the "CountOfRecords" column and assigns it to the function
GetRecordCount = rs("CountOfRecords")
End If
'close the recordset
rs.close
'dispose of the recordset
set rs=nothing
end function

Open in new window

0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 250 total points
ID: 22840686
I always forget with MS ACCESS you can use the single quotes (') for string literals too.  That is the string literal identifier in SQL so I am actual more use to that, but in VB strings are usually in double quotes (") and likewise for ACCESS.

When using " in VB, since the strings are already have " there, you have to use "" to escape the fact that you want a double quote to end up in your resulting string.  

In your case, since ' does work in the SQL side of things, probably easy to just use ' so you don't have to keep escaping it. :)

Hopefully that answered it and wasn't more confusing.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22840715
>>This is for VB6, with an Access 2003 db on the back end, connected by ADO.
I was operating under this statement, so as Brandon says that is why I was using double quotes.  
0
 

Author Comment

by:M_Epstein
ID: 22841042
How would I get a count of all records in the tblCustomers where the field CustomerEmailDomain is not Null (has any entry at all). Thanks.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22841144
Two ways:  Another function that gets a count with no WHERE.  Or interpret a 0 length string passed in as a count all.  I'll show the second.


Calling it like this:

i= GetRecordCount ("")

will return all.
'To use it
'This will msgbox the record count
msgbox GetRecordCount ("PA")
 
'This will pull the value into a variable
dim i as integer
i= GetRecordCount ("PA")
msgbox i
i= GetRecordCount ("")
msgbox i
 
public function GetRecordCount(byval DM as string)
 
Dim ssql As String
'you need a recordset to capture the output of your SQL
Dim rs As New adodb.Recordset
'Your SQL
ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers"
if len(dm)>0 then 
  ssql = ssql & " WHERE CustomerEmailDomain = '" & DM & "'"
end if
 
'populates rs with the result of products_con.execute
'another method would be -- > rs.open, ssql, products_con
Set rs = Products_con.Execute(ssql)
'tests to ensure that a record was retrieved
If Not rs.EOF And Not rs.BOF Then
'gets the value of the "CountOfRecords" column and assigns it to the function
GetRecordCount = rs("CountOfRecords")
End If
'close the recordset
rs.close
'dispose of the recordset
set rs=nothing
end function

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22841988
Alternatively for what it is worth, you could just change this line of existing Function:

ssql = "SELECT COUNT(*) AS CountOfRecords From tblCustomers WHERE CustomerEmailDomain like '" & DM & "' AND CustomerEmailDomain IS NOT NULL;"

Then you can pass * or msn.com.  If value is equal to a domain name the IS NOT NULL won't hurt it finding record as it has to not be NULL to match value passes.  if value is equal to *, then you will now match on all domains as long as they have a value to begin with.  You can also try for blanks.

CustomerEmailDomain IS NOT NULL ==> NZ(CustomerEmailDomain, "") <> ""
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question