Query to rearrange movie-title wording.

#1

I have Access2010 and I use this for a Movie Titles database.

I have already configured the a query to remove the "A" and "The" from
the titles to allow easier sorting and reading. This is used as a
source for a report.

How can I put the "A" or the "The" at the end of the title string so
as to make the title more readable?

Peter

#2

"Peter Jason" <pj (AT) jostle (DOT) com> skrev i en meddelelse
news:s3d507dnsqj960lujmkbg62phpr4jf7827 (AT) 4ax (DOT) com...

Quote:

I have Access2010 and I use this for a Movie Titles database.

I have already configured the a query to remove the "A" and "The" from
the titles to allow easier sorting and reading. This is used as a
source for a report.

How can I put the "A" or the "The" at the end of the title string so
as to make the title more readable?

Goto a new field in ur query
Write NewField: UrSortedField & "A" or & "The"
Bjarne

#3

It would help if you posted your method for removing A and The from the
titles. It could probably be modified to move the A or The to the end of the
title.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 6/22/2011 11:39 PM, Peter Jason wrote:

Quote:

I have Access2010 and I use this for a Movie Titles database.

I have already configured the a query to remove the "A" and "The" from
the titles to allow easier sorting and reading. This is used as a
source for a report.

How can I put the "A" or the "The" at the end of the title string so
as to make the title more readable?

Peter

#4

For the "The" case I use:
Expr1: Replace([MovieNameEnglish],"The ","")

And for the "A" case I use:
Expr2: Replace([Expr1],"A ","")

On Thu, 23 Jun 2011 09:03:50 -0400, John Spencer
<JSPENCER (AT) Hilltop (DOT) umbc> wrote:

Quote:

It would help if you posted your method for removing A and The from the
titles. It could probably be modified to move the A or The to the end of the
title.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On 6/22/2011 11:39 PM, Peter Jason wrote:
I have Access2010 and I use this for a Movie Titles database.

I have already configured the a query to remove the "A" and "The" from
the titles to allow easier sorting and reading. This is used as a
source for a report.

How can I put the "A" or the "The" at the end of the title string so
as to make the title more readable?

Peter

#5

On Thu, 23 Jun 2011 09:28:01 +0200, "bsn" <bsnSNABELAoncableDOTdk>
wrote:

Quote:

"Peter Jason" <pj (AT) jostle (DOT) com> skrev i en meddelelse
news:s3d507dnsqj960lujmkbg62phpr4jf7827 (AT) 4ax (DOT) com...
I have Access2010 and I use this for a Movie Titles database.

I have already configured the a query to remove the "A" and "The" from
the titles to allow easier sorting and reading. This is used as a
source for a report.

How can I put the "A" or the "The" at the end of the title string so
as to make the title more readable?

Goto a new field in ur query
Write NewField: UrSortedField & "A" or & "The"
Bjarne

But not all titles contain "The" and/or "A"

Peter

#6

"Peter Jason" skrev i meddelelsen
news:k5d707d1n9d49pd2mq6tlmg1rflncbecqp (AT) 4ax (DOT) com...

On Thu, 23 Jun 2011 09:28:01 +0200, "bsn" <bsnSNABELAoncableDOTdk>
wrote:

Quote:

"Peter Jason" <pj (AT) jostle (DOT) com> skrev i en meddelelse
news:s3d507dnsqj960lujmkbg62phpr4jf7827 (AT) 4ax (DOT) com...
I have Access2010 and I use this for a Movie Titles database.

I have already configured the a query to remove the "A" and "The" from
the titles to allow easier sorting and reading. This is used as a
source for a report.

How can I put the "A" or the "The" at the end of the title string so
as to make the title more readable?

Goto a new field in ur query
Write NewField: UrSortedField & "A" or & "The"
Bjarne

But not all titles contain "The" and/or "A"

Peter

-------------------------------------------------------------------------------

Use the IIf function...
Bjarne

#7

"bsn" skrev i meddelelsen
news:4e04670c$0$56792$edfadb0f (AT) dtext02 (DOT) news.tele.dk...

"Peter Jason" skrev i meddelelsen
news:k5d707d1n9d49pd2mq6tlmg1rflncbecqp (AT) 4ax (DOT) com...

On Thu, 23 Jun 2011 09:28:01 +0200, "bsn" <bsnSNABELAoncableDOTdk>
wrote:

Quote:

"Peter Jason" <pj (AT) jostle (DOT) com> skrev i en meddelelse
news:s3d507dnsqj960lujmkbg62phpr4jf7827 (AT) 4ax (DOT) com...
I have Access2010 and I use this for a Movie Titles database.

I have already configured the a query to remove the "A" and "The" from
the titles to allow easier sorting and reading. This is used as a
source for a report.

How can I put the "A" or the "The" at the end of the title string so
as to make the title more readable?

Goto a new field in ur query
Write NewField: UrSortedField & "A" or & "The"
Bjarne

But not all titles contain "The" and/or "A"

Peter

-------------------------------------------------------------------------------

Use the IIf function...
IIf(InStr(YourField;"The");YourField &
"The";IIf(InStr(YourField;"A");YourField & "A";YourField))
Bjarne

#8

"bsn" skrev i meddelelsen
news:4e046fc4$0$56784$edfadb0f (AT) dtext02 (DOT) news.tele.dk...

"bsn" skrev i meddelelsen
news:4e04670c$0$56792$edfadb0f (AT) dtext02 (DOT) news.tele.dk...

"Peter Jason" skrev i meddelelsen
news:k5d707d1n9d49pd2mq6tlmg1rflncbecqp (AT) 4ax (DOT) com...

On Thu, 23 Jun 2011 09:28:01 +0200, "bsn" <bsnSNABELAoncableDOTdk>
wrote:

Quote:

"Peter Jason" <pj (AT) jostle (DOT) com> skrev i en meddelelse
news:s3d507dnsqj960lujmkbg62phpr4jf7827 (AT) 4ax (DOT) com...
I have Access2010 and I use this for a Movie Titles database.

I have already configured the a query to remove the "A" and "The" from
the titles to allow easier sorting and reading. This is used as a
source for a report.

How can I put the "A" or the "The" at the end of the title string so
as to make the title more readable?

Goto a new field in ur query
Write NewField: UrSortedField & "A" or & "The"
Bjarne

But not all titles contain "The" and/or "A"

Peter

-------------------------------------------------------------------------------

Use the IIf function...
IIf(InStr(YourField;"The");YourSortedField &
"The";IIf(InStr(YourField;"A");YourSortedField & "A";YourField))
Bjarne

#9

Peter Jason wrote:

Quote:

For the "The" case I use:
Expr1: Replace([MovieNameEnglish],"The ","")

That will replace any occurence of "The" with an empty space, resulting in
titles like "Phantom of Opera" and "Meet Fockers"

Quote:

And for the "A" case I use:
Expr2: Replace([Expr1],"A ","")

Same objection. Personally, I would create a VBA function to do this but it
is possible with expressions:

Expr1: iif(left([MovieNameEnglish],4)="The ",Mid([MovieNameEnglish],5) & ",
The", [MovieNameEnglish])

Same idea for Expr2.

Question: should you move "An " to the end of the title as well ("An Affair
to Remember")?

#10

On Fri, 24 Jun 2011 14:32:12 -0400, "Bob Barrows"
<reb01501 (AT) NOSPAMyahoo (DOT) com> wrote:

Quote:

Peter Jason wrote:
For the "The" case I use:
Expr1: Replace([MovieNameEnglish],"The ","")

That will replace any occurence of "The" with an empty space, resulting in
titles like "Phantom of Opera" and "Meet Fockers"

And for the "A" case I use:
Expr2: Replace([Expr1],"A ","")

Same objection. Personally, I would create a VBA function to do this but it
is possible with expressions:

Expr1: iif(left([MovieNameEnglish],4)="The ",Mid([MovieNameEnglish],5) & ",
The", [MovieNameEnglish])

Same idea for Expr2.

Question: should you move "An " to the end of the title as well ("An Affair
to Remember")?

Thanks, but I can't get it to work: I type the above as:

Expr4: iif(left([MovieNameEnglish],4)="The ",Mid([MovieNameEnglish],5)
& ", The", [MovieNameEnglish])

The query grid acceps it, but nothing shows up when the query is run.

Peter