Unable to retrieve long string from calculated field through recordset

[Henrik]

The problem is (using MS Access 2003) I am unable to retrieve long
strings (255 chars) from calculated fields through a recordset.

The data takes the trip in three phases:

1. A custom public function returns a long string. This works.

2. A query has a calculated field based on the custom function above.
This works when the query is run directly.

3. A recordset is opened based on the query in 2, but retrieving the
long string through the recordset field fails.

What is returned is the first 255 characters correctly, then the rest
of the string is mangled ("funny" characters).

To reproduce this problem:

1. Create a custom function:

public Function getLongString()
getLongString = String(254,".") & "X" & String(255,"A")
End Function

2. Create a new query named "QRY_TESTLONGSTRING" with one field:

LongString:getLongString()

3. Create a test public function:

public Function testGetLongString()
dim r as recordset
set r = currentdb.openrecordset("QRY_TESTLONGSTRING")
testGetLongString = r.fields(0) ' or r.fields(0).Value for slightly
different results, still mangled
set r = nothing
End Function

4. Open the immediate pane (Ctrl-G)

write

? testGetLongString

5. hit Enter to run the function and see the output

You will see that the dots up to the "X" are output correctly, but the
"A"'s appear as random characters (mangled).

I have not been able to find a workaround for this, and would be most
grateful for ideas.

Thanks,

- Henrik

[Rich P]

An Access Table field of type Text has a limit of 255 chars. Try
setting your recordset object to a memo field for the long string.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[deluxeinformation]

Henrik wrote:

The problem is (using MS Access 2003) I am unable to retrieve long
strings (255 chars) from calculated fields through a recordset.

The data takes the trip in three phases:

1. A custom public function returns a long string. This works.

2. A query has a calculated field based on the custom function above.
This works when the query is run directly.

3. A recordset is opened based on the query in 2, but retrieving the
long string through the recordset field fails.

What is returned is the first 255 characters correctly, then the rest
of the string is mangled ("funny" characters).

To reproduce this problem:

1. Create a custom function:

public Function getLongString()
getLongString = String(254,".") & "X" & String(255,"A")
End Function

2. Create a new query named "QRY_TESTLONGSTRING" with one field:

LongString:getLongString()

3. Create a test public function:

public Function testGetLongString()
dim r as recordset
set r = currentdb.openrecordset("QRY_TESTLONGSTRING")
testGetLongString = r.fields(0) ' or r.fields(0).Value for slightly
different results, still mangled
set r = nothing
End Function

4. Open the immediate pane (Ctrl-G)

write

? testGetLongString

5. hit Enter to run the function and see the output

You will see that the dots up to the "X" are output correctly, but the
"A"'s appear as random characters (mangled).

I have not been able to find a workaround for this, and would be most
grateful for ideas.

Thanks,

- Henrik

I have seen a couple of references to this problem over the years in
the newsgroups but never any solutions. It appears that the problem
occurs when DAO has to decide what datatype to use for the calculated
fields in your recordset. Apparently when DAO encounters a calculated
field that returns a VBA 'string' datatype it decides to assign the
dbText type (rather than the more appropriate dbMemo) to the
corresponding recordset field (you can confirm this by inserting the
line

debug.print r.fields(0).Type

after the line

set r = currentdb.openrecordset("QRY_TESTLONGSTRING")

in your function above). The only suggestion I could offer is that you
use your query to populate a temporary table that explicitly uses a
memo field to store the output of your calculated field. Then you
could base a recordset on the temporary table and rest assured that DAO
will use the correct datatype for the fields. Other than that you'll
have to rewrite your process so that you don't base a recordset field
on the long calculated field returned by your query, i.e., shift more
processing to the SQL engine or to the VBA engine.

Bruce

[Henrik]

Thanks Bruce,

The calculated field is indeed a dbText type. I rather thought there
was no straightforward solution.

Best,

- Henrik

de***************@gmail.com wrote:

Henrik wrote:

The problem is (using MS Access 2003) I am unable to retrieve long
strings (255 chars) from calculated fields through a recordset.

The data takes the trip in three phases:

1. A custom public function returns a long string. This works.

2. A query has a calculated field based on the custom function above.
This works when the query is run directly.

3. A recordset is opened based on the query in 2, but retrieving the
long string through the recordset field fails.

What is returned is the first 255 characters correctly, then the rest
of the string is mangled ("funny" characters).

To reproduce this problem:

1. Create a custom function:

public Function getLongString()
getLongString = String(254,".") & "X" & String(255,"A")
End Function

2. Create a new query named "QRY_TESTLONGSTRING" with one field:

LongString:getLongString()

3. Create a test public function:

public Function testGetLongString()
dim r as recordset
set r = currentdb.openrecordset("QRY_TESTLONGSTRING")
testGetLongString = r.fields(0) ' or r.fields(0).Value for slightly
different results, still mangled
set r = nothing
End Function

4. Open the immediate pane (Ctrl-G)

write

? testGetLongString

5. hit Enter to run the function and see the output

You will see that the dots up to the "X" are output correctly, but the
"A"'s appear as random characters (mangled).

I have not been able to find a workaround for this, and would be most
grateful for ideas.

Thanks,

- Henrik

I have seen a couple of references to this problem over the years in
the newsgroups but never any solutions. It appears that the problem
occurs when DAO has to decide what datatype to use for the calculated
fields in your recordset. Apparently when DAO encounters a calculated
field that returns a VBA 'string' datatype it decides to assign the
dbText type (rather than the more appropriate dbMemo) to the
corresponding recordset field (you can confirm this by inserting the
line

debug.print r.fields(0).Type

after the line

set r = currentdb.openrecordset("QRY_TESTLONGSTRING")

in your function above). The only suggestion I could offer is that you
use your query to populate a temporary table that explicitly uses a
memo field to store the output of your calculated field. Then you
could base a recordset on the temporary table and rest assured that DAO
will use the correct datatype for the fields. Other than that you'll
have to rewrite your process so that you don't base a recordset field
on the long calculated field returned by your query, i.e., shift more
processing to the SQL engine or to the VBA engine.

Bruce

[Henrik]

Bruce,

The workaround I've implemented is a real hack:

I pass my handling routine the ordinal index of the calculated field in
the recordset, the name of the function for the calculated field, and
the fieldname of the recordset field value to be used as the parameter
to the calculated field function.

Then I store the return value of the calculated field in a variable,
and (for the calculated field) check to see if it is greater than 255
characters. If it is, I assume it is mangled. I then run the
calculating function directly using Application.Run and the appropriate
arguments. That works. Ugly, but it works.

The salient line is

theValue = Application.Run theLongStringFunction,
theRecordset.Fields(theParameterFieldName)

- Henrik

de***************@gmail.com wrote:

Henrik wrote:

The problem is (using MS Access 2003) I am unable to retrieve long
strings (255 chars) from calculated fields through a recordset.

The data takes the trip in three phases:

1. A custom public function returns a long string. This works.

2. A query has a calculated field based on the custom function above.
This works when the query is run directly.

3. A recordset is opened based on the query in 2, but retrieving the
long string through the recordset field fails.

What is returned is the first 255 characters correctly, then the rest
of the string is mangled ("funny" characters).

To reproduce this problem:

1. Create a custom function:

public Function getLongString()
getLongString = String(254,".") & "X" & String(255,"A")
End Function

2. Create a new query named "QRY_TESTLONGSTRING" with one field:

LongString:getLongString()

3. Create a test public function:

public Function testGetLongString()
dim r as recordset
set r = currentdb.openrecordset("QRY_TESTLONGSTRING")
testGetLongString = r.fields(0) ' or r.fields(0).Value for slightly
different results, still mangled
set r = nothing
End Function

4. Open the immediate pane (Ctrl-G)

write

? testGetLongString

5. hit Enter to run the function and see the output

You will see that the dots up to the "X" are output correctly, but the
"A"'s appear as random characters (mangled).

I have not been able to find a workaround for this, and would be most
grateful for ideas.

Thanks,

- Henrik

I have seen a couple of references to this problem over the years in
the newsgroups but never any solutions. It appears that the problem
occurs when DAO has to decide what datatype to use for the calculated
fields in your recordset. Apparently when DAO encounters a calculated
field that returns a VBA 'string' datatype it decides to assign the
dbText type (rather than the more appropriate dbMemo) to the
corresponding recordset field (you can confirm this by inserting the
line

debug.print r.fields(0).Type

after the line

set r = currentdb.openrecordset("QRY_TESTLONGSTRING")

in your function above). The only suggestion I could offer is that you
use your query to populate a temporary table that explicitly uses a
memo field to store the output of your calculated field. Then you
could base a recordset on the temporary table and rest assured that DAO
will use the correct datatype for the fields. Other than that you'll
have to rewrite your process so that you don't base a recordset field
on the long calculated field returned by your query, i.e., shift more
processing to the SQL engine or to the VBA engine.

Bruce

[deluxeinformation]

Henrik,

Thanks for sharing your solution.

Bruce