Solved

Select Statement

Posted on 2001-06-19
7
240 Views
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 :)

Vannwms
0
Comment
Question by:vannwms
7 Comments
 
LVL 1

Expert Comment

by:llewelm
Comment Utility
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.
0
 
LVL 5

Expert Comment

by:gbaren
Comment Utility


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

0
 
LVL 33

Accepted Solution

by:
hongjun earned 50 total points
Comment Utility
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;" & _
                   "Pwd=;"
   
    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")
   
    objConn.Close
    Set objRs = Nothing
    Set objConn = Nothing

hongjun
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 33

Expert Comment

by:hongjun
Comment Utility
If you are using other db, then simply change the connection string. See this for more information on connection strings.

http://www.able-consulting.com/ADO_Conn.htm

hongjun
0
 
LVL 33

Expert Comment

by:hongjun
Comment Utility
Note you need to include "Microsoft ActiveX Data Objects" library.

hongjun
0
 
LVL 6

Expert Comment

by:andyclap
Comment Utility
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) & "#"
0
 

Author Comment

by:vannwms
Comment Utility
Great Solution Hongjun! Works like a charm. I appreciated your assistance.

Vannwms  
0

Featured Post

IT, Stop Being Called Into Every Meeting

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

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…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

728 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

14 Experts available now in Live!

Get 1:1 Help Now