Merge two field in a table

[jlmystify]

All,
I have a table with about 50,000 records. In the table I have a First Name and Middle Name field. I have created a third field called FULL NAMES.
PROBLEM: How do i combine the data from First Name and Middle Name fields into the FULL Names field in the same table.

Thanks for your help!

 

[pbaldy]

I wouldn't save it, but you can display it in forms/queries/reports with

FirstName & " " & LastName

 

[jlmystify]

Merge two fields in a table

PBALDY,
Thanks for your response. I am looking to eliminate the Frist Name and Last Name fields in the table and replace it with the full name. Is that possible? As i mentioned earlier i already have 50,000 records entered so i do no want to re-type them again.

Thanks

 

[pbaldy]

You could use an update query to populate the new field, then delete the other two fields, but I would leave them separate. It's easier to put them together than break them apart when you just want one or the other.

 

[jlmystify]

Combine two fields in a table

Pbaldy,
Thanks for your response, but i can not get it to work. i try combining the First Name and Middle Name fields into Full Names but i am getting a value of -1 in the Full Names field. I must be using the wrong syntax in the criteria. Do you have any suggestions?

Thanks for all your help.

 

[jlmystify]

PBaldy,
I got it. i was using the wrong sintax. I took your advise i left the field, but i am using the new one for reporting.

Thanks for all your help!

 

[neileg]

PBaldy,
I got it. i was using the wrong sintax. I took your advise i left the field, but i am using the new one for reporting.

Thanks for all your help!

You don't need this field at all. You've now got a potential problem if the data changes because of the redundancy. You should do the combination in a select query and base your report on this.

 

[joepele]

I wouldn't save it, but you can display it in forms/queries/reports with

FirstName & " " & LastName

This does not seem to work very well with numbers.

e.g.
If the format of one of the field was
0001, 0002, 0003
and the other was
ABC, DEF, GHI

In the report, it will only reflect as 1 ABC, 2 DEF, 3 GHI.

the prefix of 000 will be missing.

Anyway around it?

 

[neileg]

Wrap the number in a format function: Format(MyNumberField,"0000")

 

[joepele]

Wrap the number in a format function: Format(MyNumberField,"0000")

Before joining or after?

When this 1 field is alone, it's fine. But when it's joined as a report... all format goes bonkers.

Anyway, is it a better idea to use a query to join the 2 fields into 1?

 

[Mike375]

I don't have that problem with something like 00000456 in a text field when using [field] & " " & [field2]

I have not tried it in a report but I can't see why the report would change the data in the calculated field.

I just tried using & " " & to join text field, number and date fields and no problem. I had 00000456 in the text field and that was how it appeared in the join

 

[joepele]

I don't have that problem with something like 00000456 in a text field when using [field] & " " & [field2]

I have not tried it in a report but I can't see why the report would change the data in the calculated field.

I just tried using & " " & to join text field, number and date fields and no problem. I had 00000456 in the text field and that was how it appeared in the join

I guess it'll be ok if it's in a text format. WYSIWYG.

But this field is an Autonumber format. WYSINWYG


Can the format be set for the report for 2 joined fields?
e.g.
Format : Field1(0000) & " " & Field2(MM/DD/YYYY)

 

[Mike375]

I am not sure what you mean. I only have one Report in my data base and have only ever made a couple of simple ones.

But my understanding is that a query would supply the data....and some of that data could be joined fields.....and the Report (as compared to a form) would allow for the appropiate display of the date..grouping, turning a long colimn insto several small fields.

I am not familiar with autonumber being like WYSINWYG

I don't think it is a good idea to use a autonumber ID field for anything more than linking data in two or more tables.

If the format of one of the field was
0001, 0002, 0003
and the other was
ABC, DEF, GHI

In the report, it will only reflect as 1 ABC, 2 DEF, 3 GHI.

the prefix of 000 will be missing.

Perhaps doing in it the query is the answer and the new field is displayed in the query.

 

[pbaldy]

Can the format be set for the report for 2 joined fields?
e.g.
Format : Field1(0000) & " " & Field2(MM/DD/YYYY)

You would format each component separately, as appropriate to each:

Format(Field1, "0000") & " " & Format(Field2, "mmddyyyy")

 

[joepele]

I am not sure what you mean. I only have one Report in my data base and have only ever made a couple of simple ones.

But my understanding is that a query would supply the data....and some of that data could be joined fields.....and the Report (as compared to a form) would allow for the appropiate display of the date..grouping, turning a long colimn insto several small fields.

I am not familiar with autonumber being like WYSINWYG

I don't think it is a good idea to use a autonumber ID field for anything more than linking data in two or more tables.

If the format of one of the field was
0001, 0002, 0003
and the other was
ABC, DEF, GHI

In the report, it will only reflect as 1 ABC, 2 DEF, 3 GHI.

the prefix of 000 will be missing.

Perhaps doing in it the query is the answer and the new field is displayed in the query.

Hope this gives a better pict.

Pls see the report. Text13 to see what I mean.

 

[joepele]

You would format each component separately, as appropriate to each:

Format(Field1, "0000") & " " & Format(Field2, "mmddyyyy")

Thanx!

Will try it.

 

[Mike375]

That is quite tricky

If you add a text field to the table and copy/paste SerialID that drops the 000. Also a SetValue macro drops the 00000

I guess the reason is because what is being copied or used for SetValue is not really 000010 but just the 10. Although if it is copied/pasted to Excel it keeps the 000s

I might pretend Paul has not posted and play with this myself.

I wish I had not read this thread

 

[joepele]

That is quite tricky

If you add a text field to the table and copy/paste SerialID that drops the 000. Also a SetValue macro drops the 00000

I guess the reason is because what is being copied or used for SetValue is not really 000010 but just the 10. Although if it is copied/pasted to Excel it keeps the 000s

I might pretend Paul has not posted and play with this myself.

I wish I had not read this thread

Looking forward to your posts / help.