Is their a way to delete leading Apostophe on some records in the same column?
--
Tom

On 3 jan, 16:51, Tom <T...@discussions.microsoft.com> wrote:
> Is their a way to delete leading Apostophe on some records in the same column?
> --
> Tom

You can use an update query like this one:

UPDATE YourTable SET YourField = Mid([YourField],2)
WHERE Left([YourField],1="'";


Groeten,

Peter
http://access.xps350.com

On 3 jan, 16:51, Tom <T...@discussions.microsoft.com> wrote:
> Is their a way to delete leading Apostophe on some records in the same column?
> --
> Tom

Sorry, must be:

UPDATE YourTable SET YourField = Mid([YourField],2)
WHERE Left([YourField],1)="'";


Groeten,

Peter
http://access.xps350.com

I tried the second part of formula in a update query and it put zero in all
fields. Can you be a little more specific? What does or where is UPDATE
"YourTable SET"?
--
Tom


"XPS350" wrote:

> On 3 jan, 16:51, Tom <T...@discussions.microsoft.com> wrote:
> > Is their a way to delete leading Apostophe on some records in the same column?
> > --
> > Tom
>
> Sorry, must be:
>
> UPDATE YourTable SET YourField = Mid([YourField],2)
> WHERE Left([YourField],1)="'";
>
>
> Groeten,
>
> Peter
> http://access.xps350.com
> .
>

On 4 jan, 19:19, Tom <T...@discussions.microsoft.com> wrote:
> I tried the second part of formula in a update query and it put zero in all
> fields. *Can you be a little more specific? *What does or where is UPDATE
> "YourTable SET"?
> --
> Tom
>
> "XPS350" wrote:
> > On 3 jan, 16:51, Tom <T...@discussions.microsoft.com> wrote:
> > > Is their a way to delete leading Apostophe on some records in the same column?
> > > --
> > > Tom
>
> > Sorry, must be:
>
> > UPDATE YourTable SET YourField = Mid([YourField],2)
> > WHERE Left([YourField],1)="'";
>
> > Groeten,
>
> > Peter
> >http://access.xps350.com
> > .

You make a new query and do not select a table. In desig mode change
the view of the query to "SQL" and enter the given code, replacing
YourTable/YourField with the correct names.

Groeten,

Peter
http://access.xps350.com

This is what I put in the SQL view query--My table name is 2009-1 and column
is Shipment ID #---The below will not work to only eliminate ' on some
records. You may think I know more than I do and I can not get it to work.
Sorry and thanks for your time

UPDATE 2009-1 SET Shipment ID # = Mid([Shipment ID #],2)
WHERE Left([Shipment ID #],1)="'";
--
Tom


"XPS350" wrote:

> On 4 jan, 19:19, Tom <T...@discussions.microsoft.com> wrote:
> > I tried the second part of formula in a update query and it put zero in all
> > fields. Can you be a little more specific? What does or where is UPDATE
> > "YourTable SET"?
> > --
> > Tom
> >
> > "XPS350" wrote:
> > > On 3 jan, 16:51, Tom <T...@discussions.microsoft.com> wrote:
> > > > Is their a way to delete leading Apostophe on some records in the same column?
> > > > --
> > > > Tom
> >
> > > Sorry, must be:
> >
> > > UPDATE YourTable SET YourField = Mid([YourField],2)
> > > WHERE Left([YourField],1)="'";
> >
> > > Groeten,
> >
> > > Peter
> > >http://access.xps350.com
> > > .
>
> You make a new query and do not select a table. In desig mode change
> the view of the query to "SQL" and enter the given code, replacing
> YourTable/YourField with the correct names.
>
> Groeten,
>
> Peter
> http://access.xps350.com
> .
>

You are paying the penalty for including spaces and symbols in table and
field names. In the future consider using a naming convention that doesn't
allow this. Try:

UPDATE [2009-1] SET [Shipment ID #] = Mid([Shipment ID #],2)
WHERE Left([Shipment ID #],1)="'";

--
Duane Hookom
Microsoft Access MVP


"Tom" wrote:

> This is what I put in the SQL view query--My table name is 2009-1 and column
> is Shipment ID #---The below will not work to only eliminate ' on some
> records. You may think I know more than I do and I can not get it to work.
> Sorry and thanks for your time
>
> UPDATE 2009-1 SET Shipment ID # = Mid([Shipment ID #],2)
> WHERE Left([Shipment ID #],1)="'";
> --
> Tom
>
>
> "XPS350" wrote:
>
> > On 4 jan, 19:19, Tom <T...@discussions.microsoft.com> wrote:
> > > I tried the second part of formula in a update query and it put zero in all
> > > fields. Can you be a little more specific? What does or where is UPDATE
> > > "YourTable SET"?
> > > --
> > > Tom
> > >
> > > "XPS350" wrote:
> > > > On 3 jan, 16:51, Tom <T...@discussions.microsoft.com> wrote:
> > > > > Is their a way to delete leading Apostophe on some records in the same column?
> > > > > --
> > > > > Tom
> > >
> > > > Sorry, must be:
> > >
> > > > UPDATE YourTable SET YourField = Mid([YourField],2)
> > > > WHERE Left([YourField],1)="'";
> > >
> > > > Groeten,
> > >
> > > > Peter
> > > >http://access.xps350.com
> > > > .
> >
> > You make a new query and do not select a table. In desig mode change
> > the view of the query to "SQL" and enter the given code, replacing
> > YourTable/YourField with the correct names.
> >
> > Groeten,
> >
> > Peter
> > http://access.xps350.com
> > .
> >