Sorting Memo Field

Question

I extracted data from a word document by reading the lines with the Open function, and read the data into a table:

Open strInputFile For Input As #1

Line Input #1, strTextLine

...

strtopic = Trim(strTextLine)

...

With rs

          .AddNew

          .Fields("Topic") = strtopic

          .Update

End With

Because some of the data in this field was more than 250 characters long, I made the field a memo field. When I opened the table, I found that there were records that contained no characters whatsoever when I wanted records that were not null and wondered if the Trim(strTextLine) failed to work. If a record contained only spaces, does the trim() function convert it to null? How can I avoid this type of problem?

2. When I sorted the table (Ascending), records that started with numeric or special characters like ", - , (, or * sorted first. This I can understand. But what surprised me was that there were other records as well that did not contain special characters and stayed on top of the rest. These records are few, but then I still wonder what could have produced them. And is there another way to sort a memo field?

Answer 1

One thing that's not obvious is that what looks like empty space in access may not be empty space at all.

When you import from Word, there are characters called non-printing characters that tell the word doc to tab or indent or something else that create what looks like a blank space but is a character that you can't see.

Now access does not make these characters, but you get them when you import from Word.

Trim will remove the space character, but not the other ones.

What I often do under these circumstances is loop through the string, printing it out character by character, to see just what those characters are:

Dim lngLoop As Long

   For lngLoop = 1 To Len(strText)

      Debug.Print Asc(Mid(strText, lngLoop, 1)) & " (" & Mid(strText,

lngLoop, 1)  & ")"

   Next lngLoop

The above lets you see those characters and you might want to make an update query that replaces those characters with a space, or just remove them completely.

You can use the Replace function in an update query to do this.

posted Current UTC (or GMT/Zulu)-time used: Friday, 18 November 2011 at 04:16:21

Answer 2

Trim will return a ZLS (Zero Length String) when the field contains noting nut space characters.

The other thing that might be happening is if the field was never set to anything (and its AllowZeroLength property is set to Yes and Required is also set to Yes).

I think you want to set your query's criteria to <>"" And Is Not Null

Both ZLS and Null values will sort at the top of an Ascending sort.

Answer 3

Hi Guys,

I have a table that looks like this:

Egyptians had a system of astronomical calculation some five thousand years before Alexander the Great, EV 128	EV 128
Waters of Immortality, The, JS 196-98	JS 196-98
an old story is told of Alexander the Great when he was a boy, LW1 175-75	LW1 175-75
could attribute his success in military matters to his ability to get out of the body and view, EKSW 119-29	EKSW 119-29
had come into India intent on conquest, HFG 240	HFG 240
sprouts, HMH 104	HMH 104
HMH 38, 39-40, 72	HMH 38, 39-40, 72
good remedy to help balance blood pressure, HMH 89-91	HMH 89-91
aid to lagging appetites, HMH 91	HMH 91
ASD 74	ASD 74
HSS 246	HSS 246
It’s Good to Be Alive, HSS 301-02	HSS 301-02

The BookLink column was parsed out of the Topics1 column. From the BookLink column, I then extracted the BookCode (as BC) which is comprised of all characters before the first space (" "). The BookCode column displayed properly in a query based on the aaTopicsFinal table which has been extracted from a word document. This table also has a TopicID column not shown here. This is the BookCode (BC) column which displays normally as "EV", "JS", "LW1" etc:

BC: IIf(IsNull([BookLink]),Null,Left([BookLink],InStr([BookLink]," ")-1))

 However I get #Name? error when I try to get the BookID (as BI) through this method:

BI: IIf(IsNull([BookLink]),Null,(Select BookID From aaBookList Where BookCode=Left([BookLink],InStr([BookLink]," ")-1)))

When I run the query, everything displays normally, apparently with correct data, for a few seconds, then I get error message: "Data type mismatch in criteria expression." Thereafter, all columns return #Name?. Incidentally, I did not get better results with DLookUp as I get the #Error in the column. How should I go about it?

The aaBookList table has BookID as the primary key (autonumber) and the BookCode as text.

Answer 4

Thanks Marshall and Jeanette. Your explanations are superb and taken. And I appreciate your suggested code too, Jeanette.