I have a expression in a query that returns a longgg result, and when I try to export it to text, it gets truncated. I’ve split it up into sections in different fields, but that’s a bitch. Any ideas on a fix?
See if this Knowledge Base article will solve your problem.
It’s not a number. It’s text. But I found a solution.
Hmmm… That article told me to make an append query to get the fields back to Memo, which I did. But the fields still contain the line breaks. How would I get rid of them?
Since no one has answered yet, I’ll ask again…
How would I change all of the line breaks contained in a field in a table to nothing?
jbird3000, your lack of responses is probably due to the vagueness of your question. It is difficult to tell what you are after. What do you mean by “line breaks”? Are you referring the Carriage return–linefeed combination (Chr(13) + Chr(10)) that separate one paragraph from another? If so, likely they are there because they are present in your original text. To remove them, you will need to write a function in VB that accepts the text as an argument and returns the text without the character(s) you wish to exclude.
If this is not what you are after, try providing a few more details and maybe I can help.
Yes, this is exactly what I’m talking about. And that is the function I want to make. But how?
Let me see if I can help. I’ll give you some code I wrote a long time ago, just to get you started.
Public Sub CleanString(ByVal theDb As String, _
ByVal theTable As String, _
ByVal theField As String, _
Optional ByVal theInvalid As String = vbCrLf)
'removes invalid chars from string field in table
Dim myDb As Database
Dim myRs As Recordset
Dim myX As Integer
Dim myStr As String
Dim myBool As Boolean
Set myDb = OpenDatabase(theDb)
Set myRs = myDb.OpenRecordset(theTable, dbOpenDynaset)
'but first, I need to find out which field is the right one to update
With myRs
For myX = 0 To .Fields.Count - 1
If .Fields(myX).Name = theField Then
myBool = True
Exit For
End If
Next
If Not myBool Then
MsgBox "not found"
Exit Sub
End If
'now update the field
Do Until .EOF
.Edit
If Not IsNull(.Fields(myX)) Then
myStr = StripChars(.Fields(myX), theInvalid)
.Fields(myX) = myStr
End If
.Update
.MoveNext
Loop
End With
myRs.Close
myDb.Close
Set myRs = Nothing
Set myDb = Nothing
Notice the call to the StripChars function. It does all the hard work of removing any unwanted characters.
Public Function StripChars(ByVal StripString As String, _
Optional ByVal BadChars As String = "()-+,.;:^""'~¨") As String
Dim i As Integer
Dim GoodString As String, tmp As String
For i = 1 To Len(StripString)
tmp = Mid(StripString, i, 1)
If InStr(1, BadChars, tmp) = 0 Then
GoodString = GoodString & tmp
End If
Next i
StripChars = GoodString
End Function
Be sure to add error checking and mucho comments to indicate what you are doing. Also, you might want to update the database code to use ADO instead of DAO.
Now you can call the CleanString function, pass it the name of the database, table and field you wish to clean, along with any invalid characters you wish to remove. Note the avoidance of “hardcoding” any values to make it as useful as possible in future situations.
Hope this helps. Let me know if you get stuck anywhere.
And one last caveat – ALWAYS test code like this on a backup copy of your data, to ensure there are no undesirable side effects.
Will that work on a table, without ever involving a query? If so, how do I do it?
No need for a query. Just create a simple form with a command button on it. Inside its click event, place code to call the CleanString function with the proper parameters.
Yeah, how do I refer to the current database? Whatever I’ve tried, it’s either been ‘type mismatch’ or it didn’t do anything.
Well I tried many things… The name of my database, the file name, Currentdb… Either errors or no effect. So I tried disabling the code having to do with it, but when I run it, it says “Invalid or unqualfied reference” for the line
For myX = 0 To .Fields.Count - 1
highlighting “.Fields”
I need this thing working SOON. So if I’m totally hopeless on this avenue, I’ve found another way of doing it. But, alas, I hit a snag there.
Is there a way of exporting a query/table to text WITHOUT having anything seperating the fields (not a space, not an apostrophe, NOTHING). If I try to put in nothing, I’ll tells me I can’t leave it blank.
I’m sorry I didn’t notice your question about the current database name earlier. Just use Currentdb.name to reference the name of the current database. Works like a champ.
Hzah! Geez, why didn’t I think of that…
After a couple of tries, I got it to work. I had to make a new Text Export Specification for some reason, but now it works A-OK. I tip my hat to you, sir (maam?).
You’re entirely welcome, and I am most assuredly 100% red-blooded male. 
Just be sure to spend enough time to understand every line of code so that you can improve your programming skills, and resolve to do the same and help some programmer newbie in the future.