trainling spaces left by update command

[garethl]

I am adding a text field to a temporary table using the following vb..

strSQL = "ALTER TABLE [TBL_TmpMonitorIn] ADD COLUMN [DupeCode]CHARACTER;"
CurrentProject.Connection.Execute strSQL

and then subsequently giving it a value like this..

strSQL = "UPDATE [TBL_TmpMonitorIn] SET DupeCode = [NumberOrName] & [Postcode] & str([MeasureCategory]);"
CurrentProject.Connection.Execute strSQL

[MeasureCategory] is a number, [NumberOrName] and [Postcode] are text

I end up with the [DupeCode] field padded with trailing whitespace so that in each record [DupeCode] is 255 characters long

Whats even stranger is that a subsequent UPDATE using Trim() or RTrim()
i.e.

strSQL = "UPDATE [TBL_TmpMonitorIn] SET DupeCode = RTrim([DupeCode]);"
CurrentProject.Connection.Execute strSQL

won't remove this whitespace.

This is driving me mad because the trainling spaces screw up any attempt to use that [DupeCode] as a linking field in queries.

Also building [DupeCode] on the fly in a query is appears to work as expected (no trailing spaces) but is ridiculously slow

 

[Sess]

Did you try putting the trim against each element you are concatinating like this
strSQL = "UPDATE [TBL_TmpMonitorIn] SET DupeCode = trim([NumberOrName]) & trim([Postcode]) & trim(str([MeasureCategory]));"

 

[garethl]

Good idea, just tried it... still the same

None of the fields I'm concatenating have these trailing spaces though so no idea where they are coming from!

 

[garethl]

Ok so I've tried some more stuff that could shed light on this..

I was wondering if any particular field was the problem so I've tried them all in pairs and on their own and I always get the trailing spaces.

More importantly I then tried something really stupid which was never going to work...

strSQL = "UPDATE [TBL_TmpMonitorIn] SET DupeCode = '[NumberOrName]) & Trim([Postcode]) & Trim(str([MeasureCategory]))';"
CurrentProject.Connection.Execute strSQL

(notice the single quotes)

Anyway altough a silly idea this was actually quite informative as instead of writing the literal string

"[NumberOrName]) & Trim([Postcode]) & Trim(str([MeasureCategory]))" into every record

it wrote that literal string followed by a load of spaces still

In other words it doesn't matter what I put into the update I still always get these spaces

strSQL = "UPDATE [TBL_TmpMonitorIn] SET DupeCode = 'hello';"
CurrentProject.Connection.Execute strSQL

generates trailing spaces

 

[gemma-the-husky]

without being silly

what makes you think there IS trailing space in the field [dupecode] - how are you seeing this, to know its there?

 

[Sess]

I have brought stuff across from SageAccPac via ODBC and every field is completely full. You know this by trying to type anything into a field and it will not allow because even if you only see 4 characters the remainder of the 64 character text field is filled with "spaces".
So you need to use the Trim on every text field you bring over if you need to manipulate the data.
If there is anything you edit or send back to the SageAccPac database, it very nicely appends the missing space for you.

 

[gemma-the-husky]

maybe its something to do with the sageaccpac then - maybe the data coming across is full width.

 

[gemma-the-husky]

what size is dupecode in the table you have created?

 

[gemma-the-husky]

reading again


strSQL = "UPDATE [TBL_TmpMonitorIn] SET DupeCode = [NumberOrName] & [Postcode] & str([MeasureCategory]);"

what is str?

an unknown function?, or one of your own.

the correct function is cstr - and it should capialise to Cstr when its checked. so maybe that is trhe problem

 

[georgedwilkinson]

Depending on your backend, "char" type columns are padded. If you don't want padded fields, change your data type to something that doesn't pad, like varchar, varchar2, text, or whatever your backend uses.

 

[Sess]

I do not know if this helped garethl or not but can you tell me if padding can be toggled on or off in MS Access or is this something that a module needes to be written and run?
Thank you

 

[garethl]

Hello

Thanks for all your responses.. sorry I'm only just back online I don't work Fridays.

Backend is Jet and I'm not using SageAccPac

Str is string conversion function with my edition of VB - It also gets autocapitalised and the VB IDE asks you for a number argument when you type str(

I will try CStr though

As to how I know the spaces are there..

- I first knew something was wrong when I tried to use the field to link tables and no records were ever returned
- So I copied the code and then did a simple text search on the table I was trying to link to and sure enough the code existed
- I then noticed that when you had the table opened up in table view you could see the spaces and could navigate the cursor along through them
- Querying the table with len([DupeCode]) returns 255 on every record

georgedwilkinson's suggestion sounds like the most likely culprit to me so I'm going to try VARCHAR and repost

thanks..

 

[garethl]

Ok I changed

strSQL = "ALTER TABLE [TBL_TmpMonitorIn] ADD COLUMN [DupeCode]CHARACTER;"
CurrentProject.Connection.Execute strSQL

to

strSQL = "ALTER TABLE [TBL_TmpMonitorIn] ADD COLUMN [DupeCode]VARCHAR;"
CurrentProject.Connection.Execute strSQL

leaving the update command the same and there are no longer trailing spaces

It was caused by my use of CHARACTER to specify data type...

Off to read about CHARARACTER, VARCHAR and VARCHAR2 now!

thanks everyone