Hi All, I have a special issue with Access 2007, that I cannot solve. I have a report with 2 fields: Ingredients and Allergens. I need to obtain a TextBox with Ingredients, Comma separeted and if Allergen is not equal to "Null" the ingredient must be Bold. here an example:
The data are:
Ingredients Allergens
water
sugar Sugar
glucose syrup (corn) Sugar
lemon juice (4%)
Result in TexBox must be:
water, sugar, glucose syrup (corn), lemon juice (4%).
In this moment I am using GetList Function (see the Bottom of my Thread) but I am in difficult to make Bold if Allergen is present.
Thanks in advance.
Option Compare Database
Option Explicit
Public Function GetList(SQL As String _
, Optional ColumnDelimeter As String = ", " _
, Optional RowDelimeter As String = vbCrLf) As String
'PURPOSE: to return a combined string from the passed query
'ARGS:
' 1. SQL is a valid Select statement
' 2. ColumnDelimiter is the character(s) that separate each column
' 3. RowDelimiter is the character(s) that separate each row
'RETURN VAL: Concatenated list
'DESIGN NOTES:
'EXAMPLE CALL: =GetList("Select Col1,Col2 From Table1 Where Table1.Key = " & OuterTable.Key)
Const PROCNAME = "GetList"
Const adClipString = 2
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sResult As String
On Error GoTo ProcErr
Set oConn = CurrentProject.Connection
Set oRS = oConn.Execute(SQL)
sResult = oRS.GetString(adClipString, -1, ColumnDelimeter, RowDelimeter)
If Right(sResult, Len(RowDelimeter)) = RowDelimeter Then
sResult = Mid$(sResult, 1, Len(sResult) - Len(RowDelimeter))
End If
GetList = sResult
oRS.Close
oConn.Close
Cleanup:
Set oRS = Nothing
Set oConn = Nothing
Exit Function
ProcErr:
' insert error handler
Resume Cleanup
End Function
The data are:
Ingredients Allergens
water
sugar Sugar
glucose syrup (corn) Sugar
lemon juice (4%)
Result in TexBox must be:
water, sugar, glucose syrup (corn), lemon juice (4%).
In this moment I am using GetList Function (see the Bottom of my Thread) but I am in difficult to make Bold if Allergen is present.
Thanks in advance.
Option Compare Database
Option Explicit
Public Function GetList(SQL As String _
, Optional ColumnDelimeter As String = ", " _
, Optional RowDelimeter As String = vbCrLf) As String
'PURPOSE: to return a combined string from the passed query
'ARGS:
' 1. SQL is a valid Select statement
' 2. ColumnDelimiter is the character(s) that separate each column
' 3. RowDelimiter is the character(s) that separate each row
'RETURN VAL: Concatenated list
'DESIGN NOTES:
'EXAMPLE CALL: =GetList("Select Col1,Col2 From Table1 Where Table1.Key = " & OuterTable.Key)
Const PROCNAME = "GetList"
Const adClipString = 2
Dim oConn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim sResult As String
On Error GoTo ProcErr
Set oConn = CurrentProject.Connection
Set oRS = oConn.Execute(SQL)
sResult = oRS.GetString(adClipString, -1, ColumnDelimeter, RowDelimeter)
If Right(sResult, Len(RowDelimeter)) = RowDelimeter Then
sResult = Mid$(sResult, 1, Len(sResult) - Len(RowDelimeter))
End If
GetList = sResult
oRS.Close
oConn.Close
Cleanup:
Set oRS = Nothing
Set oConn = Nothing
Exit Function
ProcErr:
' insert error handler
Resume Cleanup
End Function
