Loop, Dim, ignore Nulls, and Dim

[Glue]

Hello,
Using Access 2003 I am trying to loop through a query, dim each record as a string, Ignore all Nulls, concatenate the fields together, and then Dim all smaller stings into a LargerString.


Query Name: ReviewSFQ (The below code is pulling ALL records from a table, I’d like to change it to this query as it has a criteria set to eliminate unwanted fields)

PropDescription: Field I want to return. There might be anywhere from zero to 10 records with Null values mixed in.

After looping through I might have one field to Dim:

PD1 = “Blah Blah Blah”

LargerString = PD1

I might have no fields

PD1 = Null
LargerString = “”

I might have four fields

PD1 = “Blah Blah Blah”
PD2 = “Ya Ya Ya”
PD3 = Null
PD4 = “Blah de Blah”

LargerString = PD1 + PD2 + PD4

Here is the code I started:

Dim I As Integer
Dim db As Database
Dim rs As Recordset
Dim LargerString As String

Set db = CurrentDb
Set rs = db.OpenRecordset("StorefrontTable") <-How do I change this to run from a Query?

For I = 0 To rs.RecordCount - 1
Debug.Print rs.Fields("PropDescription")<- How do I change this from the Immediate foot note section into a usable place?
rs.MoveNext

Next I
rs.Close
Set rs = Nothing
db.Close
End Sub

Note: I was able to get a few lines onto a field, but kept getting a Null error and had to start over.

Any help given to solve any of the numerous problems I’m having would be greatly appreciated.

 

[pbaldy]

Set rs = db.OpenRecordset("StorefrontTable") <-How do I change this to run from a Query?

Either put the name of a saved query there, or SQL:

Set rs = db.OpenRecordset("SELECT FieldList FROM TableName WHERE Whatever")

Debug.Print rs.Fields("PropDescription")<- How do I change this from the Immediate foot note section into a usable place?

What do you want to do with it? You set the value of whatever:

VariableOrTextbox = rs.Fields("PropDescription")

 

[boblarson]

You don't need to have multiple variables declared. You can use a single variable.

Dim PD As String

PD = PD & Nz(rs.Fields(x).Value, vbNullString)

The Nz function handles nulls and turns them into empty strings so if there isn't anything there, it won't show up.

 

[Glue]

Thank you both... This gets me moving again. I was thinking I might have to take the plunge into Arrays.

 

[pbaldy]

No problem. About the only time I use an array is when I need the Split() function to break up a string.

 

[Glue]

I've been out for a bit, but back on this code... Everything works except for the "Where" part of my statement. Here is the code:

Dim PD As String

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("SELECT PropDescription FROM StorefrontTable Where EstID = Me.EstLookUp")

I've also tried

Dim IE as String
IE = Me.EstLookUp
Set rs = CurrentDb.OpenRecordset("SELECT PropDescription FROM StorefrontTable Where EstID = EI")


If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
Do Until rs.EOF = True

PD = PD & Nz(rs!PropDescription & vbNewLine & vbNewLine, vbNullString)

rs.MoveNext
Loop
Else
msgbox "There are no records in the recordset."
End If
rs.Close
Set rs = Nothing
BodyText = PD

End Sub

If I use this:

Set rs = CurrentDb.OpenRecordset("SELECT PropDescription FROM StorefrontTable Where EstID = 26")

It works fine...

So how do I get my Where statement to only find records where ESTID is equal to the value in my text box?

Currently I get this error: Runtime error 3061: Too few parameters. Expected 1

Thanks Again!!!!

 

[pbaldy]

You have to concatenate the variable/form reference into the string:

Set rs = CurrentDb.OpenRecordset("SELECT PropDescription FROM StorefrontTable Where EstID = " & Me.EstLookUp)

If EstID is text:

Set rs = CurrentDb.OpenRecordset("SELECT PropDescription FROM StorefrontTable Where EstID = '" & Me.EstLookUp & "'")

 

[Glue]

This code is working perfectly, but is there a way to condition it by two fields? I've been looking for the answer and trying different code for two days and can't get it to work.

Both of these work on thier own:

Set rs = CurrentDb.OpenRecordset("SELECT PropDescription FROM StorefrontTable Where EstID = " & Me.EstID)
Set rs = CurrentDb.OpenRecordset("SELECT PropDescription FROM StorefrontTable Where SFAlt = " & True)

Any help to combine them into one statement would be greatly appreciated!!! The below is wrong, (Maybe because the select statement is ending at "), but I can't figure out how to fix it.

Set rs = CurrentDb.OpenRecordset("SELECT PropDescription FROM StorefrontTable Where (EstID = " & Me.EstID) And (SFAlt = " & True))

Thanks in advance!!!

 

[pbaldy]

You need to restart the string after the first form reference:

Set rs = CurrentDb.OpenRecordset("SELECT PropDescription FROM StorefrontTable Where EstID = " & Me.EstID & " And SFAlt = " & True)

 

[Glue]

Working like a charm!!! I think I can finally let this thread die. Thanks again

 

[pbaldy]

Happy to help!