B
Bird_e_boy
Hi All,
I am working with 'Microsoft Access 97' on a query that concatenates
comments recorded against a unique ID, which is then displayed in a column
adjacent to that ID. This is repeated for other ID's and their associated
comments. The query which performs this is shown below:
SELECT DemandNo, DemandDate, Comments FROM tblTemp WHERE (False)
UNION ALL SELECT DemandNo, DemandDate,
Left(replaceVal(replaceVal(replaceVal(NZ(fConcatFld("tblDemandProgress","DemandNo;DemandDate","DemandProgress","String;DateTime",[DemandNo]
& ";" & Format([DemandDate],"dd-mmm-yy"),"DemandProgressDate")),Chr(13),"
"),Chr(10)," "),Chr(124)," "), 4000) AS Comments
FROM qryEPMExtract_GetDistinct_Demands;
As shown in the query the actual concatenation is handled by a function
called fConcatFld, which returns a single concatenated string to the query.
Many of the strings return by 'fConcatFld' are larger than '255', which means
storing the comment in a text field is inappropriate due to its maximum
storage capability being '255'. Therefore a memo field would be ideal as
this has a storage capacity of around '2.5 GB'.
Access by default uses the data type of the field from which the comments
are derived from (which in this case is a text field), as the data type for
the field the comments will be inserted into, post concatenation; any
concatenated comments larger than '255' will be automatically truncated to
that size on insertion back into the field. This means to prohibit the
automatic truncation, the field will need to be converted into a memo. This
was achieved (as shown in the query above) by creating a temporary table
(tblTemp) which contains the ID fields and a comment field that has a data
type of memo. A 'UNION ALL' is then performed to join 'tblTemp' to
'qryEPMExtract_GetDistinct_Demands' which converts the comments field from
text to memo, as the first 'SELECT' in a 'UNION ALL' query defines the field
data type of the columns data is returned into. Once the concatenated string
is returned to the query by 'fConcatFld' the other methods shown surrounding
it mainly remove unwanted characters and truncate it to a '4000' length which
is a requirement for the query results displayed.
This originally worked successfully for 'Microsoft Access 2003', but the
requirement is for it to run on 'Access 97' which required modifications to
fConcatFld for array handling, and functions unsupported by 'Access 97' but
supported by 'Access 2000'. The issue is when this query is executed in
'Access 97' the function executes and concatenates the first group of
comments associated with a unique ID, but as soon as control is handed back
to the query the following application error is thrown and when clicking
either OK or Cancel it terminates the 'Access' Application closing it
completely. The application error is shown below:
MSACCESS.EXE - Application Error
Instruction at 0x77fcbee8 referenced memory at 0xfffffffe. Memory could not
be "read"
The bizarre thing is that when the argument in the 'LEFT' function (shown in
query above) is changed form '4000 to <=255', the query executes successfully
concatenating all comments and displaying the results. Can any one help? The
code for 'fConcatFld' is shown below:
'==============================================================
' fConcatFld
'--------------------------------------------------------------
'
'************ Usage Conditions / Copyright Notice **********
'
'Code was originally sourced from
http://www.mvps.org/access/modules/mdl0008.htm
'
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
'***********************************************************
'
' AUTHOR: Dev Ashish
' MODIFIED: 17/04/08
' MODIFIED BY: Christopher Ian Bird
' NOTE: Original source has been modified in-line with copyright
notice above.
' Modification reason to extend original functionality.
'
' FUNCTION DESCRIPTION:
'
' Returns multiple field values for each unique value
' of another field in a single table
' in a semi-colon separated format.
'
' USAGE INFO:
' Usage:
'
?fConcatFld(("STABLE1[,STABLE2..]","WCOLUMN1[;WCOLUMN2..]","CONCATCOLUMN",
' "CTYPE1[;CTYPE2..]", CVALUE1[ & ";" & CVALUE2..],
"OCOLUMN1[;OCOLUMN2..]")
'
' Where STABLE(N) = The base table(s).
' WCOLUMN(N) = The base table(s) Field name(s) whose values to use
for lookups.
' CONCATCOLUMN = Field name to concatenate.
' CTYPE(N) = DataType of CONCATCOLUMN field(s).
' CVALUE(N) = Value(s) on which to return concatenated
CONCATCOLUMN.
' OCOLUMN(N) = Field(s) on which to order CVALUE('s)
pre-concatenation.
'
' Usage Example:
' fConcatFld("tblDemandProgress","DemandNo;DemandDate","DemandProgress",
' "String;DateTime",[DemandNo] & ";" & Format( [DemandDate],
"dd-mmm-yy"),"DemandProgressDate")
'
' Function constructs following SQL Statement for execution from parameters
passed in example below.
' Concatenates record values for 'DemandProgress' column/field:
'
' SELECT [DemandProgress]
' FROM [tblDemandProgress]
' WHERE [DemandNo] = "<VALUE>" AND [DemandDate] = #<VALUE>#
' ORDER BY [DemandProgressDate]
'
' Note: <VALUE> represents dynamic values.
'
'==============================================================
Function fConcatFld(stTable As String, _
stForFld As String, _
stFldToConcat As String, _
stForFldType As String, _
vForFldVal As Variant, _
stForFldOrd As String) _
As String
' Declarations.
' Stores Database object for currently open Access Database.
Dim lodb As Database
' Stores Recordset object returned from executing SQL statement.
Dim lors As Recordset
' Stores concatenated records.
Dim lovConcat As Variant
' Delimiter for concatenated records.
Dim loCriteria As String
' Stores constructed SQL Statement.
Dim loSQL As String
' Stores delimiter for concatenated records.
Dim concat_del As String
' Stores names of columns to be used in SQL statements 'WHERE' clause.
Dim sql_where_c As Variant
' Stores column types for columns stored in sql_where_c array.
Dim sql_where_c_type As Variant
' Stores values for columns stored in sql_where_c array.
Dim sql_where_v As Variant
' Stores names of colums to be used in SQL statements 'ORDER BY' clause.
Dim sql_orderBy_c As Variant
' Initialise constants.
' Stores symbol that represents a string value in SQL.
Const cQ = """"
' Stores symbol that represents a DateTime value in SQL.
Const cH = "#"
' TO DO...
' -- Check paramater logic i.e - handling optional parameters or empty
parameters etc.
' -- Log_op AND, OR handling.
' -- Check for Zero Length Strings and Nulls.
' Catch Error and pass control to Error Handling section.
On Error GoTo Err_fConcatFld
' Set symbol to delimit by.
concat_del = "; "
lovConcat = Null
' Returns an Database object that represents the database
' currently open in the Microsoft Access Window.
Set lodb = CurrentDb
' Tokenise function parameters by ';' and store tokens in arrays.
' VBA split function not supported by Access 97, only available 2000
onwards.
'sql_where_c = split(stForFld, ";", -1, vbTextCompare)
'sql_where_c_type = split(stForFldType, ";", -1, vbTextCompare)
'sql_where_v = split(vForFldVal, ";", -1, vbTextCompare)
'sql_orderBy_c = split(stForFldOrd, ";", -1, vbTextCompare)
' split_97 ad-hoc function to provide functionality of 'split' function
above.
sql_where_c = split_A97(stForFld, ";", -1, vbTextCompare)
sql_where_c_type = split_A97(stForFldType, ";", -1, vbTextCompare)
sql_where_v = split_A97(vForFldVal, ";", -1, vbTextCompare)
sql_orderBy_c = split_A97(stForFldOrd, ";", -1, vbTextCompare)
' Useful Debug code - outputs contents of arrays into single string
' delimited by a comma.
'Debug.Print Join(sql_where_c, ",")
'Debug.Print Join(sql_where_c_type, ",")
'Debug.Print Join(sql_where_v, ",")
'Debug.Print Join(sql_orderBy_c, ",")
' Construct SQL statement (pre-execution).
loSQL = "SELECT [" & stFldToConcat & "] FROM ["
loSQL = loSQL & stTable & "] WHERE "
' Construct 'WHERE' clause.
' Append each column and associated column value in arrays to the SQL
' statement constructed so far in following format:
' WHERE column1 = column1value AND column2 = column2value AND ...
For i = LBound(sql_where_c_type, 1) To UBound(sql_where_c_type, 1)
' Check column values data type and represent them appropriately in
SQL.
Select Case sql_where_c_type(i)
Case "String":
' String values identified by quotes "value".
loSQL = loSQL & "[" & sql_where_c(i) & "] =" & cQ &
sql_where_v(i) & cQ & " AND "
Case "Long", "Integer", "Double":
' AutoNumber is Type Long
loSQL = loSQL & "[" & sql_where_c(i) & "] = " & sql_where_v(i)
& " AND "
Case "DateTime":
' DateTime values identified by hash symbol #DateTime#
loSQL = loSQL & "[" & sql_where_c(i) & "] = " & cH &
sql_where_v(i) & cH & " AND "
Case Else
' Cannot identify column data type so Error.
GoTo Err_fConcatFld
End Select
Next i
' Strip off the trailing 'AND ' from the SQL Statement constructed so far.
loSQL = Left(loSQL, Len(loSQL) - 5)
' Append 'ORDER BY' clause to SQL Statement along with column names to
order by.
loSQL = loSQL & " ORDER BY " & "[" & Join(sql_orderBy_c, "],[") & "]"
' Useful Debug code - outputs constructed SQL statements (pre-execution).
' Debug.Print loSQL
' Execute SQL statement and retrieve generated recordset.
Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)
'Are we sure that duplicates exist in stFldToConcat
With lors
' Check recordset is not empty.
If .RecordCount <> 0 Then
' Start concatenating records
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & concat_del
' Useful Debug code - outputs each step of concatenation.
' Debug.Print lovConcat
.MoveNext
Loop
Else
' Recordset empty so Error.
GoTo Exit_fConcatFld
End If
End With
' Useful Debug code - output complete string after concatenation.
' Debug.Print lovConcat
' Trim trailing ';'.
fConcatFld = Left(lovConcat, Len(lovConcat) - 2)
' Useful Debug code - output string returned by function.
' Debug.Print fConcatFld
' Clean up.
Exit_fConcatFld:
Set lors = Nothing: Set lodb = Nothing
Exit Function
' Error Handling.
Err_fConcatFld:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
Resume Exit_fConcatFld
End Function
Any help is much appreciated.
Thanks in advance.
I am working with 'Microsoft Access 97' on a query that concatenates
comments recorded against a unique ID, which is then displayed in a column
adjacent to that ID. This is repeated for other ID's and their associated
comments. The query which performs this is shown below:
SELECT DemandNo, DemandDate, Comments FROM tblTemp WHERE (False)
UNION ALL SELECT DemandNo, DemandDate,
Left(replaceVal(replaceVal(replaceVal(NZ(fConcatFld("tblDemandProgress","DemandNo;DemandDate","DemandProgress","String;DateTime",[DemandNo]
& ";" & Format([DemandDate],"dd-mmm-yy"),"DemandProgressDate")),Chr(13),"
"),Chr(10)," "),Chr(124)," "), 4000) AS Comments
FROM qryEPMExtract_GetDistinct_Demands;
As shown in the query the actual concatenation is handled by a function
called fConcatFld, which returns a single concatenated string to the query.
Many of the strings return by 'fConcatFld' are larger than '255', which means
storing the comment in a text field is inappropriate due to its maximum
storage capability being '255'. Therefore a memo field would be ideal as
this has a storage capacity of around '2.5 GB'.
Access by default uses the data type of the field from which the comments
are derived from (which in this case is a text field), as the data type for
the field the comments will be inserted into, post concatenation; any
concatenated comments larger than '255' will be automatically truncated to
that size on insertion back into the field. This means to prohibit the
automatic truncation, the field will need to be converted into a memo. This
was achieved (as shown in the query above) by creating a temporary table
(tblTemp) which contains the ID fields and a comment field that has a data
type of memo. A 'UNION ALL' is then performed to join 'tblTemp' to
'qryEPMExtract_GetDistinct_Demands' which converts the comments field from
text to memo, as the first 'SELECT' in a 'UNION ALL' query defines the field
data type of the columns data is returned into. Once the concatenated string
is returned to the query by 'fConcatFld' the other methods shown surrounding
it mainly remove unwanted characters and truncate it to a '4000' length which
is a requirement for the query results displayed.
This originally worked successfully for 'Microsoft Access 2003', but the
requirement is for it to run on 'Access 97' which required modifications to
fConcatFld for array handling, and functions unsupported by 'Access 97' but
supported by 'Access 2000'. The issue is when this query is executed in
'Access 97' the function executes and concatenates the first group of
comments associated with a unique ID, but as soon as control is handed back
to the query the following application error is thrown and when clicking
either OK or Cancel it terminates the 'Access' Application closing it
completely. The application error is shown below:
MSACCESS.EXE - Application Error
Instruction at 0x77fcbee8 referenced memory at 0xfffffffe. Memory could not
be "read"
The bizarre thing is that when the argument in the 'LEFT' function (shown in
query above) is changed form '4000 to <=255', the query executes successfully
concatenating all comments and displaying the results. Can any one help? The
code for 'fConcatFld' is shown below:
'==============================================================
' fConcatFld
'--------------------------------------------------------------
'
'************ Usage Conditions / Copyright Notice **********
'
'Code was originally sourced from
http://www.mvps.org/access/modules/mdl0008.htm
'
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
'***********************************************************
'
' AUTHOR: Dev Ashish
' MODIFIED: 17/04/08
' MODIFIED BY: Christopher Ian Bird
' NOTE: Original source has been modified in-line with copyright
notice above.
' Modification reason to extend original functionality.
'
' FUNCTION DESCRIPTION:
'
' Returns multiple field values for each unique value
' of another field in a single table
' in a semi-colon separated format.
'
' USAGE INFO:
' Usage:
'
?fConcatFld(("STABLE1[,STABLE2..]","WCOLUMN1[;WCOLUMN2..]","CONCATCOLUMN",
' "CTYPE1[;CTYPE2..]", CVALUE1[ & ";" & CVALUE2..],
"OCOLUMN1[;OCOLUMN2..]")
'
' Where STABLE(N) = The base table(s).
' WCOLUMN(N) = The base table(s) Field name(s) whose values to use
for lookups.
' CONCATCOLUMN = Field name to concatenate.
' CTYPE(N) = DataType of CONCATCOLUMN field(s).
' CVALUE(N) = Value(s) on which to return concatenated
CONCATCOLUMN.
' OCOLUMN(N) = Field(s) on which to order CVALUE('s)
pre-concatenation.
'
' Usage Example:
' fConcatFld("tblDemandProgress","DemandNo;DemandDate","DemandProgress",
' "String;DateTime",[DemandNo] & ";" & Format( [DemandDate],
"dd-mmm-yy"),"DemandProgressDate")
'
' Function constructs following SQL Statement for execution from parameters
passed in example below.
' Concatenates record values for 'DemandProgress' column/field:
'
' SELECT [DemandProgress]
' FROM [tblDemandProgress]
' WHERE [DemandNo] = "<VALUE>" AND [DemandDate] = #<VALUE>#
' ORDER BY [DemandProgressDate]
'
' Note: <VALUE> represents dynamic values.
'
'==============================================================
Function fConcatFld(stTable As String, _
stForFld As String, _
stFldToConcat As String, _
stForFldType As String, _
vForFldVal As Variant, _
stForFldOrd As String) _
As String
' Declarations.
' Stores Database object for currently open Access Database.
Dim lodb As Database
' Stores Recordset object returned from executing SQL statement.
Dim lors As Recordset
' Stores concatenated records.
Dim lovConcat As Variant
' Delimiter for concatenated records.
Dim loCriteria As String
' Stores constructed SQL Statement.
Dim loSQL As String
' Stores delimiter for concatenated records.
Dim concat_del As String
' Stores names of columns to be used in SQL statements 'WHERE' clause.
Dim sql_where_c As Variant
' Stores column types for columns stored in sql_where_c array.
Dim sql_where_c_type As Variant
' Stores values for columns stored in sql_where_c array.
Dim sql_where_v As Variant
' Stores names of colums to be used in SQL statements 'ORDER BY' clause.
Dim sql_orderBy_c As Variant
' Initialise constants.
' Stores symbol that represents a string value in SQL.
Const cQ = """"
' Stores symbol that represents a DateTime value in SQL.
Const cH = "#"
' TO DO...
' -- Check paramater logic i.e - handling optional parameters or empty
parameters etc.
' -- Log_op AND, OR handling.
' -- Check for Zero Length Strings and Nulls.
' Catch Error and pass control to Error Handling section.
On Error GoTo Err_fConcatFld
' Set symbol to delimit by.
concat_del = "; "
lovConcat = Null
' Returns an Database object that represents the database
' currently open in the Microsoft Access Window.
Set lodb = CurrentDb
' Tokenise function parameters by ';' and store tokens in arrays.
' VBA split function not supported by Access 97, only available 2000
onwards.
'sql_where_c = split(stForFld, ";", -1, vbTextCompare)
'sql_where_c_type = split(stForFldType, ";", -1, vbTextCompare)
'sql_where_v = split(vForFldVal, ";", -1, vbTextCompare)
'sql_orderBy_c = split(stForFldOrd, ";", -1, vbTextCompare)
' split_97 ad-hoc function to provide functionality of 'split' function
above.
sql_where_c = split_A97(stForFld, ";", -1, vbTextCompare)
sql_where_c_type = split_A97(stForFldType, ";", -1, vbTextCompare)
sql_where_v = split_A97(vForFldVal, ";", -1, vbTextCompare)
sql_orderBy_c = split_A97(stForFldOrd, ";", -1, vbTextCompare)
' Useful Debug code - outputs contents of arrays into single string
' delimited by a comma.
'Debug.Print Join(sql_where_c, ",")
'Debug.Print Join(sql_where_c_type, ",")
'Debug.Print Join(sql_where_v, ",")
'Debug.Print Join(sql_orderBy_c, ",")
' Construct SQL statement (pre-execution).
loSQL = "SELECT [" & stFldToConcat & "] FROM ["
loSQL = loSQL & stTable & "] WHERE "
' Construct 'WHERE' clause.
' Append each column and associated column value in arrays to the SQL
' statement constructed so far in following format:
' WHERE column1 = column1value AND column2 = column2value AND ...
For i = LBound(sql_where_c_type, 1) To UBound(sql_where_c_type, 1)
' Check column values data type and represent them appropriately in
SQL.
Select Case sql_where_c_type(i)
Case "String":
' String values identified by quotes "value".
loSQL = loSQL & "[" & sql_where_c(i) & "] =" & cQ &
sql_where_v(i) & cQ & " AND "
Case "Long", "Integer", "Double":
' AutoNumber is Type Long
loSQL = loSQL & "[" & sql_where_c(i) & "] = " & sql_where_v(i)
& " AND "
Case "DateTime":
' DateTime values identified by hash symbol #DateTime#
loSQL = loSQL & "[" & sql_where_c(i) & "] = " & cH &
sql_where_v(i) & cH & " AND "
Case Else
' Cannot identify column data type so Error.
GoTo Err_fConcatFld
End Select
Next i
' Strip off the trailing 'AND ' from the SQL Statement constructed so far.
loSQL = Left(loSQL, Len(loSQL) - 5)
' Append 'ORDER BY' clause to SQL Statement along with column names to
order by.
loSQL = loSQL & " ORDER BY " & "[" & Join(sql_orderBy_c, "],[") & "]"
' Useful Debug code - outputs constructed SQL statements (pre-execution).
' Debug.Print loSQL
' Execute SQL statement and retrieve generated recordset.
Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)
'Are we sure that duplicates exist in stFldToConcat
With lors
' Check recordset is not empty.
If .RecordCount <> 0 Then
' Start concatenating records
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & concat_del
' Useful Debug code - outputs each step of concatenation.
' Debug.Print lovConcat
.MoveNext
Loop
Else
' Recordset empty so Error.
GoTo Exit_fConcatFld
End If
End With
' Useful Debug code - output complete string after concatenation.
' Debug.Print lovConcat
' Trim trailing ';'.
fConcatFld = Left(lovConcat, Len(lovConcat) - 2)
' Useful Debug code - output string returned by function.
' Debug.Print fConcatFld
' Clean up.
Exit_fConcatFld:
Set lors = Nothing: Set lodb = Nothing
Exit Function
' Error Handling.
Err_fConcatFld:
MsgBox "Error#: " & Err.Number & vbCrLf & Err.Description
Resume Exit_fConcatFld
End Function
Any help is much appreciated.
Thanks in advance.