How do I move the articles?

  • Thread starter Thread starter Jules Payne
  • Start date Start date
J

Jules Payne

Hello all.

Can someone help me?

I am in the process of creating a database which lists a lot of titles, many
of which begin with one of the articles (a, an, the). Part way through, I
realised I should have put the articles at the end of the titles.

Example title: "The End"
I have typed: "The End"
I should have typed "End, The"

I am positive there is a way of changing these round automatically (maybe a
macro?) but I'm damned if I can find out how (tried Access help, searching
this newsgroup and Google).

It wouldn't take too long for me to do them manually, since I only got about
15% of the way before I realised, but this would defeat the object of trying
to learn how Access works.

If you have any suggestions or a sample macro that may help me can you
please post a reply?
 
There's nothing built into Access that will do this for you.

However, since you've got a limited number of articles, you could try
running a series of Update queries.

If, for example, you use WHERE Title = "The *" as the WHERE clause in your
query, you'll retrieve all rows that start with the word The. Since you know
"The " consumes 4 positions, you want everything in the string starting at
position 5, and then concatenate ", The" to it.

The SQL would look something like:

UPDATE MyTable
SET Title = Mid$([Title], 5) & ", The"
WHERE Title = "The *"

You'll also need

UPDATE MyTable
SET Title = Mid$([Title], 3) & ", A"
WHERE Title = "A *"

and

UPDATE MyTable
SET Title = Mid$([Title], 4) & ", An"
WHERE Title = "An *"
 
Hi Jules,

The simplest way to do this if you're "getting started" is with a series
of queries. Make a backup copy of your database first in case of
accidents.

1) Create a new query in design view, returning just the title field
(which I'll assume is called Title). In the Criteria cell, put
Like "The *"
so it only returns titles beginning with "The". Note the space to avoid
picking up words that begin with The.

2) When this is working, use the Query menu command to convert the query
into an Update query. In the Update To cell, put
Mid([Title], 5) & ", The"
to drop the first four letters "The " and add ", The" at the end.

3) Run the query.

4) Modify the query to do the same with "A" and then "An".




Hello all.

Can someone help me?

I am in the process of creating a database which lists a lot of titles, many
of which begin with one of the articles (a, an, the). Part way through, I
realised I should have put the articles at the end of the titles.

Example title: "The End"
I have typed: "The End"
I should have typed "End, The"

I am positive there is a way of changing these round automatically (maybe a
macro?) but I'm damned if I can find out how (tried Access help, searching
this newsgroup and Google).

It wouldn't take too long for me to do them manually, since I only got about
15% of the way before I realised, but this would defeat the object of trying
to learn how Access works.

If you have any suggestions or a sample macro that may help me can you
please post a reply?
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Thanks for your reply. Did the job nicely!

Jules

John Nurick said:
Hi Jules,

The simplest way to do this if you're "getting started" is with a series
of queries. Make a backup copy of your database first in case of
accidents.

1) Create a new query in design view, returning just the title field
(which I'll assume is called Title). In the Criteria cell, put
Like "The *"
so it only returns titles beginning with "The". Note the space to avoid
picking up words that begin with The.

2) When this is working, use the Query menu command to convert the query
into an Update query. In the Update To cell, put
Mid([Title], 5) & ", The"
to drop the first four letters "The " and add ", The" at the end.

3) Run the query.

4) Modify the query to do the same with "A" and then "An".




Hello all.

Can someone help me?

I am in the process of creating a database which lists a lot of titles, many
of which begin with one of the articles (a, an, the). Part way through, I
realised I should have put the articles at the end of the titles.

Example title: "The End"
I have typed: "The End"
I should have typed "End, The"

I am positive there is a way of changing these round automatically (maybe a
macro?) but I'm damned if I can find out how (tried Access help, searching
this newsgroup and Google).

It wouldn't take too long for me to do them manually, since I only got about
15% of the way before I realised, but this would defeat the object of trying
to learn how Access works.

If you have any suggestions or a sample macro that may help me can you
please post a reply?
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Click to expand...
 
Back
Top