Exporting a Memo Field with a Custom Result Column giving a Data Type Mismatch

[thealy]

I have the following query that runs fine where SKU_GP_EXT_DSC is a memo field.

SELECT Formatting(SKU_GP_EXT_DSC)
FROM SKU_GROUP

The custom module Formatting works fine.

Public Function Formatting(Text As String) As String
Dim outString As String
outSring = ""
If Len(Text) > 0 Then
outString = Replace(Text, vbCrLf, "\line")
End If
Formatting = outString
End Function

But when I go to export the results of this query to a text file, I receive the error message, "Data type mismatch in criteria expression". I cannot find a way arround this issue. Any ideas?

Thanks in advance.

 

[RuralGuy]

Are you sure you are not using a reserved word and confusing Access?
List of reserved words in Access 2002 and Access 2003
List of Microsoft Jet 4.0 reserved words
Special characters that you must avoid when you work with Access databases

 

[thealy]

Nope no reserved words

When I perform a File / Export and select the Save Formatting check box while saving it to a Text file, it allows me to export it but it then includes all the extra formatting grid lines which of course I don't want because I am importing this text file into another application.

When I do not have the Save Formatted checkbox checked, it complains about the type mismatch moments after pressing the Export All buton on this Export Query 'filename' to dialog box. I am also able to export to excel without problems but I have more then 64k rows so I realy need to use the text file option.

Thanks for your help!

 

[RuralGuy]

I have not used export before but how about changing Text as used in your function to some other name and see what happens?

 

[thealy]

Changing the word Text didn't fix it.

Changing the word Text didn't fix it.

But thanks.

 

[RuralGuy]

Thanks anyway for humoring this old codger. Can you get the debugger to point to the exact line where it feels the error occurs? You may need to set your system to break on all errors.