CODEWITHSUNDEEP
ms-accessvb6dao
finch
finch

Reputation: 549

Preserve trailing spaces in Access DAO SQL INSERT

Am using DAO to insert data into an Access database. When I concatenate fields, trailing spaces are removed. How can I insert data RETAINING trailing spaces?

For example, the following code strips trailing spaces of fields ref and name when creating column "mytext":

INSERT INTO mytest (ref, mytext) SELECT ref, ref + name + '/' FROM mytable

mytable is a FoxPro 2.5 table which I have linked. I am assuming the columns in the FoxPro table contain trailing spaces, as my data in FoxPro does not take up the full width of the fields AND in FoxPro when I run a REPLACE including the concatenate operator, trailing spaces DO appear:

REPLACE ALL mytext WITH ref + name

Columns "ref" and "name" are character fields of length 10. If ref = "S1" and name = "fred" - the command run in FoxPro sets mytext to "S1" then 8 spaces then "fred" then 4 assumed spaces - the DAO command run in VB6 sets mytext to "S1fred"

Upvotes: 2

Views: 773

Answers (1)

rene
rene

Reputation: 42443

assuming [name] is 10...

Maybe adding spaces explictely will work

INSERT INTO mytest (ref, mytext) 
SELECT ref + space(10-len(ref)), 
       ref + [name] + space(20 - (len(ref)+len([name]) ) )+'/' 
FROM mytable

You have to maintain this query if the columnsizes of ref and/or name changes. To be on the safeside use left(ref + space(10), 10). At least it won't break if your columnsizes chnages

Upvotes: 1

Related Questions