Move across a recordset

[Joe]

I feel like this one is simple enough, but it's had me stumped for quite a
while. I have a string expression that will be used as an SQL statement.
The string is built by looking through a record in a table and adding
whatever text is available for each field. Here is the jist of what I need:

Dim rstSub As Recordset
Dim db As Database
Dim strAdd As String
Dim x As Integer

Set db = CurrentDb
Set rstSub = db.OpenRecordset("tblSubDef", dbOpenDynaset)

'Some of this is in plain text
With rstSub
Do While !ThisColumn <> ""
strAdd = !ThisColumn
strWhere = strWhere & "Subject = " & strAdd & " "
strWhere = strWhere & "AND "
.MoveNextColumn
End With

Your help will be much appreciated!

Joe

 

[Dirk Goldgar]

I feel like this one is simple enough, but it's had me stumped for
quite a while. I have a string expression that will be used as an
SQL statement. The string is built by looking through a record in a
table and adding whatever text is available for each field. Here is
the jist of what I need:

Dim rstSub As Recordset
Dim db As Database
Dim strAdd As String
Dim x As Integer

Set db = CurrentDb
Set rstSub = db.OpenRecordset("tblSubDef", dbOpenDynaset)

'Some of this is in plain text
With rstSub
Do While !ThisColumn <> ""
strAdd = !ThisColumn
strWhere = strWhere & "Subject = " & strAdd & " "
strWhere = strWhere & "AND "
.MoveNextColumn
End With

Your help will be much appreciated!

Joe

I'm not quite sure what you're after, Joe, but I think maybe you want to
iterate throught the recordset's Fields collection:

Dim fld As DAO.Field

For Each fld in rstSub.Fields

' ... do something involving fld.Name and/or fld.Value ...

Next fld

 

[Marshall Barton]

I feel like this one is simple enough, but it's had me stumped for quite a
while. I have a string expression that will be used as an SQL statement.
The string is built by looking through a record in a table and adding
whatever text is available for each field. Here is the jist of what I need:

Dim rstSub As Recordset
Dim db As Database
Dim strAdd As String
Dim x As Integer

Set db = CurrentDb
Set rstSub = db.OpenRecordset("tblSubDef", dbOpenDynaset)

'Some of this is in plain text
With rstSub
Do While !ThisColumn <> ""
strAdd = !ThisColumn
strWhere = strWhere & "Subject = " & strAdd & " "
strWhere = strWhere & "AND "
.MoveNextColumn
End With

I don't think you want to use AND here. The subject can not
be equal to several different things. Most likely, you ant
to use ORm but, in that case, using IN would be easier.

For Each fld In .Fields
If fld <> "" Then
strWhere = strWhere & ",""" & fld & """"
Else
Exit For
End If
Next fld
strWhere ="Subject IN (" & Mid(strWhere, 2) & ")"
msgbox strwhere

 

[Joe]

Thank you both for your responses. Marsh, you are correct in saying that I
want to use OR instead of AND in my statement. I've never used IN before,
but I will test out the code and tailor it to my needs.

Dirk, that bit of code is exactly what I was looking for.

Thanks again,

Joe