Concatenate

[Guest]

Greetings,
Newbie to this group.

My database is way to large for Excel. My experience with Access is
limited. I'm trying to join several strings of text and numbers like found
in a street address. The database also contains other customer info like
name, phone, etc.
A B C D E F
123 Beach Blvd John Smith 555-1234

In Excel I would do somethng like,

Type in a separate cell:
=concatenate(A1," ",B1, " ",C1) to get 123 Beach Blvd.
or maybe =(A1&" "&B1&" "&C1) and achieve the same result.

I'm not sure where in Access to look to enter the formula, or even if the
same formulas above work in Access. I have all my data in a table named
JAXNONSUBS. I've looked at Queries, (design view and simple query wizard)
and am able to extract fields but without concatenation, so I'm basically
lost.

The result I am looking for is to export as text the customer's name,
concateneated address strings, phone, etc.

Can anyone give a tip or point me in the right direction?

Best,

Mike
Jacksonville, Florida

 

[RBear3]

You can do this in your queries, reports, or forms.

To do so in a query, go to a new column and enter something like the
following...

FullAddress: ([A]+" ") & (+" ") & [C]

using "+" instead of "&" will prevent a blank space from being added if an
element does not exist for a particular item.


In a form or report, add a new unbound field and put the following:

= ([A]+" ") & (+" ") & [C]


Hopefully you are using actual field names not "a" "b" etc.

 

[Arvin Meyer [MVP]]

In Access:

=[A] & " " & & " " & [C] & " " & [D] & " " & [E] & " " & [F]

where A through F are the names of fields (columns)

 

[BruceM]

Make a query based on the table. At the top of an empty column in query
design view, put something like:
FullAddress: [StreetNumber] & " " & [Street] & " " & [StreetType] & Chr(13)
& Chr(10) & [City] & ", " & [State] & " " & [Zip]
Use your actual field names, of course. Chr(13) & Chr(10) sends you to a
new line. The rest is probably familiar from Excel concatenation.
Make a report based on the query. Auto Report can get you started, if you
like. Bind a text box to [FullAddress]. For data entry and editing, bind a
form to the table or query, but understand that you need to add/edit
individual fields, not the concatenated string.
Remember, a form is for viewing, adding, and editing data. A report is for
printing. A table is where the data are stored, but you shouldn't work
directly with a table after the design and development process is complete.
A query is for ordering and otherwise manipulating the data (mathematical
operations, concatenation, etc.).

 

[Guest]

--
Mike
Jacksonville, Florida

You can do this in your queries, reports, or forms.

To do so in a query, go to a new column and enter something like the
following...

FullAddress: ([A]+" ") & (+" ") & [C]

using "+" instead of "&" will prevent a blank space from being added if an
element does not exist for a particular item.


In a form or report, add a new unbound field and put the following:

= ([A]+" ") & (+" ") & [C]


Hopefully you are using actual field names not "a" "b" etc.

--
Hope that helps!

RBear3
..

Greetings,
Newbie to this group.

My database is way to large for Excel. My experience with Access is
limited. I'm trying to join several strings of text and numbers like
found
in a street address. The database also contains other customer info like
name, phone, etc.
A B C D E F
123 Beach Blvd John Smith 555-1234

In Excel I would do somethng like,

Type in a separate cell:
=concatenate(A1," ",B1, " ",C1) to get 123 Beach Blvd.
or maybe =(A1&" "&B1&" "&C1) and achieve the same result.

I'm not sure where in Access to look to enter the formula, or even if the
same formulas above work in Access. I have all my data in a table named
JAXNONSUBS. I've looked at Queries, (design view and simple query wizard)
and am able to extract fields but without concatenation, so I'm basically
lost.

The result I am looking for is to export as text the customer's name,
concateneated address strings, phone, etc.

Can anyone give a tip or point me in the right direction?

Best,

Mike
Jacksonville, Florida

 

[Guest]

Thank you RBear3 for responding. I haven't had a chance to read the other
replies yet but I will.

I'm confused as to where to insert your sample syntax.
You wrote
"go to a new column " They all appear new to me because they are blank.
In Query, I selected New>Design View. I was able to select the add the table
to the query.

You wrote
"enter something like the
following...

FullAddress: ([A]+" ") & (+" ") & [C]

I replaced your A, B anc C with the field names in the database. So your
syntax now looks like this,

FullAddress: ([HOUSENUMBER]+" ") & ([STREETNAME]+" ") & [STREETTYPE]

Here's where I am lost...

Is "FullAddress: ([HOUSENUMBER]+" ") & ([STREETNAME]+" ") & [STREETTYPE]"
the entire syntax and do I insert it all in the Field Name?

Or is "FullAddress:" the Field Name and I should insert "([HOUSENUMBER]+" ")
& ([STREETNAME]+" ") & [STREETTYPE]" in another place like the Criteria field?

See, I don't know where to put what.

I've tried several combinations and I still fail. In some attempts I get a
window asking for parameters. I read the help on parameters and tried all 4
examples shown. No joy. I think i am getting close though.

Would be kind enough to write back and tell me where my errors are so far?

Best regards.

 

[John W. Vinson]

Is "FullAddress: ([HOUSENUMBER]+" ") & ([STREETNAME]+" ") & [STREETTYPE]"
the entire syntax and do I insert it all in the Field Name?

Yes.

Did you try it? What happened?

John W. Vinson [MVP]

 

[Guest]

John. Thank you for responding.

I copied the synatax directly off the post. Precisely this including the
quotes:
"FullAddress: ([HOUSENUMBER]+" ") & ([STREETNAME]+" ") & [STREETTYPE]"

I pasted it in the Field Name of the 1st column.

The error message I got when I ran the query was,
"The expression you entered contains invalid syntax. You have entered an
operand without an operator."

Thanks again,
Mike
Jacksonville, Florida

Is "FullAddress: ([HOUSENUMBER]+" ") & ([STREETNAME]+" ") & [STREETTYPE]"
the entire syntax and do I insert it all in the Field Name?

Yes.

Did you try it? What happened?

John W. Vinson [MVP]

 

[BruceM]

Remove the quotes from the beginning and the end:
FullAddress: ([HOUSENUMBER]+" ") & ([STREETNAME]+" ") & [STREETTYPE]

John. Thank you for responding.

I copied the synatax directly off the post. Precisely this including the
quotes:
"FullAddress: ([HOUSENUMBER]+" ") & ([STREETNAME]+" ") & [STREETTYPE]"

I pasted it in the Field Name of the 1st column.

The error message I got when I ran the query was,
"The expression you entered contains invalid syntax. You have entered an
operand without an operator."

Thanks again,
Mike
Jacksonville, Florida

Is "FullAddress: ([HOUSENUMBER]+" ") & ([STREETNAME]+" ") &
[STREETTYPE]"
the entire syntax and do I insert it all in the Field Name?

Yes.

Did you try it? What happened?

John W. Vinson [MVP]

 

[Guest]

Thank you. Thank you. Thank you,
BruceM, John W. Vinson, Arvin Mayer, RBear3.

It worked.

I'm much obliged,
--
Mike
Jacksonville, Florida

Remove the quotes from the beginning and the end:
FullAddress: ([HOUSENUMBER]+" ") & ([STREETNAME]+" ") & [STREETTYPE]

John. Thank you for responding.

I copied the synatax directly off the post. Precisely this including the
quotes:
"FullAddress: ([HOUSENUMBER]+" ") & ([STREETNAME]+" ") & [STREETTYPE]"

I pasted it in the Field Name of the 1st column.

The error message I got when I ran the query was,
"The expression you entered contains invalid syntax. You have entered an
operand without an operator."

Thanks again,
Mike
Jacksonville, Florida

On Tue, 8 May 2007 18:11:02 -0700, Mike Saffer

Is "FullAddress: ([HOUSENUMBER]+" ") & ([STREETNAME]+" ") &
[STREETTYPE]"
the entire syntax and do I insert it all in the Field Name?

Yes.

Did you try it? What happened?

John W. Vinson [MVP]