Help with exporting data

[wee]

Hello,



I am trying to export some data out of my Access Query, but it seems to be truncating the data in my table at 255 characters. I have some data in a memo field and need to export the whole field as one.

Is there a simple solution to this?
Thanks!
-wee

[RuralGuy]

See if this link helps a bit.

[wee]

The fields only truncates on export. I am trying to export to a csv file. I don't have too much access experience, so I really would prefer not to split the fields up. Are there any other alternatives?

[RuralGuy]

Are you using a query to create the recordset to export? If so, then post the SQL for the query here please.

[wee]

I used the Query Wizard and selected Simple Query Wizard.

Is there a way to make a form, write a vb script, and export it? I need to export to a csv file if possible.

[RuralGuy]

Start by going into the query in design mode and switch to SQL view and copy and paste what you see into a post here.

[wee]

This?

SELECT Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([Table1.Name],"Â",""),"Ã",""),"¬",""),"¢",""),"„",""),"â",""),"'","\'"),'"',"") AS product, Manufactures.Mid, Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace([Description],"Â",""),"Ã",""),"¬",""),"¢",""),"„",""),"â",""),"'","\'"),'"',"") AS [desc], Categories.cid
FROM (Manufactures INNER JOIN Table1 ON Manufactures.[Name] = Table1.[Manufacture]) INNER JOIN Categories ON Table1.[Category] = Categories.[name]
WHERE ((Not (Manufactures.Mid)=0) AND (Not (Categories.cid)=0));

[RuralGuy]

That is certainly no simple query. Have you considered puting the Replace functions in a different query?

[dcrake]

Blimey, for a start get rid of all those replaces. Create a public function, such as

 
Public Function StripBadCharacters(AnyString) As String
 
Dim strString As String
 
strString = Replace(AnyString, "Â","")
strString = Replace(strString, "¬","")
strString = Replace(strString, "¢","")
strString = Replace(strString, "„","")
strString = Replace(strString, "â","")
strString = Replace(strString, "'","")
strString = Replace(strString, "\","")
strString = Replace(strString, ""","")
 
StrBadCharacters = strString
 
End Function

Then in your query

it becomes


Product:StrBadCharacters (Table1.[Name])

Also naming a field Name is very bad this is an Access Reserved word you can get into alsorts of problems, you nust ensure that you enclose it with [] to tell Access this is a field.

Rebuild your query and retry, see what happens then.

David

[wee]

Great! That works awesome

Sorry for slow reply, have been busy with school

One last question, is there a way to export to a tabbed csv file in access via VBA, so I can click a button on my form to export automatically?

[dcrake]

Yes you can, suggest you first do this via a macro using the TransferSpreadsheet command. Then convert your macro to VB and paste the code behind you on click event of the button.

David