Blank spaces in data fields after copying data with SQL

[CrArC]

Hello there, I'm having a problem very much like the one described by this user where an SQL statement I am using to read data from one table and copy it to another is filling the remaining field space with blanks.

I didn't notice until a VBA module I worked on later on which was to read data from the new table and copy the selected record into a form said it could not find the records. The code:

Do Until blnFound = True Or rs.EOF
'DEBUG: answer = MsgBox("ok", vbYesNo)
'DEBUG: MsgBox rs("Account Name")
If rs("Account Name") = strAName Then
        MsgBox "DEBUG: FOUND " & rs("Account Name")
    blnFound = True
End If

rs.MoveNext
Loop

The record selector just goes straight to EOF because rs("Account Name") never matches strAName (as strAname has all those damned spaces after the useful data).

I wondered if perhaps there was some way to use the code in the other thread to read the length of useful data and then chop off the garbage spaces afterwards, but I'm worried it would be incredibly slow when running through the thousands of records. Also, the field has legitimate spaces in between words (most of the account names are two or three words in length).

The SQL statement responsible is as follows:

strASQL(i) = "INSERT INTO " & strTableName & "([CAccount No], [Short Name], " & _
"[Account Name], [Telephone], [Fax], [Contact Name], " & _
"[Mail address1], [Mail address2], [Mail address3], [Mail address4], " & _
"[Analysis1], [Analysis2], [Analysis3])" & _
" SELECT [CAccount No], [Short Name], " & _
"[Account Name], [Telephone], [Fax], [Contact Name], " & _
"[Mail address1], [Mail address2], [Mail address3], [Mail address4], " & _
"[Analysis1], [Analysis2], [Analysis3] " & _
"FROM tblCustomer WHERE [" & strField(i) & "] LIKE '" & strValue(i) & "';"

strASQL, strField and strValue are arrays and this SQL statement runs in a loop, as this is part of a search form for multiple possible entries. It all works fine except it always adds those damned spaces

Any suggestions?? Cheers.

 

[CrArC]

Sorry to nag, has anyone got any suggestions? I've been searching for information and I'm working to an increasingly tight deadline here, heheh... any help appreciated!!!

 

[RuralGuy]

Have you tried using the Trim() function as was recommended in the other thread?

 

[CrArC]

I've thought about using it but it poses the following issues:

- Ability to detect when a space is legitimate and when it is not.
- Potentially slow search times if a lot of results need trimming.

I guess the slow search times won't be a problem unless thousands of results are being returned, which they won't (unless some idiot decided to search with a wildcard such as *, heheh).

If I started from the right hand side of the data in the field and worked left, I could probably remove every illegitimate space because it would stop when the end character of the legitimate data was reached.. I'll try giving that a go.

Is there no easy "no spaces" switch?

 

[RuralGuy]

Trim() *only* removes Leading and Trailing spaces, not all of the spaces. You can use RTrim() if you only want to remove the trailing spaces.

 

[CrArC]

Yeah I tried:

rs("Account Name") = RTrim(rs("Account Name"))

But it hasn't done anything. I think I've figured out why; the table I'm editing data on seems to fill all the remaining space in field length in with blank spaces itself. So the code works, but is simply being "spaced out" again...

I found out because if I entered some random data into the table manually, it had spaces after it too. The other tables in the database are fine, this one is the only one with this affliction. It was created using an SQL statement in VB, does that have something to do with it??

 

[Rich]

Why if all this data exists in one table are you copying it to another?

 

[RuralGuy]

Access is pretty frugal with disk space. Are you sure your spaces are not being inserted when you fill your array?

 

[CrArC]

They aren't, it has to be the table because if I enter data manually the same thing happens.

I copy the data to another table because I only want to display the data that matches the search criteria (several fields a user can enter data into before clicking "search") The sub uses SQL with the arrays to search the same table (tblCustomers) by each field the user choses and with the criteria chosen for that field, then amends the resultant records to the new table, which is linked to a subform to display the data.

I've managed to circumvent the issue! I used RTrim(strPAName) and trimmed it "on the fly", so the data is allowed to remain 'spacious' in the table, and when a record is selected for processing (so the data in field Account Name is read, for instance) it simply crops the data after placing it in the string. This is fine for what I need! No good if someone needed a clean table of information, though.

 

[RuralGuy]

So you are saying that the field in the tblCustomer table does *not* have all of the trailing spaces but the same field in the "strTableName" table *does* after you run that query?

 

[The_Doc_Man]

SQL doesn't insert spaces to pad text fields. That is contrary to the string paradigm used by Access. Is there a default format or some other default data implementation at work here? Is the database ODBC-connected or linked to another product? You aren't telling us something. What you ARE telling us is not normal Access behavior. Unless this involves some outside influence. Working with incomplete information, we are going to be hard pressed to find solutions. What version of Access are you using?

 

[RoyVidar]

"But it hasn't done anything. I think I've figured out why; the table I'm editing data on seems to fill all the remaining space in field length in with blank spaces itself. So the code works, but is simply being "spaced out" again..."

This is default behaviour with fixed width text fields in Jet. A simple way to reproduce the behaviour, is to run the following DDL

CREATE TABLE myTable (myText Text(25), myChar Char(25))

Enter some text in both columns "asdf", and check the differences, for instance, run something like the the following

dim rs as dao.recordset
set rs = dbengine(0)(0).openrecordset("myTable")
debug.print rs.fields(0).name, rs.fields(0).value & "@", len(rs.fields(0).value), rs.fields(0).properties("Attributes") and dbfixedfields
debug.print rs.fields(1).name, rs.fields(1).value & "@", len(rs.fields(1).value), rs.fields(1).properties("Attributes") and dbfixedfields

Fixed length text fields can be created both through DDL and other methods, but I don't think you can create it through the interface. I suspect, that there is a remote possibility it can happen through SELECT INTO, but I don't know.

If this is the case, I think you should be able to do

ALTER TABLE myTable ALTER COLUMN myChar Text(25)

which hopefully will make it dynamic again, but you'd need to update, I think, for the text to be changed

UPDATE myTable SET myChar = Trim(myChar)

But PLEASE on A COPY first

 

[RuralGuy]

Very interesting Roy. Thanks for the input.