Need to trim *all fields* across *all tables*

[skydivetom]

Minty:



Just to clarify... are you recommending unicode compression to FALSE (opposite of "But NOT when the fields have unicode compression turned on.")?

        For Each fld In flds
            If fld.Type = dbText Then
                fld.Properties("UnicodeCompression").Value = False
                'SQLString = SQLString & "[" & fld.Name & "] = Trim([" & fld.Name & "]),"
                SQLString = SQLString & "[" & fld.Name & "] = Left([" & fld.Name & "],InStr(1,[" & fld.Name & "],'  ')),"
            End If
            
        Next fld

If so, that throws an error "Property not found."

Also, all fields are data type = "Short Text" (vs. "Long Text"). Not clear on what should change?

[skydivetom]

Jack -- thanks for the additional function. I ran it and saw the same in the Immediate Window.

So, I'm still baffled as to why the 2 SEL queries still return 80. Any additional thoughts how to fix it?

** UPDATE **

I just refreshed my browser and saw the recommendations in post 14. I'll give it a whirl. Thanks.

[skydivetom]

Jack:

I'm now getting a syntax error in the ALTER statement. How should I correct it?

[orange]

Tom,
I wasn't the person who added the ADO code. Suggest Darkwind should advise.

[skydivetom]

Mea culpa... sorry, Jack!

[skydivetom]

Jack -- btw, when running your Review function, I get the following output:

Before: 80 <Notice                                                                          >
After: 80 <Notice>  6
Before: 80 <Notice                                                                          >
After: 80 <Notice>  6
Before: 80 <Notice                                                                          >
After: 80 <Notice>  6
Before: 80 <Notice                                                                          >
After: 80 <Notice>  6
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Notice                                                                          >
After: 80 <Notice>  6
Before: 80 <Notice                                                                          >
After: 80 <Notice>  6
Before: 80 <Notice                                                                          >
After: 80 <Notice>  6
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Critical                                                                        >
After: 80 <Critical>  8
Before: 80 <Notice                                                                          >
After: 80 <Notice>  6
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Critical                                                                        >
After: 80 <Critical>  8
Before: 80 <Notice                                                                          >
After: 80 <Notice>  6
Before: 80 <Notice                                                                          >
After: 80 <Notice>  6
Before: 80 <Notice                                                                          >
After: 80 <Notice>  6
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Critical                                                                        >
After: 80 <Critical>  8
Before: 80 <Notice                                                                          >
After: 80 <Notice>  6
Before: 80 <Notice                                                                          >
After: 80 <Notice>  6
Before: 80 <Notice                                                                          >
After: 80 <Notice>  6
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Error                                                                           >
After: 80 <Error>  5
Before: 80 <Critical                                                                        >
After: 80 <Critical>  8

It appears this differs from your results as yours don't show "80", right?

[orange]

Right, but I purposely turned the unicode compression OFF for field1 tbl_One.

I was getting the same output as you and confirmed that unicode compression was affecting the vba an/or SQL.

That's why I put the Len(Trim... on the After line. I noticed that Len(Trim(rs!field1)) gave the expected result.
When I remove the unicode compression from the field property, the before and after lengths for field1 were consistent.

I don't know what unicode compression will/could do in your application. Maybe someone else has some experience and could advise.

[skydivetom]

I see... thanks for the additional info.

Background:
- I have an data import routine which imports .log files into tables.
- The import routine itself works well.
- However, I earlier notice the issue with the LEN of the imported data values.
- I would have thought that it would be easy to clean up those unwanted characters.
- Apparently, that's not the case.

I am hopeful that Darkwind may come back w/ another suggestion. Would have never thought that the TRIM function won't work under certain conditions.

Cheers,
Tom

[darkwind]

Minty:

Just to clarify... are you recommending unicode compression to FALSE (opposite of "But NOT when the fields have unicode compression turned on.")?

Yeah, bit hasty there in my writing. This was pretty unclear. No need to do this at all unless the method is failing inexplicably. Basically, I wasn't sure what the effect of running an ALTER TABLE WITH COMPRESSION command would be if the property were already nominally set to Yes. Let's pin this part for now and come back later, if it comes back up, as you're still having other issues...

I'm now getting a syntax error in the ALTER statement. How should I correct it?

This is strange, as I'm not getting the same error. I've run the code on two different systems, one in Access 2013 and the other in Access 365 without issue. I don't have Access 2010, so I can't be sure if there's a version difference that's causing the problem.

In Access 2013, though, I did have to remove the UnicodeCompression condition, as it was not working properly. This shouldn't be an issue - it just would lead to running the ALTER TABLE command on fields that have already been set to enable Unicode Compression:

60                For Each fld In flds
65                    If fld.Type = dbText Then
70                        TextFldFound = True  ' a text field was found so continue
                          'Debug.Print thisTable.Name & "   " & fld.Name
75                        SQLString = SQLString & "[" & fld.Name & "] = Trim([" & fld.Name & "]),"
85                        cmd.CommandText = sCommandText & " ALTER COLUMN " & fld.Name & " TEXT(80) WITH COMPRESSION;"
90                        cmd.Execute
100                   End If
105               Next fld

However, that shouldn't do anything to affect the syntactical acceptability of the query...


The only other option I see would be to make new tables and copy the data over.... That's definitely possible, though a bit of a pain. I unfortunately don't have time to write that up now, but it shouldn't be terribly difficult from where you're ad.

[orange]

Can you make a copy of your database and turn unicode compression OFF for those text fields?
Then run you code to see if the Trim issue is fixed or continues.

Is there something in your system or subsequent systems that requires the unicode compression ON?

These are just thinking as I type since I have never seen the issue before nor heard of it being a "problem".
You might say I'm quite unicode compression naive.

I would like to see some authoritative article or link that describes the problem and scope clearly.

Here's a routine to check the value of the unicode compression property on dbtext fields,

Sub reviewProp()
    Dim db As DAO.Database, td As TableDef, fld As Field, prp As Property

    Set db = CurrentDb()

    For Each td In db.TableDefs
        If Not (td.Name Like "Msys*") Then

            For Each fld In td.Fields
                If (fld.Type = DB_TEXT Or fld.Type = DB_MEMO) Then

                    For Each prp In fld.Properties

                        If prp.Name = "UniCodeCompression" Then
                            'fld.Properties("UniCodeCompression") = True
                            Debug.Print td.Name & "  " & fld.Name & "  " & prp.Value
                        End If
                    Next
                End If
            Next
        End If

    Next
    MsgBox "Done"
    db.Close
End Sub

I set unicode compression OFF and ran your trimData, Review and ReviewProp. It all worked as expected.

[darkwind]

Okay, attached is an alternative method that works in my testing.

Instead of modifying the existing tables, this creates brand new ones. If you call it with TrimData(True), it will delete the old tables and re-name the new ones with the old names. Otherwise, the new tables will have the same names, but with "_1" appended.

On my copy, either way results in tables containing only the desired text, without extra spaces.

M100_TrimData.zip

Edit: Note that this function will only work with selected field types, as 'CREATE TABLE' has limited types available to it. If you try to run this on a database with types outside its scope, you'll get an error 5 invalid argument on the ToName call.

[skydivetom]

Jack:

Like you, I am also unfamiliar with UnicodeCompression. So, when you ask "Can you make a copy of your database and turn unicode compression OFF for those text fields?", I'm not entirely certain how to turn it off/on, etc.

I ran your reviewProp... please see attached JPG. So, looks like it's turned off now. Now, at the present time, I have so many variations of potential VBA scenarios, I honestly lost track which one is working for you.

Since yours is apparently working now, may I kindly request you post this working DB here (zipped format). That might save us continued headaches. Thank you in advance.

//

Darkwind:

Same here... kindly request to post your full version as well (if you don't mind). Thanks... 'much appreciated.

[darkwind]

Here's the full database. I've already run the procedure, so tbl_One_1 already exists... if you want to re-run it, you'll have to delete those new tables.

I really don't understand why it isn't working for you... the WITH COMPRESSION keyword is only supported through Jet OLD DB or ADO, but by going through CurrentProject.Connection.Execute, that should address that limitation, and it IS running fine on my machines. You don't have any sort of registry changes related to the engine, do you?

Trim Fields.zip

[orange]

Tom,
I repeat, I do not understand the unicode compression property.

To set it:
I went to the table design; selected a text field;looked at the field properties, saw UnicodeCompression and changed to NO.

Attached is the database in zip format.

[skydivetom]

Darkwind:

First, thank you for posting the full version. Allow me to recap in bullet format:

a. Created queries to test the LEN for the two tables (tbl_One_1, tbl_Two_1). See attached output "Snapshot.jpg"... this is GREAT!!!!
b. Naturally, based on a., the "ugly" black trailing characters are GONE, GONE, GONE in the tables. Yeah!!!

Next, I deleted the 2 newly created (dup) tables and wanted to recreate them. Forgive, how did you generate them? I open the module and ran the code (green triangle). But nothing happened. Well, I got a dialogue box referencing "Macro".
See attached JPG.

I know this may be a silly question, but how did you run it? I'm sure the answer is staring me in my face, but still...

Thank you,
Tom