Make report alpha then numeric

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

"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

"beckyboop" wrote in message
...
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

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

--
Build a little, test a little.


"beckyboop" wrote:

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" wrote:

"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

"beckyboop" wrote in message
...
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

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.

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

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

"beckyboop" wrote in message
...


"Jeff Boyce" wrote:

"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

"beckyboop" wrote in message
...
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

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.

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jeff Boyce" wrote in message
...
Then don't use numbers. What about using something like:

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

"beckyboop" wrote in message
...


"Jeff Boyce" wrote:

"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

"beckyboop" wrote in message
...
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

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.

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

"KARL DEWEY" wrote:

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

--
Build a little, test a little.

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

"Jeff Boyce" wrote:

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

Regards

Jeff Boyce
Microsoft Office/Access MVP

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

Ooooh! You are soooooo right!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Clifford Bass" wrote in message
...
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

"Jeff Boyce" wrote:

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

Regards

Jeff Boyce
Microsoft Office/Access MVP