extraing data from field

[Alex H]

Hi, I have 1300 records where in one field is the text "moderator......."

I needto extract everything after and including the word "Moderator" and
place it into another field.

Is thispossibleplease and whats the best way to go about it

Thanks

A

 

[Brendan Reynolds]

An update query ...

UPDATE tblTest SET tblTest.NewField =
Mid$([OldField],InStr(1,[OldField],"Moderator"))
WHERE (((InStr(1,[OldField],"Moderator"))>0));

Where "tblTest" is the name of your table, "NewField" is the name of the
field into which the text is to be placed, and "OldField" is the name of the
field from which the text is to be extracted.

See 'Mid Function' and 'InStr Function' in the help files for more
information.

 

[AlCamp]

Do an "Update" Query, and use this criteria for updating the your new field
Left([YourFieldName],9) = "moderator"
hth
Al Camp

 

[SJ]

Brendan

many thanks

Can I also remove the updated text from the old field?

Thanks

Alex

An update query ...

UPDATE tblTest SET tblTest.NewField =
Mid$([OldField],InStr(1,[OldField],"Moderator"))
WHERE (((InStr(1,[OldField],"Moderator"))>0));

Where "tblTest" is the name of your table, "NewField" is the name of the
field into which the text is to be placed, and "OldField" is the name of
the field from which the text is to be extracted.

See 'Mid Function' and 'InStr Function' in the help files for more
information.

--
Brendan Reynolds (MVP)

Hi, I have 1300 records where in one field is the text
"moderator......."

I needto extract everything after and including the word "Moderator" and
place it into another field.

Is thispossibleplease and whats the best way to go about it

Thanks

A

 

[Brendan Reynolds]

That would be ...

Left$([OldField],InStr(1,[OldField],"Moderator") - 1)

--
Brendan Reynolds (MVP)

Brendan

many thanks

Can I also remove the updated text from the old field?

Thanks

Alex

An update query ...

UPDATE tblTest SET tblTest.NewField =
Mid$([OldField],InStr(1,[OldField],"Moderator"))
WHERE (((InStr(1,[OldField],"Moderator"))>0));

Where "tblTest" is the name of your table, "NewField" is the name of the
field into which the text is to be placed, and "OldField" is the name of
the field from which the text is to be extracted.

See 'Mid Function' and 'InStr Function' in the help files for more
information.

--
Brendan Reynolds (MVP)

Hi, I have 1300 records where in one field is the text
"moderator......."

I needto extract everything after and including the word "Moderator" and
place it into another field.

Is thispossibleplease and whats the best way to go about it

Thanks

A