Select Statement

Posted on 2001-06-19
Last Modified: 2010-05-02
I want to generate a number.  I know what I wanna do, but just can't find the correct way to jot it down.  Please assist if you can.  

I want to generate a number by counting the records in my database for the current year.  Yes, that's right.  I want to count the number of records in a field called Tdate (01/06/01 - shortdate format) current year only - from a table called Travel only. I am using ADO.

Can you help me?
Your assistance is really appreciated.  

Thanks :)

Question by:vannwms
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

Expert Comment

ID: 6208008
The SQL statement that you want is:

SELECT COUNT(*) FROM TableName WHERE TDate >= '01/01/2001' and TDate <= '12/31/2001'

To do this within ADO:
1) Create a new Connection object
2) Open the connection using the proper connect string (ODBC/DSN or DSN-less)
3) Execute the ExecuteSQL() method of the connection object to get the value returned from this SQL.
4) Use the Recordset.GetFieldValue() method to get the returned value.

Expert Comment

ID: 6208036

select count(*) as Total from table where year(tdate) = year(Now)

LVL 33

Accepted Solution

hongjun earned 50 total points
ID: 6208684
Try this. I am assuming you are using Access, db name is your_db.mdb, table name is your_table.

    Dim objConn As ADODB.Connection
    Dim objRs As ADODB.Recordset
    Dim intCount As Integer
    Dim strSQL As String
    Dim intYear As Integer
    Set objConn = New ADODB.Connection
    objConn.Open "Driver={Microsoft Access Driver (*.mdb)};" & _
                   "Dbq=" & App.Path & "\your_db.mdb;" & _
                   "Uid=Admin;" & _
    intYear = Year(Now())
    strSQL = "select count(tDate) as cnt from your_table where tDate >= #" & DateSerial(intYear, 1, 1) & "# and tdate <= #" & DateSerial(intYear, 12, 31) & "#"
    Set objRs = objConn.Execute(strSQL)
    MsgBox "Count = " & objRs("cnt")
    Set objRs = Nothing
    Set objConn = Nothing

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 33

Expert Comment

ID: 6208689
If you are using other db, then simply change the connection string. See this for more information on connection strings.

LVL 33

Expert Comment

ID: 6208691
Note you need to include "Microsoft ActiveX Data Objects" library.


Expert Comment

ID: 6209820
Could also use between
strSQL = "select count(tDate) as cnt from your_table where tDate between #" & DateSerial(intYear, 1, 1) & "# and #" & DateSerial(intYear, 12, 31) & "#"

Author Comment

ID: 6215611
Great Solution Hongjun! Works like a charm. I appreciated your assistance.


Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
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…

734 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