Hi,
I am working with Access 97 and experiencing difficulty in 97 returned errors when trying to perform a left function on a returned memo field. Details are below. Has anyone experienced this and have a workaround for it? Do you know if this is a known error in 97?
The purpose of this query is concatenate a series of comments registered against a specific demand id in one field (fConcatFld) which works fine in 2003 but only works in 97 when the total length of all comments being concatenated are less than 256.
Query
SELECT DemandNo, DemandDate, Comments FROM tblTemp WHERE (False)
UNION ALL SELECT DemandNo, DemandDate, Left(fConcatFld("tblDemandProgress","DemandNo;DemandDate","DemandProgress","String;DateTime",[DemandNo] & ";" & Format([DemandDate],"dd-mmm-yy"),"DemandProgressDate"), 4000) AS Comments
FROM qryEPMExtract_GetDistinct_Demands;
fConcatFld returns a single concatenated string to the query (details of fConcatFld below). Many of the concatenated strings returned by 'fConcatFld' are larger than '255', which means storing the comment in a text field would be inappropriate due to its length of 255 with a memo field better (storage of 2.5GB).
Access by default uses the data type of the field from which the comments are derived from (which in the database in this case is a text field) as the data type for the field the comments will be inserted into. To avoid automatic truncation of concatenated comments to the database comments field type (225 length) we have converted the field to a memo by performing a union with a temporary table (tblTemp) which contains an empty comment field of type memo.
This works successfully with concatenated comments of up to 4000 in Access 2003 but only with concatenated comments of total length of up to 255 in 97. When this query is executed in 97 the function executes, concatenating 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:
MSACCESS.EXE - Application Error
Instruction at 0x77fcbee8 referenced memory at 0xfffffffe. Memory could not
be "read"
When the argument in the 'LEFT' function (shown in query above) is reduced to <=255', the query executes successfully concatenating all comments and displaying the results. Can any one help?
'==============================================================
' 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
I am working with Access 97 and experiencing difficulty in 97 returned errors when trying to perform a left function on a returned memo field. Details are below. Has anyone experienced this and have a workaround for it? Do you know if this is a known error in 97?
The purpose of this query is concatenate a series of comments registered against a specific demand id in one field (fConcatFld) which works fine in 2003 but only works in 97 when the total length of all comments being concatenated are less than 256.
Query
SELECT DemandNo, DemandDate, Comments FROM tblTemp WHERE (False)
UNION ALL SELECT DemandNo, DemandDate, Left(fConcatFld("tblDemandProgress","DemandNo;DemandDate","DemandProgress","String;DateTime",[DemandNo] & ";" & Format([DemandDate],"dd-mmm-yy"),"DemandProgressDate"), 4000) AS Comments
FROM qryEPMExtract_GetDistinct_Demands;
fConcatFld returns a single concatenated string to the query (details of fConcatFld below). Many of the concatenated strings returned by 'fConcatFld' are larger than '255', which means storing the comment in a text field would be inappropriate due to its length of 255 with a memo field better (storage of 2.5GB).
Access by default uses the data type of the field from which the comments are derived from (which in the database in this case is a text field) as the data type for the field the comments will be inserted into. To avoid automatic truncation of concatenated comments to the database comments field type (225 length) we have converted the field to a memo by performing a union with a temporary table (tblTemp) which contains an empty comment field of type memo.
This works successfully with concatenated comments of up to 4000 in Access 2003 but only with concatenated comments of total length of up to 255 in 97. When this query is executed in 97 the function executes, concatenating 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:
MSACCESS.EXE - Application Error
Instruction at 0x77fcbee8 referenced memory at 0xfffffffe. Memory could not
be "read"
When the argument in the 'LEFT' function (shown in query above) is reduced to <=255', the query executes successfully concatenating all comments and displaying the results. Can any one help?
'==============================================================
' 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
