Help!!! combining fields

[Guest]

I have 2 fields... FirstName and LastName. I'm trying to get both into one
field.

Thanks

 

[fredg]

I have 2 fields... FirstName and LastName. I'm trying to get both into one
field.

Thanks

Into one field?
In your Table?
No, you really shouldn't do that.

Whenever you need to combined first and last names, concatenate them,
in a query:
FullName:[FirstName] & " " & [LastName]

or directly in a form or on a report, using an unbound text control:
=[FirstName] & " " & [LastName]

but the combined name should not be stored in any table.

 

[Guest]

I've done that and I get an error


Control can't be edited, It's bound to the expression [FirstName] & " " &
[LastName]

I have 2 fields... FirstName and LastName. I'm trying to get both into one
field.

Thanks

Into one field?
In your Table?
No, you really shouldn't do that.

Whenever you need to combined first and last names, concatenate them,
in a query:
FullName:[FirstName] & " " & [LastName]

or directly in a form or on a report, using an unbound text control:
=[FirstName] & " " & [LastName]

but the combined name should not be stored in any table.

 

[Rick Brandt]

I've done that and I get an error


Control can't be edited, It's bound to the expression [FirstName] & " " &
[LastName]

Yep, controls with expressions (any expression) in their control source are not
editable.

 

[Guest]

I tried to chose the name from the dropdown list and that's the error I got

I've done that and I get an error


Control can't be edited, It's bound to the expression [FirstName] & " " &
[LastName]

Yep, controls with expressions (any expression) in their control source are not
editable.

 

[strive4peace]

Hi Eddie,

you should be storing the ID for the record and not the text
in a related table.

here is an example with the properties you need to set for a
combobox

combobox control

Name --> IDfieldname
ControlSource --> IDfieldname
RowSource -->
SELECT IDfieldname,
[FirstName] & " " & [LastName] AS Person
FROM Tablename
ORDER BY FirstName, LastName

BoundColumn --> 1
ColumnCount --> 2

columnWidths --> 0;2
(etc for however many columns you have
-- the ID column will be hidden since its width is zero)

ListWidth --> 2
(should add up to the sum of the column widths)

IDfieldname will be stored in the form RecordSource while
showing you information from another table...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I tried to chose the name from the dropdown list and that's the error I got

:

I've done that and I get an error


Control can't be edited, It's bound to the expression [FirstName] & " " &
[LastName]

Yep, controls with expressions (any expression) in their control source are not
editable.

 

[Guest]

I'm sorry but I'm new at this. I'm trying to figure this out.

I've created the combo box with the wizard and I've tried to change the
values in the example you gave me but I can't seem to get it to work

Name: name of box (Client)

Control Source: (Client) from drop down list

RowSource type: (table/query)

RowSource: SELECT Contacts.ContactID, Contact.FirstName, Contact.LastName
FROM Contacts ORDER BY [LastName], [FirstName];

BoundColumnCount: 1

Column Count2


When I go to the drop down menu I get "First Name" and Edward (client name)

Hi Eddie,

you should be storing the ID for the record and not the text
in a related table.

here is an example with the properties you need to set for a
combobox

combobox control

Name --> IDfieldname
ControlSource --> IDfieldname
RowSource -->
SELECT IDfieldname,
[FirstName] & " " & [LastName] AS Person
FROM Tablename
ORDER BY FirstName, LastName

BoundColumn --> 1
ColumnCount --> 2

columnWidths --> 0;2
(etc for however many columns you have
-- the ID column will be hidden since its width is zero)

ListWidth --> 2
(should add up to the sum of the column widths)

IDfieldname will be stored in the form RecordSource while
showing you information from another table...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I tried to chose the name from the dropdown list and that's the error I got

:

I've done that and I get an error


Control can't be edited, It's bound to the expression [FirstName] & " " &
[LastName]

Yep, controls with expressions (any expression) in their control source are not
editable.

 

[strive4peace]

Hi Eddie,

try this

SELECT ContactID, LastName & ', ' & FirstName as Client
FROM Contacts ORDER BY [LastName], [FirstName];

If you are storing ContactID, your field should be named the
same -- ContactID. If you want to qualify it is a client
contact instead of some other kind of contact, you may want
to name you field like this: ContactID_Client

I also like to name the control the same as the
ControlSource (fieldname) for bound controls

don't use the wizards -- they are too confusing

Look at the table design where you are storing the related
ContactID -- make sure the display is a TEXTBOX and NOT a
combo or a listbox

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm

also, in case you may not choose a ContactID, set
DefaultValue --> null (instead of 0, which will prevent you
from being able to enforce referential integrity if you do
not always have it filled out)

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I'm sorry but I'm new at this. I'm trying to figure this out.

I've created the combo box with the wizard and I've tried to change the
values in the example you gave me but I can't seem to get it to work

Name: name of box (Client)

Control Source: (Client) from drop down list

RowSource type: (table/query)

RowSource: SELECT Contacts.ContactID, Contact.FirstName, Contact.LastName
FROM Contacts ORDER BY [LastName], [FirstName];

BoundColumnCount: 1

Column Count2


When I go to the drop down menu I get "First Name" and Edward (client name)




:

Hi Eddie,

you should be storing the ID for the record and not the text
in a related table.

here is an example with the properties you need to set for a
combobox

combobox control

Name --> IDfieldname
ControlSource --> IDfieldname
RowSource -->
SELECT IDfieldname,
[FirstName] & " " & [LastName] AS Person
FROM Tablename
ORDER BY FirstName, LastName

BoundColumn --> 1
ColumnCount --> 2

columnWidths --> 0;2
(etc for however many columns you have
-- the ID column will be hidden since its width is zero)

ListWidth --> 2
(should add up to the sum of the column widths)

IDfieldname will be stored in the form RecordSource while
showing you information from another table...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I tried to chose the name from the dropdown list and that's the error I got

:





I've done that and I get an error


Control can't be edited, It's bound to the expression [FirstName] & " " &
[LastName]

Yep, controls with expressions (any expression) in their control source are not
editable.

 

[Guest]

THANK YOU SOOOOO VERY MUCH

It worked. That's a load off

Hi Eddie,

try this

SELECT ContactID, LastName & ', ' & FirstName as Client
FROM Contacts ORDER BY [LastName], [FirstName];

If you are storing ContactID, your field should be named the
same -- ContactID. If you want to qualify it is a client
contact instead of some other kind of contact, you may want
to name you field like this: ContactID_Client

I also like to name the control the same as the
ControlSource (fieldname) for bound controls

don't use the wizards -- they are too confusing

Look at the table design where you are storing the related
ContactID -- make sure the display is a TEXTBOX and NOT a
combo or a listbox

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm

also, in case you may not choose a ContactID, set
DefaultValue --> null (instead of 0, which will prevent you
from being able to enforce referential integrity if you do
not always have it filled out)

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I'm sorry but I'm new at this. I'm trying to figure this out.

I've created the combo box with the wizard and I've tried to change the
values in the example you gave me but I can't seem to get it to work

Name: name of box (Client)

Control Source: (Client) from drop down list

RowSource type: (table/query)

RowSource: SELECT Contacts.ContactID, Contact.FirstName, Contact.LastName
FROM Contacts ORDER BY [LastName], [FirstName];

BoundColumnCount: 1

Column Count2


When I go to the drop down menu I get "First Name" and Edward (client name)




:

Hi Eddie,

you should be storing the ID for the record and not the text
in a related table.

here is an example with the properties you need to set for a
combobox

combobox control

Name --> IDfieldname
ControlSource --> IDfieldname
RowSource -->
SELECT IDfieldname,
[FirstName] & " " & [LastName] AS Person
FROM Tablename
ORDER BY FirstName, LastName

BoundColumn --> 1
ColumnCount --> 2

columnWidths --> 0;2
(etc for however many columns you have
-- the ID column will be hidden since its width is zero)

ListWidth --> 2
(should add up to the sum of the column widths)

IDfieldname will be stored in the form RecordSource while
showing you information from another table...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Eddie wrote:

I tried to chose the name from the dropdown list and that's the error I got

:





I've done that and I get an error


Control can't be edited, It's bound to the expression [FirstName] & " " &
[LastName]

Yep, controls with expressions (any expression) in their control source are not
editable.

 

[strive4peace]

you're welcome, Eddie happy to help

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

THANK YOU SOOOOO VERY MUCH

It worked. That's a load off



:

Hi Eddie,

try this

SELECT ContactID, LastName & ', ' & FirstName as Client
FROM Contacts ORDER BY [LastName], [FirstName];

If you are storing ContactID, your field should be named the
same -- ContactID. If you want to qualify it is a client
contact instead of some other kind of contact, you may want
to name you field like this: ContactID_Client

I also like to name the control the same as the
ControlSource (fieldname) for bound controls

don't use the wizards -- they are too confusing

Look at the table design where you are storing the related
ContactID -- make sure the display is a TEXTBOX and NOT a
combo or a listbox

Don't use lookup fields in table design
http://www.mvps.org/access/lookupfields.htm

also, in case you may not choose a ContactID, set
DefaultValue --> null (instead of 0, which will prevent you
from being able to enforce referential integrity if you do
not always have it filled out)

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I'm sorry but I'm new at this. I'm trying to figure this out.

I've created the combo box with the wizard and I've tried to change the
values in the example you gave me but I can't seem to get it to work

Name: name of box (Client)

Control Source: (Client) from drop down list

RowSource type: (table/query)

RowSource: SELECT Contacts.ContactID, Contact.FirstName, Contact.LastName
FROM Contacts ORDER BY [LastName], [FirstName];

BoundColumnCount: 1

Column Count2


When I go to the drop down menu I get "First Name" and Edward (client name)




:



Hi Eddie,

you should be storing the ID for the record and not the text
in a related table.

here is an example with the properties you need to set for a
combobox

combobox control

Name --> IDfieldname
ControlSource --> IDfieldname
RowSource -->
SELECT IDfieldname,
[FirstName] & " " & [LastName] AS Person

FROM Tablename

ORDER BY FirstName, LastName

BoundColumn --> 1
ColumnCount --> 2

columnWidths --> 0;2
(etc for however many columns you have
-- the ID column will be hidden since its width is zero)

ListWidth --> 2
(should add up to the sum of the column widths)

IDfieldname will be stored in the form RecordSource while
showing you information from another table...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Eddie wrote:


I tried to chose the name from the dropdown list and that's the error I got

:







I've done that and I get an error


Control can't be edited, It's bound to the expression [FirstName] & " " &
[LastName]

Yep, controls with expressions (any expression) in their control source are not
editable.