combine data into one text box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using access 2002 and would like to combine 2 cells of data into 1 cell.
I have a form created from a table that has text boxes for a date and for a
table letter and a text box for a reference #. I am trying to combine the
date and table letter to make a reference #.
Example: Date 8/14/2007, Table Letter C
I would like the reference number to be 081407C
I can get this to work in excel with =TEXT(C8,"MMDDYY") & TEXT(F8,"0") with
C8 being the cell under the Date column and F8 being the cell under Table
Letter column.

Any help suggested would be appreciated.
 
try this in the control source of your new text box.

= str(format("mmddyyy",[YourDateField])) & [TableLetter]

If you use VBA, you'll need appropriate form references, e.g.,
Me.[TableLetter]
 
Although this is not a good idea, the formula is:
=Format(Date,"mmddyy") & TableLetter

BTW, there are no cells in Access.
If it is in a table or query, it is a field.
If it is on a form, it is a control.
 
One too many y's (you have 3, you only need 2) and the str is extraneous, the
Format returns a string.
--
Dave Hargis, Microsoft Access MVP


Pendragon said:
try this in the control source of your new text box.

= str(format("mmddyyy",[YourDateField])) & [TableLetter]

If you use VBA, you'll need appropriate form references, e.g.,
Me.[TableLetter]

Patrick said:
I am using access 2002 and would like to combine 2 cells of data into 1 cell.
I have a form created from a table that has text boxes for a date and for a
table letter and a text box for a reference #. I am trying to combine the
date and table letter to make a reference #.
Example: Date 8/14/2007, Table Letter C
I would like the reference number to be 081407C
I can get this to work in excel with =TEXT(C8,"MMDDYY") & TEXT(F8,"0") with
C8 being the cell under the Date column and F8 being the cell under Table
Letter column.

Any help suggested would be appreciated.
Click to expand...
Click to expand...
 
I received an error when I entered the information in. I put in
=Str(Format("mmddyyy",[Date Processed])) & [Table Letter]
I received the #error in the reference # cell.

What should I do next?

Pendragon said:
try this in the control source of your new text box.

= str(format("mmddyyy",[YourDateField])) & [TableLetter]

If you use VBA, you'll need appropriate form references, e.g.,
Me.[TableLetter]

Patrick said:
I am using access 2002 and would like to combine 2 cells of data into 1 cell.
I have a form created from a table that has text boxes for a date and for a
table letter and a text box for a reference #. I am trying to combine the
date and table letter to make a reference #.
Example: Date 8/14/2007, Table Letter C
I would like the reference number to be 081407C
I can get this to work in excel with =TEXT(C8,"MMDDYY") & TEXT(F8,"0") with
C8 being the cell under the Date column and F8 being the cell under Table
Letter column.

Any help suggested would be appreciated.
Click to expand...
Click to expand...
 
See my previous post. Patrick is certainly trying to help and is on the
right track, but there is a typo and some extraneous code.
--
Dave Hargis, Microsoft Access MVP


Patrick said:
I received an error when I entered the information in. I put in
=Str(Format("mmddyyy",[Date Processed])) & [Table Letter]
I received the #error in the reference # cell.

What should I do next?

Pendragon said:
try this in the control source of your new text box.

= str(format("mmddyyy",[YourDateField])) & [TableLetter]

If you use VBA, you'll need appropriate form references, e.g.,
Me.[TableLetter]

Patrick said:
I am using access 2002 and would like to combine 2 cells of data into 1 cell.
I have a form created from a table that has text boxes for a date and for a
table letter and a text box for a reference #. I am trying to combine the
date and table letter to make a reference #.
Example: Date 8/14/2007, Table Letter C
I would like the reference number to be 081407C
I can get this to work in excel with =TEXT(C8,"MMDDYY") & TEXT(F8,"0") with
C8 being the cell under the Date column and F8 being the cell under Table
Letter column.

Any help suggested would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Thanks! I see where he was using 07 and not 2007. Also, I usually have to
type and retype a few times in order to get the sequence correct, so thanks
for fixing that as well.

Klatuu said:
One too many y's (you have 3, you only need 2) and the str is extraneous, the
Format returns a string.
--
Dave Hargis, Microsoft Access MVP


Pendragon said:
try this in the control source of your new text box.

= str(format("mmddyyy",[YourDateField])) & [TableLetter]

If you use VBA, you'll need appropriate form references, e.g.,
Me.[TableLetter]

Patrick said:
I am using access 2002 and would like to combine 2 cells of data into 1 cell.
I have a form created from a table that has text boxes for a date and for a
table letter and a text box for a reference #. I am trying to combine the
date and table letter to make a reference #.
Example: Date 8/14/2007, Table Letter C
I would like the reference number to be 081407C
I can get this to work in excel with =TEXT(C8,"MMDDYY") & TEXT(F8,"0") with
C8 being the cell under the Date column and F8 being the cell under Table
Letter column.

Any help suggested would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Not To wrory, msot pepole dnot tpye as wlel as I do, so it is urdensnadyable
that you colud mkae a mtsikae.

--
Dave Hargis, Microsoft Access MVP


Pendragon said:
Thanks! I see where he was using 07 and not 2007. Also, I usually have to
type and retype a few times in order to get the sequence correct, so thanks
for fixing that as well.

Klatuu said:
One too many y's (you have 3, you only need 2) and the str is extraneous, the
Format returns a string.
--
Dave Hargis, Microsoft Access MVP


Pendragon said:
try this in the control source of your new text box.

= str(format("mmddyyy",[YourDateField])) & [TableLetter]

If you use VBA, you'll need appropriate form references, e.g.,
Me.[TableLetter]

:

I am using access 2002 and would like to combine 2 cells of data into 1 cell.
I have a form created from a table that has text boxes for a date and for a
table letter and a text box for a reference #. I am trying to combine the
date and table letter to make a reference #.
Example: Date 8/14/2007, Table Letter C
I would like the reference number to be 081407C
I can get this to work in excel with =TEXT(C8,"MMDDYY") & TEXT(F8,"0") with
C8 being the cell under the Date column and F8 being the cell under Table
Letter column.

Any help suggested would be appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Now that the reference number is created how do I get the table updated from
the control that was created? I have a master table with a field named
Reference # and an update master table with a field named Reference #. I
have all the data in the master table and created the form from the update
master table. I have an update query to update the master table and would
like the Reference # control in the form, =Format(Date,"mmddyy") &
TableLetter, to show up in the update master table so that when I run my
update query I update the Reference # in the master table. In the update
query the field is titled Reference # and now we have the control in the form
changed from Reference # to =Format.....
 
Back
Top