On Sat, 25 Jun 2011 14:47:37 +1000, Peter Jason <pj (AT) jostle (DOT) com> wrote:

Sorry, false alarm. There was a Cr in you expression that had to be
removed.

I used:
1/ To move the "And":

Expr5: IIf(Left([MovieNameEnglish],4)="The ",Mid([MovieNameEnglish],5)
& ", The",[MovieNameEnglish])

2/ To move the "A":

Expr6: IIf(Left([Expr5],2)="A ",Mid([Expr5],3) & ", A",[Expr5])


Unfortunately when I run the query and the report based on it, Access
throws up a blank parameter box which requires two Cr for the query
and report to run.

Any idea what causes this?

Regards, Peter

"Peter Jason" <pj (AT) jostle (DOT) com> wrote

is why I would have used a VBA function to do this - VBA code can be
formatted to avoid the chars/line limit imposed by newsreaders. Here is what
such a function would look like.

1. If you have no modules in the Modules tab of your database window, click
the New button to create a new one - you can keep the name Module1 if you
want, or enter a name like MyFunctions if you desire. Otherwise, doubleclick
one of the existing modules to open it.

2. In the resulting code window enter:

Public Function GetSortableTitle(ByVal pTitle As String) _
As String
if len(pTitle)>0 then
if left(pTitle,2)="A " Then
pTitle=Mid(pTitle,3) & ", A"
end if
if left(pTitle,3)="An " Then
pTitle=Mid(pTitle,4) & ", An"
end if
if left(pTitle,4)="The " Then
pTitle=Mid(pTitle,5) & ", The"
end if
end if
SortableTitle=pTitle
End Function

In your query, use:
SortableTitle: GetSortableTitle([MovieNameEnglish])

If you do it in the query, you need to nest the IIF statements all in one
expression. That expression might look like the following - all one line.

IIf([MovieNameEnglish] Like "The *", Mid([MovieNameEnglish],5) & ", The"
, IIF([MovieNameEnglish] Like "A *", Mid([MovieNameEnglish],3) & ", A")
, IIF([MovieNameEnglish] Like "An *", Mid([MovieNameEnglish],4) & ", An"
, [MovieNameEnglish])))

Note that the last choice was to return MovieNameEnglish in the case that it
did not start with The, An, or A. Hope this helps. As noted by Bob Barrows
you **might** be better off using a custom VBA function to do this.

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

On 6/25/2011 1:39 AM, Peter Jason wrote:

A different approach.....

I understand that you remove the words "A" and "The" in order to make
sorting easier. Why not leave the title alone and create a query with
one additional field called Title2. Title2 would be created by
removing the selected words. Then, in your sorting and other
functions, use the Title2 field. And when you want to use the true
title, you still have the untouched field.

Fred Zuckerman



On Jun 25, 8:38*am, John Spencer <JSPEN... (AT) Hilltop (DOT) umbc> wrote:

I believe that's exactly what he's doing. See the very first post in this
thread where he says "This is used as a
source for a report."

zuckermanf (AT) gmail (DOT) com wrote: