Combine 5 text fields into one

[mwconnor]

Combine text fields into one - keeping input mask

Hello,

I am new to this forum and appreciate any help.

What I have is 5 text fields(used to hold phrases), all 40 characters long - they have an input mask set up so that there are 40 " " (spaces) always in the field, or any thing you type has spaces filling the remaing field to keep the total at 40 characters.

I need to combine these 5 text fields into one field with no spaces between fields, keeping it at 200 characters (5 x 40). This data can be displayed in either this table or another table.

any suggestions?

 

[mwconnor]

To rephrase I have this as an example


SELECT (Table1!Lines) & (Table1!Lines1) AS Lines2
FROM Table1;


BUT it does not keep the 40 characters for each field as spaces.
it displays this

goodgame

when it should display
good game
^ this is 36 spaces
good= lines(then 36 "spaces")
game= lines1(then 36 "spaces")

 

[mwconnor]

Is this in the wrong area- or is this not possible?

 

[WayneRyan]

mw,

SELECT Lines1 & Space(40 - Len(Lines1)) & Lines2 & Space(40 - Len(Lines2)) ... As OneLine

Wayne

 

[mwconnor]

Wayne

Thank you for your help this is what I have

SELECT
Table1![Line1] & Space(40 - Len(Table1![Lines1])) &
Table1![Line2] & Space(40 - Len(Table1![Lines2]))
AS table1![combine];

The other lines aren't there yet. I get the error
"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

 

[filo65]

Do you Line or Lines in your Table?

filo65

 

[mwconnor]

Line

SELECT
Table1![Line1] & Space(40 - Len(Table1![Line1])) &
Table1![Line2] & Space(40 - Len(Table1![Line2]))
AS table1![combine];

Typed that out wrong, but error is still present with the above.

 

[mwconnor]

Looking at this
http://exchange.manifold.net/manifold/manuals/6_userman/mfd50Expressions.htm

It seems like Len is the only reserved word? maybe I need to use the
Char_Length(string), but that is very foreign to me.

 

[WayneRyan]

mw,

You had "As Table1![combine]". Access will not put that into the
combine field of the table for you. If that's what you need, then
we need to change it to an update query.

The following query will give you a one-column result (from the table
SomeTable):

SELECT Line1 & Space(40 - Len(Line1)) &
       Line2 & Space(40 - Len(Line2)) &       
       Line3 & Space(40 - Len(Line3)) &
       Line4 & Space(40 - Len(Line4)) &
       Line5 & Space(40 - Len(Line5)) As SomeName
From   SomeTable;

Let me know if you need the syntax for the update query.

Wayne

 

[mwconnor]

Wayne you saved my day. Thank you so much for your help.