Make report alpha then numeric

[beckyboop]

My report sorts a field A-Z but now that the number 1 has been added, it
prints the 1 first then A-Z. What do I need in the expression? Access 2007

A Newby

 

[Jeff Boyce]

"How" depends on "what"...

If you are saying that the field you are sorting on includes both digits and
text characters, then you will get the digits sorted first before text
characters. That's how Access does the alphanumeric sort.

What is it that you want it to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[KARL DEWEY]

You could add a calculated field in your query that feeds the report like
this --
MySort_1: IIF(Left([Field_X], 1) IsNumber, "ZZZZZZZ" & [Field_X],
[Field_X])

In the report first sort by MySort_1 and then [Field_X].

 

[beckyboop]

"How" depends on "what"...

If you are saying that the field you are sorting on includes both digits and
text characters, then you will get the digits sorted first before text
characters. That's how Access does the alphanumeric sort.

What is it that you want it to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP



The report is an inventory of minutes recorded with books A-Z and the next book is #1. The report is like this:

A Appointments Book
Page #
Appointments made to Library Board 1
284
Appointments made to Park Board A
199

As these minutes were inventoried, Book A was the first book with book 1
currently being used. I do not want the newest entries in the beginning of
each category. I want book 1 to print after Book Z.

 

[Jeff Boyce]

Then don't use numbers. What about using something like:

A, B, C, ...
then
A1, A2, A3, ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

That is, don't use numbers except with letters.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Clifford Bass]

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

 

[Clifford Bass]

Hi Jeff,

I do not think that would result in the order she desires, but would
intermingle the new books with the older books:

A
A1
A2
B
B1
B2

She could use ZA, ZB, ZC, ... or Z1, Z2, Z3, or if as expected once she
gets up to 10, it should be Z01, Z02, Z03, etc. Or if allowing for hunderds:
Z001, Z002, Z003, etc. Or it allowing for ....., well, you get the drift.

Clifford Bass

 

[Clifford Bass]

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

 

[Jeff Boyce]

Ooooh! You are soooooo right!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[beckyboop]

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,

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

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

 

[John Spencer]

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

 

[Clifford Bass]

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]

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.

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

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?

 

[John W. Vinson]

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.

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.

 

[beckyboop]

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 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.

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.

 

[Clifford Bass]

Hi Becky,

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

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

Clifford Bass

 

[beckyboop]

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

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

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 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.

:

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 Spencer]

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

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

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

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.

 

[beckyboop]

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.

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

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

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

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.