Solved

Need sql to count records in a table

Posted on 2008-10-28
23
354 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
  • 11
  • 7
  • 5
23 Comments
 
LVL 59

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 59

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
 
LVL 59

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 59

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 59

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 59

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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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 59

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 59

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 59

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 59

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 59

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now