Appending Fields

R

Rudi

Hi,

I have 2 fields in my table, name and surname. How do I append these 2
fields and store the result in a third field?

Thanks!
 
F

Fred Boer

Dear Rudi:

The easiest way is not to do that! ;) If you do that you will be storing
redundant information unnecessarily. Whenever you might need a "fullname",
simply concatenate the two together. For example: name&" "&surname, would
give Fred Boer; surname&", "&name would give Boer, Fred.

By the way, you shouldn't use "name" as the name of a field. It is a
"reserved" word in Access and this will cause problems. You could use
something like "firstname" "midname" "surname", etc..

HTH
Fred Boer
 
R

Rick B

You don't.

If you need to combine them, do so in your queries, reports, or forms.

You don't store calculated values like that in a table. What if someone
gets married or divorced? You want them to have to fix two fields?

Please do a search and read the hundreds of previous answers to this exact
question if you need more supporting information.
 
R

Rudi

Thank you Fred!

I did the following on my form on a drop-down list Row source:

SELECT [Employee Details].NameID, [Employee Details].Names & " " & [Employee
Details].Surname FROM [Employee Details];

I now refer to this drop-down list from within a query with:

[Forms]![Timesheet Report Selection]![Names]

Names is the name of the drop-down list object. What actually should happen,
is that I must choose from the drop-down list a name (which is concatenated
with the surname) and then use this value in a query to determine all the
records that has this name in a required field. The problem now is, that that
query returns no values. If I use just a text box on the form and I type the
name in manually, the query finds all the records. Any ideas what the problem
might be? I can't see how there are any spaces added to the name/surname
which might have been the problem. Maybe I could add an asterix somewhere to
eliminate the possibiliy of spaces?
 
F

Fred Boer

Hi Rudi:

Well, try the following as the rowsource for the combobox:

SELECT [Employee Details].NameID, [Employee Details].Names & " " & [Employee
Details].Surname AS Expr1
FROM [Employee Details];

Now, you have to decide what you want the combobox to return... i.e. what do
you want Access to "see" when it looks for a value in the combobox. If you
want it to use the NameID, then the "Bound Column" has to be 1, if you want
it to use the concatenated name the "Bound Column" has to be 2. The query
would then look for the following: [Forms]![Timesheet Report
Selection]![EXP1]. Now, if you want the combobox to *display* the full name,
but *return* only the first name (i.e. Names), you need to set it up
differently. In that case your SQL statement could be:

SELECT [Employee Details].Names, [Employee Details].Names & " " & [Employee
Details].Surname AS Expr1
FROM [Employee Details];

In this situation, the bound column would be 1. The column count would be 2.
The column widths could be 0,1.

Now when the query runs, it needs to use the value in the combobox as a
parameter, right? So, if you want to use the first SQL statement, you need
to ensure that the query can use the "new" style name that you've created
for the combobox, i.e. "Fred Boer".

By the way, I see you have named the combobox "Names" which is also the name
of a field in the table. I suggest that you do some research into the use of
a naming convention in your application - it will save you many headaches.
Using a naming convention, you might call the combobox "cboNames", clearly
differentiating it from [Employee Details].Names...

The following link might be useful:

http://www.mvps.org/access/tencommandments.htm

HTH
Fred



Rudi said:
Thank you Fred!

I did the following on my form on a drop-down list Row source:

SELECT [Employee Details].NameID, [Employee Details].Names & " " &
[Employee
Details].Surname FROM [Employee Details];

I now refer to this drop-down list from within a query with:

[Forms]![Timesheet Report Selection]![Names]

Names is the name of the drop-down list object. What actually should
happen,
is that I must choose from the drop-down list a name (which is
concatenated
with the surname) and then use this value in a query to determine all the
records that has this name in a required field. The problem now is, that
that
query returns no values. If I use just a text box on the form and I type
the
name in manually, the query finds all the records. Any ideas what the
problem
might be? I can't see how there are any spaces added to the name/surname
which might have been the problem. Maybe I could add an asterix somewhere
to
eliminate the possibiliy of spaces?


Fred Boer said:
Dear Rudi:

The easiest way is not to do that! ;) If you do that you will be storing
redundant information unnecessarily. Whenever you might need a
"fullname",
simply concatenate the two together. For example: name&" "&surname,
would
give Fred Boer; surname&", "&name would give Boer, Fred.

By the way, you shouldn't use "name" as the name of a field. It is a
"reserved" word in Access and this will cause problems. You could use
something like "firstname" "midname" "surname", etc..

HTH
Fred Boer
Click to expand...
Click to expand...
 
R

Rudi

Thanks!

I did the following and it works:

SELECT [Employee Details].Names & " " & [Employee Details].Surname,
[Employee Details].Names & " " & [Employee Details].Surname FROM [Employee
Details];

I want the user, as well as Access to the see the concatenated name. I now
want to do almost the same with 2 date selector drop-down lists. One list if
for years, the other is for months. I basically want to create a drop-down
list that shows all (or most) of the years to come from now on and another
drop-down list to show all the 12 months. I then want to access this
information again from my query, but into a single date. Thus I want all the
entries of table that has that spesific year and month in a date. The day
doesn't matter. The dates in my database is in the following format
yyyy/mm/dd. I want the drop-down list for the year to send the year value
into the yyyy part and the month drop-down value into the mm part (which must
be sent as a corresponding number not the name of the month). The dd part can
be filled with an asterix, because the days doen't matter. How do I do this
in the criteria part of the query and how do I set up the row sources in the
drop-down lists?


Fred Boer said:
Hi Rudi:

Well, try the following as the rowsource for the combobox:

SELECT [Employee Details].NameID, [Employee Details].Names & " " & [Employee
Details].Surname AS Expr1
FROM [Employee Details];

Now, you have to decide what you want the combobox to return... i.e. what do
you want Access to "see" when it looks for a value in the combobox. If you
want it to use the NameID, then the "Bound Column" has to be 1, if you want
it to use the concatenated name the "Bound Column" has to be 2. The query
would then look for the following: [Forms]![Timesheet Report
Selection]![EXP1]. Now, if you want the combobox to *display* the full name,
but *return* only the first name (i.e. Names), you need to set it up
differently. In that case your SQL statement could be:

SELECT [Employee Details].Names, [Employee Details].Names & " " & [Employee
Details].Surname AS Expr1
FROM [Employee Details];

In this situation, the bound column would be 1. The column count would be 2.
The column widths could be 0,1.

Now when the query runs, it needs to use the value in the combobox as a
parameter, right? So, if you want to use the first SQL statement, you need
to ensure that the query can use the "new" style name that you've created
for the combobox, i.e. "Fred Boer".

By the way, I see you have named the combobox "Names" which is also the name
of a field in the table. I suggest that you do some research into the use of
a naming convention in your application - it will save you many headaches.
Using a naming convention, you might call the combobox "cboNames", clearly
differentiating it from [Employee Details].Names...

The following link might be useful:

http://www.mvps.org/access/tencommandments.htm

HTH
Fred



Rudi said:
Thank you Fred!

I did the following on my form on a drop-down list Row source:

SELECT [Employee Details].NameID, [Employee Details].Names & " " &
[Employee
Details].Surname FROM [Employee Details];

I now refer to this drop-down list from within a query with:

[Forms]![Timesheet Report Selection]![Names]

Names is the name of the drop-down list object. What actually should
happen,
is that I must choose from the drop-down list a name (which is
concatenated
with the surname) and then use this value in a query to determine all the
records that has this name in a required field. The problem now is, that
that
query returns no values. If I use just a text box on the form and I type
the
name in manually, the query finds all the records. Any ideas what the
problem
might be? I can't see how there are any spaces added to the name/surname
which might have been the problem. Maybe I could add an asterix somewhere
to
eliminate the possibiliy of spaces?


Fred Boer said:
Dear Rudi:

The easiest way is not to do that! ;) If you do that you will be storing
redundant information unnecessarily. Whenever you might need a
"fullname",
simply concatenate the two together. For example: name&" "&surname,
would
give Fred Boer; surname&", "&name would give Boer, Fred.

By the way, you shouldn't use "name" as the name of a field. It is a
"reserved" word in Access and this will cause problems. You could use
something like "firstname" "midname" "surname", etc..

HTH
Fred Boer

Hi,

I have 2 fields in my table, name and surname. How do I append these 2
fields and store the result in a third field?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
F

Fred Boer

Hi Rudi:

I don't think your combobox rowsource is correctly set up. Your SQL
statement sets up a query with two columns, each of which contains the
concatenated name. So, it isn't quite right yet...

To see what I mean, try the following:

Click on the three dots to the right of the RowSource property. This will
show you the RowSource as a query. Click on View>Datasheet, and you will see
that you have a query with two columns, both with the same data.

So, let's try again, ok? :)

You want the combobox to display the concatenated name, and you want it to
return the concatenated name for use as a parameter in a query.

Try this:

RowSource: The following SQL will create a rowsource with one column,
containing the concatenated name. Instead of letting Access call it "EXP1"
or some such name, I've given it the name "Fullname"

SELECT [Employee Details].[Names] & " " & [Employee Details].[Surname] AS
Fullname
FROM [Employee Details];

Now, set the following for the combobox:

Bound Column:1
Column Count:1
Column Width: 1"

This will provide a combobox that displays the concatenated name and return
the concatenated name for Access to use as a parameter in your query.


As far as picking the year and month, you might be better off implementing a
calendar control, since you are trying to deal with date/time data. If you
have two comboboxes with the year (as a number or string) and a month (as a
number), you would have to take these values and convert them into a
date/time values. It might be easier to use a calendar control, but I
haven't had any experience with them...

HTH
Fred Boer



Rudi said:
Thanks!

I did the following and it works:

SELECT [Employee Details].Names & " " & [Employee Details].Surname,
[Employee Details].Names & " " & [Employee Details].Surname FROM [Employee
Details];

I want the user, as well as Access to the see the concatenated name. I now
want to do almost the same with 2 date selector drop-down lists. One list
if
for years, the other is for months. I basically want to create a drop-down
list that shows all (or most) of the years to come from now on and another
drop-down list to show all the 12 months. I then want to access this
information again from my query, but into a single date. Thus I want all
the
entries of table that has that spesific year and month in a date. The day
doesn't matter. The dates in my database is in the following format
yyyy/mm/dd. I want the drop-down list for the year to send the year value
into the yyyy part and the month drop-down value into the mm part (which
must
be sent as a corresponding number not the name of the month). The dd part
can
be filled with an asterix, because the days doen't matter. How do I do
this
in the criteria part of the query and how do I set up the row sources in
the
drop-down lists?


Fred Boer said:
Hi Rudi:

Well, try the following as the rowsource for the combobox:

SELECT [Employee Details].NameID, [Employee Details].Names & " " &
[Employee
Details].Surname AS Expr1
FROM [Employee Details];

Now, you have to decide what you want the combobox to return... i.e. what
do
you want Access to "see" when it looks for a value in the combobox. If
you
want it to use the NameID, then the "Bound Column" has to be 1, if you
want
it to use the concatenated name the "Bound Column" has to be 2. The query
would then look for the following: [Forms]![Timesheet Report
Selection]![EXP1]. Now, if you want the combobox to *display* the full
name,
but *return* only the first name (i.e. Names), you need to set it up
differently. In that case your SQL statement could be:

SELECT [Employee Details].Names, [Employee Details].Names & " " &
[Employee
Details].Surname AS Expr1
FROM [Employee Details];

In this situation, the bound column would be 1. The column count would be
2.
The column widths could be 0,1.

Now when the query runs, it needs to use the value in the combobox as a
parameter, right? So, if you want to use the first SQL statement, you
need
to ensure that the query can use the "new" style name that you've
created
for the combobox, i.e. "Fred Boer".

By the way, I see you have named the combobox "Names" which is also the
name
of a field in the table. I suggest that you do some research into the use
of
a naming convention in your application - it will save you many
headaches.
Using a naming convention, you might call the combobox "cboNames",
clearly
differentiating it from [Employee Details].Names...

The following link might be useful:

http://www.mvps.org/access/tencommandments.htm

HTH
Fred



Rudi said:
Thank you Fred!

I did the following on my form on a drop-down list Row source:

SELECT [Employee Details].NameID, [Employee Details].Names & " " &
[Employee
Details].Surname FROM [Employee Details];

I now refer to this drop-down list from within a query with:

[Forms]![Timesheet Report Selection]![Names]

Names is the name of the drop-down list object. What actually should
happen,
is that I must choose from the drop-down list a name (which is
concatenated
with the surname) and then use this value in a query to determine all
the
records that has this name in a required field. The problem now is,
that
that
query returns no values. If I use just a text box on the form and I
type
the
name in manually, the query finds all the records. Any ideas what the
problem
might be? I can't see how there are any spaces added to the
name/surname
which might have been the problem. Maybe I could add an asterix
somewhere
to
eliminate the possibiliy of spaces?


:

Dear Rudi:

The easiest way is not to do that! ;) If you do that you will be
storing
redundant information unnecessarily. Whenever you might need a
"fullname",
simply concatenate the two together. For example: name&" "&surname,
would
give Fred Boer; surname&", "&name would give Boer, Fred.

By the way, you shouldn't use "name" as the name of a field. It is a
"reserved" word in Access and this will cause problems. You could use
something like "firstname" "midname" "surname", etc..

HTH
Fred Boer

Hi,

I have 2 fields in my table, name and surname. How do I append these
2
fields and store the result in a third field?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Top