Problem in VBA!!

[FROFESSOR]

Hi I have a list of names and other information in a excel list that I'm preparing for a house party. What I want to do is have the results shown in the immediate window. The information I want is the Name and Sex of the Male only. How do I do that in VBA.

Below I have the code that I have till now!!!
Please help!!!



Sub Query2()
Dim database As ADODB.Connection
Dim students As ADODB.Recordset
Dim SQL As String

Set database = ConnectDB
' define filter
SQL = "SELECT * FROM Data"

' start and run query
Set students = database.Execute(SQL)

' display in Immediate Window
Do Until students.EOF
Debug.Print students!Name, students!sex
students.MoveNext
Loop

' copy results to excel
Range("F2").CopyFromRecordset students

' close properly
students.Close
database.Close
End Sub

 

[namliam]

of the Male only.

Appart from that part your code seems pretty OK to me?
Why not simply return only the male records in the query? If that is all you require why fetch more?

 

[FROFESSOR]

I have already figured it out.
This is what I did!!

Function ConnectDB() As ADODB.Connection
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=Excel 8.0;"
.Open
End With

Set ConnectDB = cn
End Function

Sub Query1()
Dim database As ADODB.Connection
Dim students As ADODB.Recordset
Dim SQL As String

Set database = ConnectDB
' define filter
SQL = "SELECT * FROM Data"

' start and run query
Set students = database.Execute(SQL)

' display in Immediate Window
Do Until students.EOF
Debug.Print students!Name, students!age
students.MoveNext
Loop

' copy results to excel
Range("F2").CopyFromRecordset students

' close properly
students.Close
database.Close
End Sub

Sub Query2()
Dim database As ADODB.Connection
Dim students As ADODB.Recordset
Dim SQL As String

Set database = ConnectDB
' define filter
SQL = "SELECT * FROM Data WHERE sex='M'"

' start and run query
Set students = database.Execute(SQL)

' display in Immediate Window
Do Until students.EOF
Debug.Print students!Name, students!sex
students.MoveNext
Loop

' copy results to excel
Range("F2").CopyFromRecordset students

' close properly
students.Close
database.Close
End Sub

Sub Query3()
Dim database As ADODB.Connection
Dim students As ADODB.Recordset
Dim SQL As String

Set database = ConnectDB
' define filter
SQL = "SELECT * FROM Data WHERE sex='F'"

' start and run query
Set students = database.Execute(SQL)

' display in Immediate Window

Do Until students.EOF
Debug.Print students!Name, students!sex
students.MoveNext
Loop

' copy results to excel
Range("F2").CopyFromRecordset students

' close properly
students.Close
database.Close
End Sub

Sub Query4()
Dim database As ADODB.Connection
Dim students As ADODB.Recordset
Dim SQL As String

Set database = ConnectDB
' define filter

SQL = "SELECT * FROM Data WHERE age<18"
' start and run query
Set students = database.Execute(SQL)

' display in Immediate Window

Do Until students.EOF
Debug.Print students!Name, students!age
students.MoveNext
Loop

' copy results to excel
Range("F2").CopyFromRecordset students

' close properly
students.Close
database.Close
End Sub

Sub Query5()
Dim database As ADODB.Connection
Dim students As ADODB.Recordset
Dim SQL As String

Set database = ConnectDB
' define filter

SQL = "SELECT * FROM Data WHERE age>18"
' start and run query
Set students = database.Execute(SQL)

' display in Immediate Window

Do Until students.EOF
Debug.Print students!Name, students!age
students.MoveNext
Loop

' copy results to excel
Range("F2").CopyFromRecordset students

' close properly
students.Close
database.Close
End Sub