Error Message trying to concatenate from multiple records to a field

[brian.reimer]

I have a table with the field:

tblFormatted1Summary

f1PropertyID
__________
R100000
R100001
R100002
R100003

The second table contains the field f3PropertyID which is related to
f1PropertyID. I need to concatenate the f3ImprovTypeCode field for
each instance of the f1PropertyID in tblFormatted1Summary.

tblFormatted2Land

f3PropertyID f2LandTypeCode
R100000 DC
R100000 IC
R100001
R100003 DC

To where I can get:

R100000 DC,IC
R100001
R100002
R100003 DC

I found the following concatenate function by Duane Hookom and I get
the following error message:

"no value given for one or more required parameters" for each of the
records that is "processed"

The debug feature points to the section:
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

My query contains tblFormatted1Summary.

The first column has the f1PropertyID field

The second column has: LandTypeCodes: Concatenate("SELECT
f2LandTypeCode FROM tblFormatted2Land WHERE f2PropertyID =" &
[f1PropertyID])

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Here's Duane's Concatenate function:

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

 

[brian.reimer]

I have a table with the field:

tblFormatted1Summary

f1PropertyID
__________
R100000
R100001
R100002
R100003

The second table contains the field f3PropertyID which is related to
f1PropertyID. I need to concatenate the f3ImprovTypeCode field for
each instance of the f1PropertyID in tblFormatted1Summary.

tblFormatted2Land

f3PropertyID f2LandTypeCode
R100000 DC
R100000 IC
R100001
R100003 DC

To where I can get:

R100000 DC,IC
R100001
R100002
R100003 DC

I found the following concatenate function by Duane Hookom and I get
the following error message:

"no value given for one or more required parameters" for each of the
records that is "processed"

The debug feature points to the section:
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

My query contains tblFormatted1Summary.

The first column has the f1PropertyID field

The second column has: LandTypeCodes: Concatenate("SELECT
f2LandTypeCode FROM tblFormatted2Land WHERE f2PropertyID =" &
[f1PropertyID])

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Here's Duane's Concatenate function:

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

Any help I could get would be greatly appreciated...

 

[Ken Snell \(MVP\)]

Because f1PropertyID is a text field, you must delimit its value in WHERE
clause with ' characters:

LandTypeCodes: Concatenate("SELECT
f2LandTypeCode FROM tblFormatted2Land WHERE f2PropertyID ='" &
[f1PropertyID] & "'")


By the way, are you sure f2PropertyID is the correct field in the SQL
statement?

 

[brian.reimer]

Because f1PropertyID is a text field, you must delimit its value in WHERE
clause with ' characters:

LandTypeCodes: Concatenate("SELECT
f2LandTypeCode FROM tblFormatted2Land WHERE f2PropertyID ='" &
[f1PropertyID] & "'")

By the way, are you sure f2PropertyID is the correct field in the SQL
statement?
--

Ken Snell
<MS ACCESS MVP>

I have a table with the field:


The second table contains the field f3PropertyID which is related to
f1PropertyID. I need to concatenate the f3ImprovTypeCode field for
each instance of the f1PropertyID in tblFormatted1Summary.

f3PropertyID f2LandTypeCode
R100000 DC
R100000 IC
R100001
R100003 DC

To where I can get:

R100000 DC,IC
R100001
R100002
R100003 DC

I found the following concatenate function by Duane Hookom and I get
the following error message:

"no value given for one or more required parameters" for each of the
records that is "processed"

The debug feature points to the section:
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

My query contains tblFormatted1Summary.

The first column has the f1PropertyID field

The second column has: LandTypeCodes: Concatenate("SELECT
f2LandTypeCode FROM tblFormatted2Land WHERE f2PropertyID =" &
[f1PropertyID])

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Here's Duane's Concatenate function:

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

thanks Ken, that works! Appreciate it.

 

[brian.reimer]

Because f1PropertyID is a text field, you must delimit its value in WHERE
clause with ' characters:

LandTypeCodes: Concatenate("SELECT
f2LandTypeCode FROM tblFormatted2Land WHERE f2PropertyID ='" &
[f1PropertyID] & "'")

By the way, are you sure f2PropertyID is the correct field in the SQL
statement?
--

Ken Snell
<MS ACCESS MVP>

I have a table with the field:


The second table contains the field f3PropertyID which is related to
f1PropertyID. I need to concatenate the f3ImprovTypeCode field for
each instance of the f1PropertyID in tblFormatted1Summary.

f3PropertyID f2LandTypeCode
R100000 DC
R100000 IC
R100001
R100003 DC

To where I can get:

R100000 DC,IC
R100001
R100002
R100003 DC

I found the following concatenate function by Duane Hookom and I get
the following error message:

"no value given for one or more required parameters" for each of the
records that is "processed"

The debug feature points to the section:
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

My query contains tblFormatted1Summary.

The first column has the f1PropertyID field

The second column has: LandTypeCodes: Concatenate("SELECT
f2LandTypeCode FROM tblFormatted2Land WHERE f2PropertyID =" &
[f1PropertyID])

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Here's Duane's Concatenate function:

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

How could I put this in an update query?

 

[brian.reimer]

Because f1PropertyID is a text field, you must delimit its value in WHERE
clause with ' characters:

LandTypeCodes: Concatenate("SELECT
f2LandTypeCode FROM tblFormatted2Land WHERE f2PropertyID ='" &
[f1PropertyID] & "'")

By the way, are you sure f2PropertyID is the correct field in the SQL
statement?
--

Ken Snell
<MS ACCESS MVP>

I have a table with the field:


The second table contains the field f3PropertyID which is related to
f1PropertyID. I need to concatenate the f3ImprovTypeCode field for
each instance of the f1PropertyID in tblFormatted1Summary.

f3PropertyID f2LandTypeCode
R100000 DC
R100000 IC
R100001
R100003 DC

To where I can get:

R100000 DC,IC
R100001
R100002
R100003 DC

I found the following concatenate function by Duane Hookom and I get
the following error message:

"no value given for one or more required parameters" for each of the
records that is "processed"

The debug feature points to the section:
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

My query contains tblFormatted1Summary.

The first column has the f1PropertyID field

The second column has: LandTypeCodes: Concatenate("SELECT
f2LandTypeCode FROM tblFormatted2Land WHERE f2PropertyID =" &
[f1PropertyID])

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Here's Duane's Concatenate function:

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

How could I put this in an update query?

 

[Ken Snell \(MVP\)]

How can you put "what" in an update query? I'm not understanding your
question, sorry.

--

Ken Snell
<MS ACCESS MVP>

Because f1PropertyID is a text field, you must delimit its value in WHERE
clause with ' characters:

LandTypeCodes: Concatenate("SELECT
f2LandTypeCode FROM tblFormatted2Land WHERE f2PropertyID ='" &
[f1PropertyID] & "'")

By the way, are you sure f2PropertyID is the correct field in the SQL
statement?
--

Ken Snell
<MS ACCESS MVP>

I have a table with the field:


The second table contains the field f3PropertyID which is related to
f1PropertyID. I need to concatenate the f3ImprovTypeCode field for
each instance of the f1PropertyID in tblFormatted1Summary.

f3PropertyID f2LandTypeCode
R100000 DC
R100000 IC
R100001
R100003 DC

To where I can get:

R100000 DC,IC
R100001
R100002
R100003 DC

I found the following concatenate function by Duane Hookom and I get
the following error message:

"no value given for one or more required parameters" for each of the
records that is "processed"

The debug feature points to the section:
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

My query contains tblFormatted1Summary.

The first column has the f1PropertyID field

The second column has: LandTypeCodes: Concatenate("SELECT
f2LandTypeCode FROM tblFormatted2Land WHERE f2PropertyID =" &
[f1PropertyID])

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Here's Duane's Concatenate function:

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

How could I put this in an update query?

 

[brian.reimer]

How can you put "what" in an update query? I'm not understanding your
question, sorry.

--

Ken Snell
<MS ACCESS MVP>

Because f1PropertyID is a text field, you must delimit its value in WHERE
clause with ' characters:
LandTypeCodes: Concatenate("SELECT
f2LandTypeCode FROM tblFormatted2Land WHERE f2PropertyID ='" &
[f1PropertyID] & "'")
By the way, are you sure f2PropertyID is the correct field in the SQL
statement?
--
Ken Snell
<MS ACCESS MVP>

I have a table with the field:
tblFormatted1Summary
f1PropertyID
__________
R100000
R100001
R100002
R100003
The second table contains the field f3PropertyID which is related to
f1PropertyID. I need to concatenate the f3ImprovTypeCode field for
each instance of the f1PropertyID in tblFormatted1Summary.
tblFormatted2Land
f3PropertyID f2LandTypeCode
R100000 DC
R100000 IC
R100001
R100003 DC
To where I can get:
R100000 DC,IC
R100001
R100002
R100003 DC
I found the following concatenate function by Duane Hookom and I get
the following error message:
"no value given for one or more required parameters" for each of the
records that is "processed"
The debug feature points to the section:
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
My query contains tblFormatted1Summary.
The first column has the f1PropertyID field
The second column has: LandTypeCodes: Concatenate("SELECT
f2LandTypeCode FROM tblFormatted2Land WHERE f2PropertyID =" &
[f1PropertyID])
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String
Here's Duane's Concatenate function:
'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)
'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function

How could I put this in an update query?

Instead of using this function in a select query, could I use this
concatenate function in an update query to update the table with
concatenated values?

 

[Ken Snell \(MVP\)]

Sure; for example:

UPDATE TableName
SET FieldName = Concatenate("SELECT f2LandTypeCode FROM tblFormatted2Land
WHERE f2PropertyID ='" & [f1PropertyID] & "'");

 

[brian.reimer]

Thank you Ken, I appreciate it.

Sure; for example:

UPDATE TableName
SET FieldName = Concatenate("SELECT f2LandTypeCode FROM tblFormatted2Land
WHERE f2PropertyID ='" & [f1PropertyID] & "'");

--

Ken Snell
<MS ACCESS MVP>

Instead of using this function in a select query, could I use this
concatenate function in an update query to update the table with
concatenated values?