|
|
|
date: Fri, 15 Aug 2008 05:59:03 -0700,
group: microsoft.public.access.queries
back
RE: inserting a dash
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
"G." wrote:
> 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.
>
> "KARL DEWEY" wrote:
>
> > 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
> >
> >
> > "G." wrote:
> >
> > > 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.
date: Fri, 15 Aug 2008 08:50:02 -0700
author: Klatuu
RE: inserting a dash
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.
"Klatuu" wrote:
> 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
>
>
> "G." wrote:
>
> > 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.
> >
> > "KARL DEWEY" wrote:
> >
> > > 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
> > >
> > >
> > > "G." wrote:
> > >
> > > > 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.
date: Fri, 15 Aug 2008 09:00:00 -0700
author: G.
RE: inserting a dash
Thanks for the kudos, G.
Glad I could help.
BTW, I have been wrong at times :)
--
Dave Hargis, Microsoft Access MVP
"G." wrote:
> 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.
>
> "Klatuu" wrote:
>
> > 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
> >
> >
> > "G." wrote:
> >
> > > 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.
> > >
> > > "KARL DEWEY" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > "G." wrote:
> > > >
> > > > > 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.
date: Fri, 15 Aug 2008 09:07:03 -0700
author: Klatuu
|
|
