Help with Dev Ashish's fConcatChild function

R

Ray S.

I'm using a slight variation of Dev's function, but apostrophes in names are
giving me fits. Here's the essence of his function, which I always credit to
him even in my variation:

Option Compare Database
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String

'This function was modified from one written by Dev Ashish

'It returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Example with my database:
' ?fConcatChild("Timesheets","Name","Timesheet
' ID","String",[Names].[Name])
' returns all the Timesheet IDs that are assigned
' to an employee separated by colons
'
' Timesheets = Many side table name
' Name = Primary Key of the One side table
' Timesheet ID = Field name to concatenate
' String = DataType of Primary Key of One Side Table
' value = Value on which return concatenated item
'
' I replaced the value with [Names].[Name] and changed the strIDType
from
' Long to String because I'm returning values on names
' to give me all values

Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String


On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs

If .RecordCount <> 0 Then
'start concatenating records

Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & "; "
.MoveNext

Loop

End If

End With

'That's it... you should have a concatenated string now
'Just Trim the trailing "; "
fConcatChild = Left(varConcat, Len(varConcat) - 2)


Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function

OK, essentially that's the function...the problem I'm getting is that when
the name has an apostrophe, like O'Neill or O'Meara, the function returns no
data (when I can see that there is data that should be returning). It is only
happening with those types of names. I created fake "Irish type" names to
check this and that's what's happening, but I don't know why, or how to fix
it.
 
D

Douglas J. Steele

Change

strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"

to

strSQL = strSQL & "[" & strIDName & "] = """ & varIDvalue & """"

(that's three double quotes in front of varIDvalue and four double quotes
after).

Alternatively, use

strSQL = strSQL & "[" & strIDName & "] = '" & Replace(varIDvalue, "'", "''")
& "'"

Exagerated for clarity, the Replace function call is

Replace(varIDvalue, " ' ", " ' ' ")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ray S. said:
I'm using a slight variation of Dev's function, but apostrophes in names
are
giving me fits. Here's the essence of his function, which I always credit
to
him even in my variation:

Option Compare Database
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String

'This function was modified from one written by Dev Ashish

'It returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Example with my database:
' ?fConcatChild("Timesheets","Name","Timesheet
' ID","String",[Names].[Name])
' returns all the Timesheet IDs that are assigned
' to an employee separated by colons
'
' Timesheets = Many side table name
' Name = Primary Key of the One side table
' Timesheet ID = Field name to concatenate
' String = DataType of Primary Key of One Side
Table
' value = Value on which return concatenated
item
'
' I replaced the value with [Names].[Name] and changed the strIDType
from
' Long to String because I'm returning values on names
' to give me all values

Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String


On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs

If .RecordCount <> 0 Then
'start concatenating records

Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & "; "
.MoveNext

Loop

End If

End With

'That's it... you should have a concatenated string now
'Just Trim the trailing "; "
fConcatChild = Left(varConcat, Len(varConcat) - 2)


Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function

OK, essentially that's the function...the problem I'm getting is that when
the name has an apostrophe, like O'Neill or O'Meara, the function returns
no
data (when I can see that there is data that should be returning). It is
only
happening with those types of names. I created fake "Irish type" names to
check this and that's what's happening, but I don't know why, or how to
fix
it.
Click to expand...
 
R

Ray S.

Thanks Doug,

The first alternative worked perfectly. It would sure be nice if you added a
little explanation, though, so I could understand what was happening.

Douglas J. Steele said:
Change

strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"

to

strSQL = strSQL & "[" & strIDName & "] = """ & varIDvalue & """"

(that's three double quotes in front of varIDvalue and four double quotes
after).

Alternatively, use

strSQL = strSQL & "[" & strIDName & "] = '" & Replace(varIDvalue, "'", "''")
& "'"

Exagerated for clarity, the Replace function call is

Replace(varIDvalue, " ' ", " ' ' ")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ray S. said:
I'm using a slight variation of Dev's function, but apostrophes in names
are
giving me fits. Here's the essence of his function, which I always credit
to
him even in my variation:

Option Compare Database
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String

'This function was modified from one written by Dev Ashish

'It returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Example with my database:
' ?fConcatChild("Timesheets","Name","Timesheet
' ID","String",[Names].[Name])
' returns all the Timesheet IDs that are assigned
' to an employee separated by colons
'
' Timesheets = Many side table name
' Name = Primary Key of the One side table
' Timesheet ID = Field name to concatenate
' String = DataType of Primary Key of One Side
Table
' value = Value on which return concatenated
item
'
' I replaced the value with [Names].[Name] and changed the strIDType
from
' Long to String because I'm returning values on names
' to give me all values

Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String


On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs

If .RecordCount <> 0 Then
'start concatenating records

Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & "; "
.MoveNext

Loop

End If

End With

'That's it... you should have a concatenated string now
'Just Trim the trailing "; "
fConcatChild = Left(varConcat, Len(varConcat) - 2)


Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function

OK, essentially that's the function...the problem I'm getting is that when
the name has an apostrophe, like O'Neill or O'Meara, the function returns
no
data (when I can see that there is data that should be returning). It is
only
happening with those types of names. I created fake "Irish type" names to
check this and that's what's happening, but I don't know why, or how to
fix
it.
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Take a look at my May, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access".

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ray S. said:
Thanks Doug,

The first alternative worked perfectly. It would sure be nice if you added
a
little explanation, though, so I could understand what was happening.

Douglas J. Steele said:
Change

strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"

to

strSQL = strSQL & "[" & strIDName & "] = """ & varIDvalue & """"

(that's three double quotes in front of varIDvalue and four double quotes
after).

Alternatively, use

strSQL = strSQL & "[" & strIDName & "] = '" & Replace(varIDvalue, "'",
"''")
& "'"

Exagerated for clarity, the Replace function call is

Replace(varIDvalue, " ' ", " ' ' ")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ray S. said:
I'm using a slight variation of Dev's function, but apostrophes in
names
are
giving me fits. Here's the essence of his function, which I always
credit
to
him even in my variation:

Option Compare Database
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String

'This function was modified from one written by Dev Ashish

'It returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Example with my database:
' ?fConcatChild("Timesheets","Name","Timesheet
' ID","String",[Names].[Name])
' returns all the Timesheet IDs that are assigned
' to an employee separated by colons
'
' Timesheets = Many side table name
' Name = Primary Key of the One side table
' Timesheet ID = Field name to concatenate
' String = DataType of Primary Key of One Side
Table
' value = Value on which return concatenated
item
'
' I replaced the value with [Names].[Name] and changed the
strIDType
from
' Long to String because I'm returning values on names
' to give me all values

Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String


On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable &
"]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue &
"'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs

If .RecordCount <> 0 Then
'start concatenating records

Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & "; "
.MoveNext

Loop

End If

End With

'That's it... you should have a concatenated string now
'Just Trim the trailing "; "
fConcatChild = Left(varConcat, Len(varConcat) - 2)


Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function

OK, essentially that's the function...the problem I'm getting is that
when
the name has an apostrophe, like O'Neill or O'Meara, the function
returns
no
data (when I can see that there is data that should be returning). It
is
only
happening with those types of names. I created fake "Irish type" names
to
check this and that's what's happening, but I don't know why, or how to
fix
it.
Click to expand...
Click to expand...
Click to expand...
 
R

Ray S.

WOW - thanks, great source, great articles! I really appreciate your help.

Douglas J. Steele said:
Take a look at my May, 2004 "Access Answers" column in Pinnacle
Publication's "Smart Access".

You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Ray S. said:
Thanks Doug,

The first alternative worked perfectly. It would sure be nice if you added
a
little explanation, though, so I could understand what was happening.

Douglas J. Steele said:
Change

strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"

to

strSQL = strSQL & "[" & strIDName & "] = """ & varIDvalue & """"

(that's three double quotes in front of varIDvalue and four double quotes
after).

Alternatively, use

strSQL = strSQL & "[" & strIDName & "] = '" & Replace(varIDvalue, "'",
"''")
& "'"

Exagerated for clarity, the Replace function call is

Replace(varIDvalue, " ' ", " ' ' ")

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I'm using a slight variation of Dev's function, but apostrophes in
names
are
giving me fits. Here's the essence of his function, which I always
credit
to
him even in my variation:

Option Compare Database
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String

'This function was modified from one written by Dev Ashish

'It returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Example with my database:
' ?fConcatChild("Timesheets","Name","Timesheet
' ID","String",[Names].[Name])
' returns all the Timesheet IDs that are assigned
' to an employee separated by colons
'
' Timesheets = Many side table name
' Name = Primary Key of the One side table
' Timesheet ID = Field name to concatenate
' String = DataType of Primary Key of One Side
Table
' value = Value on which return concatenated
item
'
' I replaced the value with [Names].[Name] and changed the
strIDType
from
' Long to String because I'm returning values on names
' to give me all values

Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String


On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable &
"]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue &
"'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs

If .RecordCount <> 0 Then
'start concatenating records

Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & "; "
.MoveNext

Loop

End If

End With

'That's it... you should have a concatenated string now
'Just Trim the trailing "; "
fConcatChild = Left(varConcat, Len(varConcat) - 2)


Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function

OK, essentially that's the function...the problem I'm getting is that
when
the name has an apostrophe, like O'Neill or O'Meara, the function
returns
no
data (when I can see that there is data that should be returning). It
is
only
happening with those types of names. I created fake "Irish type" names
to
check this and that's what's happening, but I don't know why, or how to
fix
it.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

fConcatChild issues 7
Problem with fConcatChild 1
Need help combining related records 3
Trouble with concatenate function 6
fConcatChild Function help? 2
remove duplicate email addresses on bulk email 1
Event after update of last record in subform 6
How to reference a field of a recordset with a variable 4

Top