inserting a dash

[G.]

I am trying add two dashed line between six numbers. I am using an update
query to change the table but my "Update to" argument must be incorrect.

The field is called MR_# and the data in the field is currently 123456. But
I need it to be 12-34-56. I am using:

Left([MR_#],2) & "-" & Mid([MR_#],3,2) & "-" & Right([MR_#],2)

Can anyone help with what I am doing wrong?

Thank you.

 

[KARL DEWEY]

If the field is datatype number you will not be able to have dashes. You can
have dashes in a text field.
You also might read up on 'Input Mask' and Format.

 

[G.]

Thank you for the reply. I changed the dataype to Text and built another
update query using the new table but the query still did not execute. My
suspicion is that the statement I am using is incorrect.

To explain better, I currently have a table with the data. I do not need to
enter more data, I just need to change what is currently there (all I want to
do is match the MR_# in this table to MR_# in another table but the format of
the data must be the same and the format in table 2 has dashes). So, I am not
sure how setting the input mask and format would help in this case.

Any help is appreciated. Thank you.

If the field is datatype number you will not be able to have dashes. You can
have dashes in a text field.
You also might read up on 'Input Mask' and Format.
--
KARL DEWEY
Build a little - Test a little

I am trying add two dashed line between six numbers. I am using an update
query to change the table but my "Update to" argument must be incorrect.

The field is called MR_# and the data in the field is currently 123456. But
I need it to be 12-34-56. I am using:

Left([MR_#],2) & "-" & Mid([MR_#],3,2) & "-" & Right([MR_#],2)

Can anyone help with what I am doing wrong?

Thank you.

 

[KARL DEWEY]

What did not execute? The update query or the comparison?
If you are doing comparrison then an update is not necessary, just use an
intermediate query to change the format.
--
KARL DEWEY
Build a little - Test a little

Thank you for the reply. I changed the dataype to Text and built another
update query using the new table but the query still did not execute. My
suspicion is that the statement I am using is incorrect.

To explain better, I currently have a table with the data. I do not need to
enter more data, I just need to change what is currently there (all I want to
do is match the MR_# in this table to MR_# in another table but the format of
the data must be the same and the format in table 2 has dashes). So, I am not
sure how setting the input mask and format would help in this case.

Any help is appreciated. Thank you.

If the field is datatype number you will not be able to have dashes. You can
have dashes in a text field.
You also might read up on 'Input Mask' and Format.
--
KARL DEWEY
Build a little - Test a little

I am trying add two dashed line between six numbers. I am using an update
query to change the table but my "Update to" argument must be incorrect.

The field is called MR_# and the data in the field is currently 123456. But
I need it to be 12-34-56. I am using:

Left([MR_#],2) & "-" & Mid([MR_#],3,2) & "-" & Right([MR_#],2)

Can anyone help with what I am doing wrong?

Thank you.

 

[Klatuu]

It doesn't matter what the orginal data type is. It is that the output data
type will be text. You also need the correct syntax for creating a
calculated field:

In the query builder:
MR: Format([MR_#], "00\-00\-00")

In SQL view:

SELECT Format([MR_#, "00\-00\-00") AS MR


--
Dave Hargis, Microsoft Access MVP

Thank you for the reply. I changed the dataype to Text and built another
update query using the new table but the query still did not execute. My
suspicion is that the statement I am using is incorrect.

To explain better, I currently have a table with the data. I do not need to
enter more data, I just need to change what is currently there (all I want to
do is match the MR_# in this table to MR_# in another table but the format of
the data must be the same and the format in table 2 has dashes). So, I am not
sure how setting the input mask and format would help in this case.

Any help is appreciated. Thank you.

If the field is datatype number you will not be able to have dashes. You can
have dashes in a text field.
You also might read up on 'Input Mask' and Format.
--
KARL DEWEY
Build a little - Test a little

I am trying add two dashed line between six numbers. I am using an update
query to change the table but my "Update to" argument must be incorrect.

The field is called MR_# and the data in the field is currently 123456. But
I need it to be 12-34-56. I am using:

Left([MR_#],2) & "-" & Mid([MR_#],3,2) & "-" & Right([MR_#],2)

Can anyone help with what I am doing wrong?

Thank you.

 

[G.]

Dave,

I have looked up questions and found answers in these Access discussion
groups for the last couple of months and you have always been dead on. Your
answers are clear and to the point.

As our president would say - you wreak of geneosity.

I know more because of people like yourself. Thanks for sharing your
knowledge.

G.

It doesn't matter what the orginal data type is. It is that the output data
type will be text. You also need the correct syntax for creating a
calculated field:

In the query builder:
MR: Format([MR_#], "00\-00\-00")

In SQL view:

SELECT Format([MR_#, "00\-00\-00") AS MR


--
Dave Hargis, Microsoft Access MVP

Thank you for the reply. I changed the dataype to Text and built another
update query using the new table but the query still did not execute. My
suspicion is that the statement I am using is incorrect.

To explain better, I currently have a table with the data. I do not need to
enter more data, I just need to change what is currently there (all I want to
do is match the MR_# in this table to MR_# in another table but the format of
the data must be the same and the format in table 2 has dashes). So, I am not
sure how setting the input mask and format would help in this case.

Any help is appreciated. Thank you.

If the field is datatype number you will not be able to have dashes. You can
have dashes in a text field.
You also might read up on 'Input Mask' and Format.
--
KARL DEWEY
Build a little - Test a little


:

I am trying add two dashed line between six numbers. I am using an update
query to change the table but my "Update to" argument must be incorrect.

The field is called MR_# and the data in the field is currently 123456. But
I need it to be 12-34-56. I am using:

Left([MR_#],2) & "-" & Mid([MR_#],3,2) & "-" & Right([MR_#],2)

Can anyone help with what I am doing wrong?

Thank you.

 

[Klatuu]

Thanks for the kudos, G.
Glad I could help.
BTW, I have been wrong at times
--
Dave Hargis, Microsoft Access MVP

Dave,

I have looked up questions and found answers in these Access discussion
groups for the last couple of months and you have always been dead on. Your
answers are clear and to the point.

As our president would say - you wreak of geneosity.

I know more because of people like yourself. Thanks for sharing your
knowledge.

G.

It doesn't matter what the orginal data type is. It is that the output data
type will be text. You also need the correct syntax for creating a
calculated field:

In the query builder:
MR: Format([MR_#], "00\-00\-00")

In SQL view:

SELECT Format([MR_#, "00\-00\-00") AS MR


--
Dave Hargis, Microsoft Access MVP

Thank you for the reply. I changed the dataype to Text and built another
update query using the new table but the query still did not execute. My
suspicion is that the statement I am using is incorrect.

To explain better, I currently have a table with the data. I do not need to
enter more data, I just need to change what is currently there (all I want to
do is match the MR_# in this table to MR_# in another table but the format of
the data must be the same and the format in table 2 has dashes). So, I am not
sure how setting the input mask and format would help in this case.

Any help is appreciated. Thank you.

:

If the field is datatype number you will not be able to have dashes. You can
have dashes in a text field.
You also might read up on 'Input Mask' and Format.
--
KARL DEWEY
Build a little - Test a little


:

I am trying add two dashed line between six numbers. I am using an update
query to change the table but my "Update to" argument must be incorrect.

The field is called MR_# and the data in the field is currently 123456. But
I need it to be 12-34-56. I am using:

Left([MR_#],2) & "-" & Mid([MR_#],3,2) & "-" & Right([MR_#],2)

Can anyone help with what I am doing wrong?

Thank you.