Concatenate two fields into one in same table

[Jose Ruben Gonzalez-Baird]

Hello,

I am rather new to building expressions in Access. I have a pretty simple
task, really. I want to concatenate values in 'Reach' and 'Station' fields
into one field called 'Reach_Station'

For instance, if reach = 10 and station = 300, then reach_station = 10-300.

I built a query using the following syntax:

Reach_Station: [Reach]&"-"&[Station]

the query itself returned the correct results after I ran it, but I am not
able to get the Reach_Station field to populate in my table when I try to use
the query as a lookup value. any clues? Thank you, Ruben

[mscertified]

I dont know how to do it via the query designer (I never use it), but if you
go into the SQL designer you need to change the query to say:
[Reach] &"-" & [Station] As Reach_Station

-Dorian

Post by Jose Ruben Gonzalez-Baird
Hello,
I am rather new to building expressions in Access. I have a pretty simple
task, really. I want to concatenate values in 'Reach' and 'Station' fields
into one field called 'Reach_Station'
For instance, if reach = 10 and station = 300, then reach_station = 10-300.
Reach_Station: [Reach]&"-"&[Station]
the query itself returned the correct results after I ran it, but I am not
able to get the Reach_Station field to populate in my table when I try to use
the query as a lookup value. any clues? Thank you, Ruben

[Jose Ruben Gonzalez-Baird]

Hi, thanks so much for the response. The SQL language you gave checks out
just fine in the when I run the query. However, when I try to specify this
query as the lookup value for the field within the table design view, there
are no results. In other words, the query designer approach that I was using
and the SQL design that you suggested check out once the query is run. But
for some reason, when I point to this query as the lookup value of the
Reach_Station field in the table, there are no results. If you have any
suggestions for troublshooting the lookup value function in the table design
that sure would be great. Thanks once again. Ruben

Post by mscertified
I dont know how to do it via the query designer (I never use it), but if you
[Reach] &"-" & [Station] As Reach_Station
-Dorian

Post by Jose Ruben Gonzalez-Baird
Hello,
I am rather new to building expressions in Access. I have a pretty simple
task, really. I want to concatenate values in 'Reach' and 'Station' fields
into one field called 'Reach_Station'
For instance, if reach = 10 and station = 300, then reach_station = 10-300.
Reach_Station: [Reach]&"-"&[Station]
the query itself returned the correct results after I ran it, but I am not
able to get the Reach_Station field to populate in my table when I try to use
the query as a lookup value. any clues? Thank you, Ruben

[Jeff Boyce]

It sounds like you might be trying to design a table with a "lookup" data
type. Unless you (and any subsequent user/developer) is very clear on this,
you (and future user/developers) may run into problems (see discussions in
the tablesdbdesign newsgroup).

The primary reason NOT to use the lookup data type is that what is actually
stored in the table doesn't match what is displayed -- this leads to
confusion.

If you want to use "lookup", do so in a form, using a combo box or list box,
rather than in a table. In Access, use tables to store data, and forms to
display/add/edit/... data.
--
Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

"Jose Ruben Gonzalez-Baird"

Post by Jose Ruben Gonzalez-Baird
Hi, thanks so much for the response. The SQL language you gave checks out
just fine in the when I run the query. However, when I try to specify this
query as the lookup value for the field within the table design view, there
are no results. In other words, the query designer approach that I was using
and the SQL design that you suggested check out once the query is run. But
for some reason, when I point to this query as the lookup value of the
Reach_Station field in the table, there are no results. If you have any
suggestions for troublshooting the lookup value function in the table design
that sure would be great. Thanks once again. Ruben

Post by mscertified
I dont know how to do it via the query designer (I never use it), but if you
[Reach] &"-" & [Station] As Reach_Station
-Dorian

Post by Jose Ruben Gonzalez-Baird
Hello,
I am rather new to building expressions in Access. I have a pretty simple
task, really. I want to concatenate values in 'Reach' and 'Station' fields
into one field called 'Reach_Station'
For instance, if reach = 10 and station = 300, then reach_station = 10-300.
Reach_Station: [Reach]&"-"&[Station]
the query itself returned the correct results after I ran it, but I am not
able to get the Reach_Station field to populate in my table when I try to use
the query as a lookup value. any clues? Thank you, Ruben

[J. Goddard]

Hi -

Did you try using an update query? All you need in it is the
Reach_Station field from your table, with your expression in the "Update
To" field of the query. It should work fine.

But - why do you want to do this? If you intend to keep the two fields
Reach and Station in your table, then Reach_Station becomes redundant,
and you will have to run the update query every time you add or change
records.

If all you need reach_station for is to display it in forms or reports,
you can make it a calculated field in the form/report, leaving it out of
the table altogether.

Hope this helps

John

Post by Jose Ruben Gonzalez-Baird
Hello,
I am rather new to building expressions in Access. I have a pretty simple
task, really. I want to concatenate values in 'Reach' and 'Station' fields
into one field called 'Reach_Station'
For instance, if reach = 10 and station = 300, then reach_station = 10-300.
Reach_Station: [Reach]&"-"&[Station]
the query itself returned the correct results after I ran it, but I am not
able to get the Reach_Station field to populate in my table when I try to use
the query as a lookup value. any clues? Thank you, Ruben

[Allen Browne]

Jose, the solution depends on your answer to this question:

Are there valid cases where the Reach_Station should NOT contain a value
that is the same as:
[Reach]&"-"&[Station]

NO: They should always be the same.
============================
In this case, you *must* not store the value in the table. You can use the
calculated query field anywhere you can use the table. You never have to
worry about keeping the table field up to date. You're home free, with no
maintenance issues to worry about. In any form or report, you can also set
the Control Source of a text box to:
=[Reach]&"-"&[Station]

YES: There are cases where the user should be able to change it.
==================================================
You can't do this at the table level, but you can in a form. Use the
AfterUpdate event procedure of both Reach and Station to assign the value to
the new field, and the user can then change if as needed.

More info:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

In summary, one of the most basic rules of data normalization is that you
never store dependent data. The question above simply determines whether
this is dependent data or not.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jose Ruben Gonzalez-Baird" <Jose Ruben

Post by Jose Ruben Gonzalez-Baird
Hello,
I am rather new to building expressions in Access. I have a pretty simple
task, really. I want to concatenate values in 'Reach' and 'Station' fields
into one field called 'Reach_Station'
For instance, if reach = 10 and station = 300, then reach_station = 10-300.
Reach_Station: [Reach]&"-"&[Station]
the query itself returned the correct results after I ran it, but I am not
able to get the Reach_Station field to populate in my table when I try to use
the query as a lookup value. any clues? Thank you, Ruben