Make report alpha then numeric

I hate to be really dumb, but I have tried these suggestions in numerous
places and numerous ways. When I sent in for "New Users", I mean literally
"New User".

This is an inventory of subjects that were discussed in Council meetings
since the 60's. I had Access 2000 and had the database working fine until
book Z was finished and Book 1 came in. I will not be here long enough to
get to Book 10!

I now have Access 2007 and I cannot get the entries of book 1 to come after
book Z (chronological order). I need detailed help if you have time. Where
do I put the MySort.... at?

"Clifford Bass" wrote:

Hi Becky,

Correction: IsNumber should be IsNumeric.

Of course, these will fail to sort correctly once you get up to 10 for
a book number because your second sort is a string, not a number. So you may
want to add a second sort:

MySort_2: IIf(IsNumeric([Field_X]), CLng([Field_X]), 0)

So for these values you get these sort values
A A 0
B B 0
1 ZZZZZZZ 1
2 ZZZZZZZ 2
10 ZZZZZZZ 10
100 ZZZZZZZ 100
1000 ZZZZZZZ 1000

Where the second sort value is numeric instead of a string. In this
case you would not need to sort by [Field_X].

Hope that helps,

Clifford Bass

"Clifford Bass" wrote:

Hi Karl and Becky,

I think that should be:

MySort_1: IIf(IsNumber(Left([Field_X], 1)), "ZZZZZZZ", [Field_X])

Or, if there is never going to be a book with both a number and a
letter, maybe just:

MySort_1: IIf(IsNumber([Field_X]), "ZZZZZZZ", [Field_X])

Alternatively, it may make sense to add in a "Sort_Order" field and
populate that as desired.

Clifford Bass

Add a new calculated field to your query. Assuming your field is named
BookNumber you would enter the following in the field and sort "cells".
Obviously replacing BookNumber with the name of the field that contains the
book indentification.

Field: MySort: IsNumeric([BookNumber],"ZZZ" &
Format([BookNumber],"000"),[BookNumber])
Sort: Ascending

That will keep everying in order until you reach book 1000. In other words,
we will probably be immune from recrimination at the point book 1000 is reached.

If you aren't using a query, then you need to so you can sort correctly,
publish reports in the correct order, etc.

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

beckyboop wrote:
I hate to be really dumb, but I have tried these suggestions in numerous
places and numerous ways. When I sent in for "New Users", I mean literally
"New User".

This is an inventory of subjects that were discussed in Council meetings
since the 60's. I had Access 2000 and had the database working fine until
book Z was finished and Book 1 came in. I will not be here long enough to
get to Book 10!

I now have Access 2007 and I cannot get the entries of book 1 to come after
book Z (chronological order). I need detailed help if you have time. Where
do I put the MySort.... at?

Hi Becky,

And a final alternative to the other suggestions, inspired by your
phrase "chronolgical order": Add one or two fields to your table: Start_Date
and maybe End_Date. Fill in the dates for the existing books. You can now
sort on the Start_Date. The existing and any future names/numbers the books
will not matter one iota.

Clifford Bass

"beckyboop" wrote:

I hate to be really dumb, but I have tried these suggestions in numerous
places and numerous ways. When I sent in for "New Users", I mean literally
"New User".

This is an inventory of subjects that were discussed in Council meetings
since the 60's. I had Access 2000 and had the database working fine until
book Z was finished and Book 1 came in. I will not be here long enough to
get to Book 10!

I now have Access 2007 and I cannot get the entries of book 1 to come after
book Z (chronological order). I need detailed help if you have time. Where
do I put the MySort.... at?

I have tried this and I get "The expression you entered has a function
containing the wrong number of arguments". I typed it in exactly like you
have listed (field is named Book) assuming that the & is a continuance of the
expression. I do not see a "Format Field" in the query design view.

"John Spencer" wrote:

Add a new calculated field to your query. Assuming your field is named
BookNumber you would enter the following in the field and sort "cells".
Obviously replacing BookNumber with the name of the field that contains the
book indentification.

Field: MySort: IsNumeric([BookNumber],"ZZZ" &
Format([BookNumber],"000"),[BookNumber])
Sort: Ascending

That will keep everying in order until you reach book 1000. In other words,
we will probably be immune from recrimination at the point book 1000 is reached.

If you aren't using a query, then you need to so you can sort correctly,
publish reports in the correct order, etc.

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

beckyboop wrote:
I hate to be really dumb, but I have tried these suggestions in numerous
places and numerous ways. When I sent in for "New Users", I mean literally
"New User".

This is an inventory of subjects that were discussed in Council meetings
since the 60's. I had Access 2000 and had the database working fine until
book Z was finished and Book 1 came in. I will not be here long enough to
get to Book 10!

I now have Access 2007 and I cannot get the entries of book 1 to come after
book Z (chronological order). I need detailed help if you have time. Where
do I put the MySort.... at?

On Wed, 29 Jul 2009 11:38:01 -0700, beckyboop
wrote:

I have tried this and I get "The expression you entered has a function
containing the wrong number of arguments". I typed it in exactly like you
have listed (field is named Book) assuming that the & is a continuance of the
expression. I do not see a "Format Field" in the query design view.

"John Spencer" wrote:

Add a new calculated field to your query. Assuming your field is named
BookNumber you would enter the following in the field and sort "cells".
Obviously replacing BookNumber with the name of the field that contains the
book indentification.

Field: MySort: IsNumeric([BookNumber],"ZZZ" &
Format([BookNumber],"000"),[BookNumber])
Sort: Ascending


I think you got caught by wordwrap on the newsgroup. The lines

Field: MySort: IsNumeric([BookNumber],"ZZZ" &
Format([BookNumber],"000"),[BookNumber])

should all be on one line (one Field cell) in the query grid.
--

John W. Vinson [MVP]

I typed: MySort: IsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book])

I get the error: the expression you entered has a function containing the
wrong number of arguments.

I do appreciate your help.

"John W. Vinson" wrote:

On Wed, 29 Jul 2009 11:38:01 -0700, beckyboop
wrote:

I have tried this and I get "The expression you entered has a function
containing the wrong number of arguments". I typed it in exactly like you
have listed (field is named Book) assuming that the & is a continuance of the
expression. I do not see a "Format Field" in the query design view.

"John Spencer" wrote:

Add a new calculated field to your query. Assuming your field is named
BookNumber you would enter the following in the field and sort "cells".
Obviously replacing BookNumber with the name of the field that contains the
book indentification.

Field: MySort: IsNumeric([BookNumber],"ZZZ" &
Format([BookNumber],"000"),[BookNumber])
Sort: Ascending


I think you got caught by wordwrap on the newsgroup. The lines

Field: MySort: IsNumeric([BookNumber],"ZZZ" &
Format([BookNumber],"000"),[BookNumber])

should all be on one line (one Field cell) in the query grid.
--

John W. Vinson [MVP]

Hi Becky,

He forgot the IIf() part. Try (all one line):

MySort: IIf(IsNumeric([Book]),"ZZZ" & Format([Book],"000"),[Book])

Clifford Bass

"beckyboop" wrote:

I typed: MySort: IsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book])

I get the error: the expression you entered has a function containing the
wrong number of arguments.

I do appreciate your help.

I typed MySort: IIfIsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book])

I get the error: undefined function 'Format' in expression

"beckyboop" wrote:

I typed: MySort: IsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book])

I get the error: the expression you entered has a function containing the
wrong number of arguments.

I do appreciate your help.

"John W. Vinson" wrote:

On Wed, 29 Jul 2009 11:38:01 -0700, beckyboop
wrote:

I have tried this and I get "The expression you entered has a function
containing the wrong number of arguments". I typed it in exactly like you
have listed (field is named Book) assuming that the & is a continuance of the
expression. I do not see a "Format Field" in the query design view.

"John Spencer" wrote:

Add a new calculated field to your query. Assuming your field is named
BookNumber you would enter the following in the field and sort "cells".
Obviously replacing BookNumber with the name of the field that contains the
book indentification.

Field: MySort: IsNumeric([BookNumber],"ZZZ" &
Format([BookNumber],"000"),[BookNumber])
Sort: Ascending


I think you got caught by wordwrap on the newsgroup. The lines

Field: MySort: IsNumeric([BookNumber],"ZZZ" &
Format([BookNumber],"000"),[BookNumber])

should all be on one line (one Field cell) in the query grid.
--

John W. Vinson [MVP]

Computers sure are finicky!
There should be a parenthesis Between IIF and IsNumeric
There should be a closing parenthesis after the first [Book]

MySort: IIf(IsNumeric([Book]),"ZZZ" & Format([Book],"000"),[Book])

If you still get an error then post back with the error (as you have been
patiently doing) and we will try again.

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

beckyboop wrote:
I typed MySort: IIfIsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book])

I get the error: undefined function 'Format' in expression

"beckyboop" wrote:

I typed: MySort: IsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book])

I get the error: the expression you entered has a function containing the
wrong number of arguments.

I do appreciate your help.

My bad John.

I did type it that way in the query. When I try the data sheet view, I get
the error: undefined function 'Format' in expression.

"John Spencer" wrote:

Computers sure are finicky!
There should be a parenthesis Between IIF and IsNumeric
There should be a closing parenthesis after the first [Book]

MySort: IIf(IsNumeric([Book]),"ZZZ" & Format([Book],"000"),[Book])

If you still get an error then post back with the error (as you have been
patiently doing) and we will try again.

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

beckyboop wrote:
I typed MySort: IIfIsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book])

I get the error: undefined function 'Format' in expression

"beckyboop" wrote:

I typed: MySort: IsNumeric([Book],"ZZZ" & Format([Book],"000"),[Book])

I get the error: the expression you entered has a function containing the
wrong number of arguments.

I do appreciate your help.