By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,837 Members | 1,206 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,837 IT Pros & Developers. It's quick & easy.

Return field values from a query using a Recordset in VBA

P: n/a
I am trying to extract the values from a query using VBA.
Specifically, I want to concatenate all the values of the "rosEmail"
field from query "qselRosterEmailList" into one string (strEmails). I
can get one record's result by using the DLookup fuction, of course,
but I want to get every record's value. To do this, I believe I need
to build a recordset, but I do not know how.

I've read about doing it using DAO in Access 97, but I am using Access
2002. Also, I do not want to register any extra libraries, since I'll
be sharing the Access database with others and do not want those users
to have to register libraries as well.

The final result should be something like:
strEmails = "pe*****@email.com, pe*****@email.com, pe*****@email.com,
pe*****@email.com"

Thanks for your help,
David

Nov 13 '05 #1
Share this Question
Share on Google+
19 Replies


P: n/a
Unfortunately, this code does not work. I get an error saying
"User-defined type not defined" and it doesn't go farther than the
first line of code. Although I'm not sure, I think this is because
it's DAO and not ADO. You wrote "Oh, so convert it to ADO. Or use late
binding" - can you elaborate on how to do this? I don't know how to
return the Recordset in ADO.

Also, where do I define the query that feeds this data? (strQuery =
qselRosterEmailList or something like that)

Thanks

Nov 13 '05 #2

P: n/a

bdt513 wrote:
I am trying to extract the values from a query using VBA.
Specifically, I want to concatenate all the values of the "rosEmail"
field from query "qselRosterEmailList" into one string (strEmails). I can get one record's result by using the DLookup fuction, of course,
but I want to get every record's value. To do this, I believe I need
to build a recordset, but I do not know how.

I've read about doing it using DAO in Access 97, but I am using Access 2002. Also, I do not want to register any extra libraries, since I'll be sharing the Access database with others and do not want those users to have to register libraries as well.

The final result should be something like:
strEmails = "pe*****@email.com, pe*****@email.com, pe*****@email.com,
pe*****@email.com"

Thanks for your help,
David


Umm... where's the code?
You basically declare a string variable and then inside looping through
the records, you append each value to the string.

This is crap, but it works... assumes you get your info from a query...
Option Compare Database

Public Function AddressList(ByVal strQuery As String) As String
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim strList As String

Set db = DBEngine(0)(0)
Set qd = db.QueryDefs(strQuery)
Set rs = qd.OpenRecordset

Do Until rs.EOF
strList = strList & ", " & rs.Fields(2).Value
rs.MoveNext
Loop

AddressList = Right$(strList, Len(strList) - 2)

rs.Close
Set rs = Nothing
Set qd = Nothing
Set db = Nothing
End Function

query:
SELECT tblPeople.FirstName, tblPeople.LastName, Left$([Firstname],1) &
[LastName] & "@mail.net" AS Expr1
FROM tblPeople;
Oh, so convert it to ADO. Or use late binding.

Nov 13 '05 #3

P: n/a
Here is the Recordset code I have now, and this produces an error at
the rs.Open line. "rosEmail" is the field I want and
"qselRosterEmailList" is the query from which I want to read.

Private Sub cmdGenerateEmailList_Click()

Dim temp, list As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "qselRosterEmailList"

Do While Not rs.EOF
temp = rs!rosEmail
list = temp & ", "
rs.MoveNext
Loop

rs.Close

End Sub

Nov 13 '05 #4

P: n/a
I think rs.Open "qselRosterEmailList" needs a connection.
I often use the connection of the current project

rs.Open "qselRosterEmailList", CurrentProject.Connection

And you might want to add further arguments after the conenction about the
way the recordset must be opened.

Kees de Boer

bdt513 wrote:
Here is the Recordset code I have now, and this produces an error at
the rs.Open line. "rosEmail" is the field I want and
"qselRosterEmailList" is the query from which I want to read.

Private Sub cmdGenerateEmailList_Click()

Dim temp, list As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "qselRosterEmailList"

Do While Not rs.EOF
temp = rs!rosEmail
list = temp & ", "
rs.MoveNext
Loop

rs.Close

End Sub

Nov 13 '05 #5

P: n/a
C.P.J. de Boer wrote:

Did you work in The Hague in 1986?
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Nov 13 '05 #6

P: n/a
I've modified the code, but I'm now getting a different error. For
some reason it does not like using a query as the Recordset on the
rs.Open command. When I use a table, I'm fine, but I need to pull this
data from a query. What do I need to do in order to allow me to use a
query to feed this Recordset? (I receive the error message, "No value
given for one or more parameters")

Here's the code as it stands now...

Sub EmailList()

Dim rs As ADODB.Recordset
Dim strEmails As String

Set rs = New ADODB.Recordset

rs.Open "Select * FROM qselRosterEmailList;", CurrentProject.Connection

While Not rs.EOF
strEmails = strEmails & ", " & rs.Fields(0).Value
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

End Sub

Nov 13 '05 #7

P: n/a
> rs.Open "Select * FROM qselRosterEmailList", CurrentProject.Connection

As counterintuitive as it sounds, you cannot actually use
CurrentProject.Connection in this manner; you must decalre and use a
Connection object (and no, I don't know why):

Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open "Select * FROM qselRosterEmailList", cnxn

Your code should run as above, however, I advise you to get in the habit of
specifying the parameters after the Connection, otherwise, Access uses the
defaults for the first two parameters, then figures the final one out on its
own (which slows down your code).

Filling in the default values, you have opened up this type of Recordset
/cursor:

rs.Open "Select * FROM qselRosterEmailList", cnxn, adOpenForwardOnly,
adLockReadOnly

This is fine in your situation, but you will need other types of cursors
(Keyset, LockOptimistic)

Also, you should specify the final parameter, in order to speed up the code:
adCmdText for queries, adCmdTableDirect for tables, or adCmdStoredProc for
Stored Procedures

rs.Open "Select * FROM qselRosterEmailList", cnxn, adOpenForwardOnly,
adLockReadOnly, adCmdText
Darryl Kerkeslager
"bdt513" <BD****@gmail.com> wrote:
I've modified the code, but I'm now getting a different error. For
some reason it does not like using a query as the Recordset on the
rs.Open command. When I use a table, I'm fine, but I need to pull this
data from a query. What do I need to do in order to allow me to use a
query to feed this Recordset? (I receive the error message, "No value
given for one or more parameters")

Here's the code as it stands now...

Sub EmailList()

Dim rs As ADODB.Recordset
Dim strEmails As String

Set rs = New ADODB.Recordset

rs.Open "Select * FROM qselRosterEmailList;", CurrentProject.Connection

While Not rs.EOF
strEmails = strEmails & ", " & rs.Fields(0).Value
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

End Sub

Nov 13 '05 #8

P: n/a
That was a very helpful post, and I appreciate the advice. Still,
however, the code does not work. I receive the same error on the
rs.Open line:
"No value given for one or more parameters"

When I run the same code but use a table instead of a query, the code
works. Short of using a make table, how can I use the query
(qselRosterEmailList) to populate the Recordset? [The query does work
okay on its own. It opens properly and returns the right information]

Here's the code that's in my module:
-----------------------------
Sub EmailList()

Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open "Select * FROM qselRosterEmailList", cnxn, adOpenForwardOnly,
adLockOptimistic, adCmdText

[...other stuff...]

End Sub
-----------------------------

When I use the follow line, which looks to a table instead of a query,
I have no problem:
rs.Open "Select * FROM tblRoster", cnxn, adOpenForwardOnly,
adLockOptimistic, adCmdText

Thanks again for you help,
David

Nov 13 '05 #9

P: n/a
Unfortunately, this code does not work. I get an error saying
"User-defined type not defined" and it doesn't go farther than the
first line of code. Although I'm not sure, I think this is because
it's DAO and not ADO. You wrote "Oh, so convert it to ADO. Or use late
binding" - can you elaborate on how to do this? I don't know how to
return the Recordset in ADO.

Also, where do I define the query that feeds this data? (strQuery =
qselRosterEmailList or something like that)

Thanks

Nov 13 '05 #10

P: n/a
Here is the Recordset code I have now, and this produces an error at
the rs.Open line. "rosEmail" is the field I want and
"qselRosterEmailList" is the query from which I want to read.

Private Sub cmdGenerateEmailList_Click()

Dim temp, list As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "qselRosterEmailList"

Do While Not rs.EOF
temp = rs!rosEmail
list = temp & ", "
rs.MoveNext
Loop

rs.Close

End Sub

Nov 13 '05 #11

P: n/a
I think rs.Open "qselRosterEmailList" needs a connection.
I often use the connection of the current project

rs.Open "qselRosterEmailList", CurrentProject.Connection

And you might want to add further arguments after the conenction about the
way the recordset must be opened.

Kees de Boer

bdt513 wrote:
Here is the Recordset code I have now, and this produces an error at
the rs.Open line. "rosEmail" is the field I want and
"qselRosterEmailList" is the query from which I want to read.

Private Sub cmdGenerateEmailList_Click()

Dim temp, list As String
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
rs.Open "qselRosterEmailList"

Do While Not rs.EOF
temp = rs!rosEmail
list = temp & ", "
rs.MoveNext
Loop

rs.Close

End Sub

Nov 13 '05 #12

P: n/a
C.P.J. de Boer wrote:

Did you work in The Hague in 1986?
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Nov 13 '05 #13

P: n/a
I've modified the code, but I'm now getting a different error. For
some reason it does not like using a query as the Recordset on the
rs.Open command. When I use a table, I'm fine, but I need to pull this
data from a query. What do I need to do in order to allow me to use a
query to feed this Recordset? (I receive the error message, "No value
given for one or more parameters")

Here's the code as it stands now...

Sub EmailList()

Dim rs As ADODB.Recordset
Dim strEmails As String

Set rs = New ADODB.Recordset

rs.Open "Select * FROM qselRosterEmailList;", CurrentProject.Connection

While Not rs.EOF
strEmails = strEmails & ", " & rs.Fields(0).Value
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

End Sub

Nov 13 '05 #14

P: n/a
> rs.Open "Select * FROM qselRosterEmailList", CurrentProject.Connection

As counterintuitive as it sounds, you cannot actually use
CurrentProject.Connection in this manner; you must decalre and use a
Connection object (and no, I don't know why):

Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open "Select * FROM qselRosterEmailList", cnxn

Your code should run as above, however, I advise you to get in the habit of
specifying the parameters after the Connection, otherwise, Access uses the
defaults for the first two parameters, then figures the final one out on its
own (which slows down your code).

Filling in the default values, you have opened up this type of Recordset
/cursor:

rs.Open "Select * FROM qselRosterEmailList", cnxn, adOpenForwardOnly,
adLockReadOnly

This is fine in your situation, but you will need other types of cursors
(Keyset, LockOptimistic)

Also, you should specify the final parameter, in order to speed up the code:
adCmdText for queries, adCmdTableDirect for tables, or adCmdStoredProc for
Stored Procedures

rs.Open "Select * FROM qselRosterEmailList", cnxn, adOpenForwardOnly,
adLockReadOnly, adCmdText
Darryl Kerkeslager
"bdt513" <BD****@gmail.com> wrote:
I've modified the code, but I'm now getting a different error. For
some reason it does not like using a query as the Recordset on the
rs.Open command. When I use a table, I'm fine, but I need to pull this
data from a query. What do I need to do in order to allow me to use a
query to feed this Recordset? (I receive the error message, "No value
given for one or more parameters")

Here's the code as it stands now...

Sub EmailList()

Dim rs As ADODB.Recordset
Dim strEmails As String

Set rs = New ADODB.Recordset

rs.Open "Select * FROM qselRosterEmailList;", CurrentProject.Connection

While Not rs.EOF
strEmails = strEmails & ", " & rs.Fields(0).Value
rs.MoveNext
Wend
rs.Close
Set rs = Nothing

End Sub

Nov 13 '05 #15

P: n/a
That was a very helpful post, and I appreciate the advice. Still,
however, the code does not work. I receive the same error on the
rs.Open line:
"No value given for one or more parameters"

When I run the same code but use a table instead of a query, the code
works. Short of using a make table, how can I use the query
(qselRosterEmailList) to populate the Recordset? [The query does work
okay on its own. It opens properly and returns the right information]

Here's the code that's in my module:
-----------------------------
Sub EmailList()

Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open "Select * FROM qselRosterEmailList", cnxn, adOpenForwardOnly,
adLockOptimistic, adCmdText

[...other stuff...]

End Sub
-----------------------------

When I use the follow line, which looks to a table instead of a query,
I have no problem:
rs.Open "Select * FROM tblRoster", cnxn, adOpenForwardOnly,
adLockOptimistic, adCmdText

Thanks again for you help,
David

Nov 13 '05 #16

P: n/a
> Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset

rs.Open "Select * FROM qselRosterEmailList", cnxn, adOpenForwardOnly,
adLockOptimistic, adCmdText
The code above is correct. So, the problem is with the query. I would
start asking questions like:

Does the query need parameters that it is not finding? [What *is* the SQL?]

Am I sure that I spelled the query name right?

Is the query that doesn't work in the same mdb as the table that does?
Darryl Kerkeslager
"bdt513" <BD****@gmail.com> wrote: I receive the same error on the
rs.Open line:
"No value given for one or more parameters"

When I run the same code but use a table instead of a query, the code
works. Short of using a make table, how can I use the query
(qselRosterEmailList) to populate the Recordset? [The query does work
okay on its own. It opens properly and returns the right information]
[snip] When I use the follow line, which looks to a table instead of a query,
I have no problem:
rs.Open "Select * FROM tblRoster", cnxn, adOpenForwardOnly,
adLockOptimistic, adCmdText

Nov 13 '05 #17

P: n/a
The query is spelled correctly (I understand that it's worth
double-checking, however).
Does the query need parameters? I don't think so, since it works fine
when run within Access. All queries and tables are within the same
Access database.

The SQL statement looks at check boxes on a form and compares them with
values in a query. If any of the conditions are true, a value of
"show" is returned, and that record appears in the result.

The SQL is below:
SELECT qselRosterActive.rosEmail
FROM qselRosterActive
WHERE (((IIf((([CurrentRole]="Sabre" And
[forms]![frmRosterEmail]![chkSabre]=True) Or ([CurrentRole]="Epee" And
[forms]![frmRosterEmail]![chkEpee]=True) Or ([CurrentRole]="Foil" And
[forms]![frmRosterEmail]![chkFoil]=True) Or ([CurrentRole]="Staff" And
[forms]![frmRosterEmail]![chkStaff]=True)) And (([rosGender]="M" And
[forms]![frmRosterEmail]![chkMen]=True) Or ([rosGender]="W" And
[forms]![frmRosterEmail]![chkWomen]=True)),"show"))="show"))
ORDER BY qselRosterActive.rosEmail;

This SQL referenced a previous query (qselRosterActive), whose SQL is
as follows:
SELECT tblRoster.*, tblRosterRole.rolRole AS CurrentRole
FROM tblRoster INNER JOIN tblRosterRole ON tblRoster.rosID =
tblRosterRole.rolRosID
WHERE (((tblRoster.rosActiveStatus)=True) AND
((tblRosterRole.rolDateEnd) Like #12/31/9999#))
ORDER BY tblRoster.rosNameFull;

Again, thank you very much for your help. I've long struggled to learn
about using Recordsets and am excited about applying knowledge now.
-David

Nov 13 '05 #18

P: n/a
My first thought (and really my only useful one) is that the form
frmRosterEmail is not accessible dwhen the query is run. Were it me, I would
take these steps:

You've already run this simple query, which works (and presumable returns a
value)

rs.Open "Select * FROM tblRoster", cnxn, adOpenForwardOnly,
adLockOptimistic, adCmdText

So run the Open statement with qselRosterActive, your other query. You can
run it either as:

rs.Source = "SELECT tblRoster.*, tblRosterRole.rolRole AS CurrentRole
FROM tblRoster INNER JOIN tblRosterRole ON tblRoster.rosID =
tblRosterRole.rolRosID
WHERE (((tblRoster.rosActiveStatus)=True) AND
((tblRosterRole.rolDateEnd) Like #12/31/9999#))
ORDER BY tblRoster.rosNameFull"
rs.Open , cnxn, adOpenForwardOnly, adLockOptimistic, adCmdText

or

rs.Open "SELECT tblRoster.*, tblRosterRole.rolRole AS CurrentRole
FROM tblRoster INNER JOIN tblRosterRole ON tblRoster.rosID =
tblRosterRole.rolRosID
WHERE (((tblRoster.rosActiveStatus)=True) AND
((tblRosterRole.rolDateEnd) Like #12/31/9999#))
ORDER BY tblRoster.rosNameFull", cnxn, adOpenForwardOnly, adLockOptimistic,
adCmdText

or

rs.Open "SELECT * FROM qselRosterActive", cnxn, adOpenForwardOnly,
adLockOptimistic, adCmdText

or

rs.Source = "SELECT * FROM qselRosterActive"
rs.Open , cnxn, adOpenForwardOnly, adLockOptimistic, adCmdText
I usually separate out the Source parameter on queries for readability, but
the important point is, they all work.

I have to assume that the above will work, since I know of no reason that it
would not. If it does not ...?
If it does, then there is something with the first query. I would make a
duplicate, and working with the duplicate, simplify it until it works.
Darryl Kerkeslager
A Person Rapidly Running Out of Ideas



"bdt513" <BD****@gmail.com> wrote in message
news:11*********************@c13g2000cwb.googlegro ups.com...
The query is spelled correctly (I understand that it's worth
double-checking, however).
Does the query need parameters? I don't think so, since it works fine
when run within Access. All queries and tables are within the same
Access database.

The SQL statement looks at check boxes on a form and compares them with
values in a query. If any of the conditions are true, a value of
"show" is returned, and that record appears in the result.

The SQL is below:
SELECT qselRosterActive.rosEmail
FROM qselRosterActive
WHERE (((IIf((([CurrentRole]="Sabre" And
[forms]![frmRosterEmail]![chkSabre]=True) Or ([CurrentRole]="Epee" And
[forms]![frmRosterEmail]![chkEpee]=True) Or ([CurrentRole]="Foil" And
[forms]![frmRosterEmail]![chkFoil]=True) Or ([CurrentRole]="Staff" And
[forms]![frmRosterEmail]![chkStaff]=True)) And (([rosGender]="M" And
[forms]![frmRosterEmail]![chkMen]=True) Or ([rosGender]="W" And
[forms]![frmRosterEmail]![chkWomen]=True)),"show"))="show"))
ORDER BY qselRosterActive.rosEmail;

This SQL referenced a previous query (qselRosterActive), whose SQL is
as follows:
SELECT tblRoster.*, tblRosterRole.rolRole AS CurrentRole
FROM tblRoster INNER JOIN tblRosterRole ON tblRoster.rosID =
tblRosterRole.rolRosID
WHERE (((tblRoster.rosActiveStatus)=True) AND
((tblRosterRole.rolDateEnd) Like #12/31/9999#))
ORDER BY tblRoster.rosNameFull;

Again, thank you very much for your help. I've long struggled to learn
about using Recordsets and am excited about applying knowledge now.
-David

Nov 13 '05 #19

P: n/a
Bri
David,

The error message is correct, you are missing parameters and those
parameters are your form references. The query workes when you open it
in Access because access can see the form and fill in these parameters
while the Recordset Open cannot. You have two choices; either create the
Source to include the values as actual values or add in the parameters
properties to the Command object before you Open it. Examples (Air Code):

Option 1:

dim frm as Form
set frm = [forms]![frmRosterEmail]
stSQL = "SELECT qselRosterActive.rosEmail " & _
"FROM qselRosterActive " & _
"WHERE (((IIf((([CurrentRole]='Sabre' And " & _
frm![chkSabre] & "=True) Or ([CurrentRole]='Epee' And " & _
frm![chkEpee] & "=True) Or ([CurrentRole]='Foil' And " & _
frm![chkFoil] & "=True) Or ([CurrentRole]='Staff' And " & _
frm![chkStaff] & "=True)) And (([rosGender]='M' And " & _
frm![chkMen] & "=True) Or ([rosGender]='W' And " & _
frm![chkWomen] & "=True)),'show'))='show')) " & _
"ORDER BY qselRosterActive.rosEmail; "
rs.Source = stSQL
rs.Open 'use appropriate options for connection etc

Option 2:
Is a bit more complex in ADO (DAO is so much easier for this kind of
thing) so instead of adjusting your code I instead paste in the example
from the Help topic on 'Using the Command Object':

'BeginManualParamCmd
On Error GoTo ErrHandler:

Dim objConn As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objParm1 As New ADODB.Parameter
Dim objRs As New ADODB.Recordset

' Set the CommandText as a parameterized SQL query.
objCmd.CommandText = "SELECT OrderID, OrderDate, " & _
"RequiredDate, ShippedDate " & _
"FROM Orders " & _
"WHERE CustomerID = ? " & _
"ORDER BY OrderID"
objCmd.CommandType = adCmdText

' Prepare command since we will be executing it more than once.
objCmd.Prepared = True

' Create new parameter for CustomerID. Initial value is ALFKI.
Set objParm1 = objCmd.CreateParameter("CustId", adChar, _
adParamInput, 5, "ALFKI")
objCmd.Parameters.Append objParm1

' Connect to the data source.
Set objConn = GetNewConnection
objCmd.ActiveConnection = objConn

' Execute once and display...
Set objRs = objCmd.Execute

Debug.Print objParm1.Value
Do While Not objRs.EOF
Debug.Print vbTab & objRs(0) & vbTab & objRs(1) & vbTab & _
objRs(2) & vbTab & objRs(3)
objRs.MoveNext
Loop

' ...then set new param value, re-execute command, and display.
objCmd("CustId") = "CACTU"
Set objRs = objCmd.Execute

Debug.Print objParm1.Value
Do While Not objRs.EOF
Debug.Print vbTab & objRs(0) & vbTab & objRs(1) & vbTab & _
objRs(2) & vbTab & objRs(3)
objRs.MoveNext
Loop

'clean up
objRs.Close
objConn.Close
Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
Set objParm1 = Nothing
Exit Sub

ErrHandler:
'clean up
If objRs.State = adStateOpen Then
objRs.Close
End If

If objConn.State = adStateOpen Then
objConn.Close
End If

Set objRs = Nothing
Set objConn = Nothing
Set objCmd = Nothing
Set objParm1 = Nothing

If Err <> 0 Then
MsgBox Err.Source & "-->" & Err.Description, , "Error"
End If
'EndManualParamCmd
Hope this helps.

--
Bri
bdt513 wrote:
The query is spelled correctly (I understand that it's worth
double-checking, however).
Does the query need parameters? I don't think so, since it works fine
when run within Access. All queries and tables are within the same
Access database.

The SQL statement looks at check boxes on a form and compares them with
values in a query. If any of the conditions are true, a value of
"show" is returned, and that record appears in the result.

The SQL is below:
SELECT qselRosterActive.rosEmail
FROM qselRosterActive
WHERE (((IIf((([CurrentRole]="Sabre" And
[forms]![frmRosterEmail]![chkSabre]=True) Or ([CurrentRole]="Epee" And
[forms]![frmRosterEmail]![chkEpee]=True) Or ([CurrentRole]="Foil" And
[forms]![frmRosterEmail]![chkFoil]=True) Or ([CurrentRole]="Staff" And
[forms]![frmRosterEmail]![chkStaff]=True)) And (([rosGender]="M" And
[forms]![frmRosterEmail]![chkMen]=True) Or ([rosGender]="W" And
[forms]![frmRosterEmail]![chkWomen]=True)),"show"))="show"))
ORDER BY qselRosterActive.rosEmail;

This SQL referenced a previous query (qselRosterActive), whose SQL is
as follows:
SELECT tblRoster.*, tblRosterRole.rolRole AS CurrentRole
FROM tblRoster INNER JOIN tblRosterRole ON tblRoster.rosID =
tblRosterRole.rolRosID
WHERE (((tblRoster.rosActiveStatus)=True) AND
((tblRosterRole.rolDateEnd) Like #12/31/9999#))
ORDER BY tblRoster.rosNameFull;

Again, thank you very much for your help. I've long struggled to learn
about using Recordsets and am excited about applying knowledge now.
-David

Nov 13 '05 #20

This discussion thread is closed

Replies have been disabled for this discussion.