Still Stuck on Alpha Numerics

I need to sort a numbering system comprised by multiple alpha characters and
numerics, and I need it to sort this list numerically, and not using a text
convention.
The records are in this general format with each character in a separate
column (field) defined either as text or numeric:
A.1
A.1.1
A.1.1.a
A.1.1.a.1
…
Z.1.1.a.1, etc.

In an earlier help session, I was given this solution:
DisplayNumber:[FirstAlpha] & ("." + [FirstNumber]) & ("." + [SecondAlpha]) &
("." + [Second Number]) & ("." + [ThirdNumber])

With this advice: Don't sort on the calculated field, but on the individual
level fields, otherwise you will be back to square one. But I do not know
how to apply this advice.

I have tried putting the solution into a query in the first empty field
after the 5 fields and I am not having much luck. The query asks for data to
be input, and then it populates the entire calculated field with whatever I
input, rather than obtaining the information from the 5 fields I had listed
(all the fields are contained in one table). If I do not enter any data,
and only hit return, then it only places “0�s in the numeric fields and
nothing is placed in the text fields.

These are my questions: Would I insert the first field name into
[FirstAlpha] (ditto for the rest?) It seems like something is missing, but I
have not been able to pinpoint it in any of my books and could use some
detailed step-by-step help, please!

On Thu, 7 Apr 2005 20:33:02 -0700, HOT FLASH
wrote:



These are my questions: Would I insert the first field name into
[FirstAlpha] (ditto for the rest?) It seems like something is missing, but I
have not been able to pinpoint it in any of my books and could use some
detailed step-by-step help, please!

Yes. You never posted your fieldnames, so the person who responded had
to make guesses at what they might be. You can see your table; we
cannot.

There should be nothing on the Criteria line; what you should have is
your five fields (whatever they are named), each with Ascending on the
sort line, and then the edited expression in the next vacant Field
cell, replacing the guessed field name FirstAlpha with the actual name
of your first alphabetic field, and so on.

John W. Vinson[MVP]

Thank you John for replying.
I have added ascending to each query sort field (except for the calculated
field, as that generates an error statement of ‘data type mismatch in
criteria expression’.) I have put in the field names into the expression ,
and I get #ERROR in every record of the calculated field in Display mode.
I have triple-checked to make sure there are no mistakes. Suggestions?
(Hopefully)


"John Vinson" wrote:

On Thu, 7 Apr 2005 20:33:02 -0700, HOT FLASH
wrote:



These are my questions: Would I insert the first field name into
[FirstAlpha] (ditto for the rest?) It seems like something is missing, but I
have not been able to pinpoint it in any of my books and could use some
detailed step-by-step help, please!

Yes. You never posted your fieldnames, so the person who responded had
to make guesses at what they might be. You can see your table; we
cannot.

There should be nothing on the Criteria line; what you should have is
your five fields (whatever they are named), each with Ascending on the
sort line, and then the edited expression in the next vacant Field
cell, replacing the guessed field name FirstAlpha with the actual name
of your first alphabetic field, and so on.

John W. Vinson[MVP]

On Thu, 7 Apr 2005 22:29:02 -0700, HOT FLASH
wrote:

Thank you John for replying.
I have added ascending to each query sort field (except for the calculated
field, as that generates an error statement of ‘data type mismatch in
criteria expression’.) I have put in the field names into the expression ,
and I get #ERROR in every record of the calculated field in Display mode.
I have triple-checked to make sure there are no mistakes. Suggestions?
(Hopefully)

Please open your Query in SQL view (use the View menu option from
query design). Copy and paste the probably cryptic (at this point,
you'll get familiar with it soon!) SQL text to a message here.

Can you create a query based on the table, select all fields, and view
the data? Are the fields other than the calculated field showing
#ERROR?

John W. Vinson[MVP]

Thanks John,
I am able to view all the data in the first five fields just fine. It is
only the last calculated field which shows the error. This is the SQL text
and it looks fine to me up to the point where it adds “with owner access
option� which I don’t understand…(yet!)


SELECT tbl_Standards.FirstAlpha, tbl_Standards.FirstNumber,
tbl_Standards.SecondNumber, tbl_Standards.SecondAlpha,
tbl_Standards.ThirdNumber, [FirstAlpha] & ("."+[FirstNumber]) &
("."+[SecondNumber]) & ("."+[SecondAlpha]) & ("."+[ThirdNumber]) AS DisplayNum
FROM tbl_Standards
ORDER BY tbl_Standards.FirstAlpha, tbl_Standards.FirstNumber,
tbl_Standards.SecondNumber, tbl_Standards.SecondAlpha,
tbl_Standards.ThirdNumber
WITH OWNERACCESS OPTION;


"John Vinson" wrote:

On Thu, 7 Apr 2005 22:29:02 -0700, HOT FLASH
wrote:

Thank you John for replying.
I have added ascending to each query sort field (except for the calculated
field, as that generates an error statement of ‘data type mismatch in
criteria expression’.) I have put in the field names into the expression ,
and I get #ERROR in every record of the calculated field in Display mode.
I have triple-checked to make sure there are no mistakes. Suggestions?
(Hopefully)

Please open your Query in SQL view (use the View menu option from
query design). Copy and paste the probably cryptic (at this point,
you'll get familiar with it soon!) SQL text to a message here.

Can you create a query based on the table, select all fields, and view
the data? Are the fields other than the calculated field showing
#ERROR?

John W. Vinson[MVP]

Hi John, I had a thought. Could it be that the null values are affecting the
query? In which case, would I use a NZ() to replace the nulls with a
zero-length string, and if so how exactly. Thank you!

"John Vinson" wrote:

On Thu, 7 Apr 2005 22:29:02 -0700, HOT FLASH
wrote:

Thank you John for replying.
I have added ascending to each query sort field (except for the calculated
field, as that generates an error statement of ‘data type mismatch in
criteria expression’.) I have put in the field names into the expression ,
and I get #ERROR in every record of the calculated field in Display mode.
I have triple-checked to make sure there are no mistakes. Suggestions?
(Hopefully)

Please open your Query in SQL view (use the View menu option from
query design). Copy and paste the probably cryptic (at this point,
you'll get familiar with it soon!) SQL text to a message here.

Can you create a query based on the table, select all fields, and view
the data? Are the fields other than the calculated field showing
#ERROR?

John W. Vinson[MVP]

On Fri, 8 Apr 2005 07:15:06 -0700, HOT FLASH
wrote:

Thanks John,
I am able to view all the data in the first five fields just fine. It is
only the last calculated field which shows the error. This is the SQL text
and it looks fine to me up to the point where it adds “with owner access
option” which I don’t understand…(yet!)


SELECT tbl_Standards.FirstAlpha, tbl_Standards.FirstNumber,
tbl_Standards.SecondNumber, tbl_Standards.SecondAlpha,
tbl_Standards.ThirdNumber, [FirstAlpha] & ("."+[FirstNumber]) &
("."+[SecondNumber]) & ("."+[SecondAlpha]) & ("."+[ThirdNumber]) AS DisplayNum
FROM tbl_Standards
ORDER BY tbl_Standards.FirstAlpha, tbl_Standards.FirstNumber,
tbl_Standards.SecondNumber, tbl_Standards.SecondAlpha,
tbl_Standards.ThirdNumber
WITH OWNERACCESS OPTION;

Hm. Looks ok to me! Try putting a blank on either side of each +
operator: e.g.

("." + [FirstNumber])

though I'd have thought Access could figure that out.

The WITH OWNERACCESS OPTION has to do with security. It shouldn't
affect this part of the problem.

John W. Vinson[MVP]

Hi John,
When I enter this “DisplayNum: [FirstAlpha] & ("." + [FirstNumber]) & ("." +
[SecondNumber]) & ("." + [SecondAlpha]) & ("." + [ThirdNumber])
Instead of seeing the following SQl text, it compresses the expression and
removes the spaces.
SELECT tbl_Standards.FirstAlpha, tbl_Standards.FirstNumber,
tbl_Standards.SecondNumber, tbl_Standards.SecondAlpha,
tbl_Standards.ThirdNumber, [FirstAlpha] & ("." + [FirstNumber]) & ("." +
[SecondNumber]) & ("." + [SecondAlpha]) & ("." + [ThirdNumber]) AS DisplayNum
FROM tbl_Standards
ORDER BY tbl_Standards.FirstAlpha, tbl_Standards.FirstNumber,
tbl_Standards.SecondNumber, tbl_Standards.SecondAlpha,
tbl_Standards.ThirdNumber WITH OWNERACCESS OPTION;

And I still get the error messages.
Here is a thought- These are the general Table attributes:
FirstAlpha SecondAlpha All Numbers
Data type: “text� “text� ‘number�
Field Size: “1� “5� “Byte
Format ‘’ “� “@�
Required: ‘yes’ “no� “no�
Allow 0 length ‘no� ‘yes’ decimal ‘0’
Indexed ‘yes(dup ok)’ ‘no’ ‘no’
UnicodeComrsn ‘yes’ ‘yes’ default ‘0’
Ime Mode ‘no control’ ‘no control’
Ime Sentence Mode ‘none’ ‘none’

Thanks for sticking with this!


"John Vinson" wrote:

On Fri, 8 Apr 2005 07:15:06 -0700, HOT FLASH
wrote:

Thanks John,
I am able to view all the data in the first five fields just fine. It is
only the last calculated field which shows the error. This is the SQL text
and it looks fine to me up to the point where it adds “with owner access
option� which I don’t understand…(yet!)


SELECT tbl_Standards.FirstAlpha, tbl_Standards.FirstNumber,
tbl_Standards.SecondNumber, tbl_Standards.SecondAlpha,
tbl_Standards.ThirdNumber, [FirstAlpha] & ("."+[FirstNumber]) &
("."+[SecondNumber]) & ("."+[SecondAlpha]) & ("."+[ThirdNumber]) AS DisplayNum
FROM tbl_Standards
ORDER BY tbl_Standards.FirstAlpha, tbl_Standards.FirstNumber,
tbl_Standards.SecondNumber, tbl_Standards.SecondAlpha,
tbl_Standards.ThirdNumber
WITH OWNERACCESS OPTION;

Hm. Looks ok to me! Try putting a blank on either side of each +
operator: e.g.

("." + [FirstNumber])

though I'd have thought Access could figure that out.

The WITH OWNERACCESS OPTION has to do with security. It shouldn't
affect this part of the problem.

John W. Vinson[MVP]

On Fri, 8 Apr 2005 09:09:06 -0700, HOT FLASH
wrote:

Hi John, I had a thought. Could it be that the null values are affecting the
query? In which case, would I use a NZ() to replace the nulls with a
zero-length string, and if so how exactly. Thank you!

The + operator should be taking care of the NULLS: ("." + [Alpha])
will return NULL if Alpha is NULL.

I'm really perplexed!

John W. Vinson[MVP]

On Fri, 8 Apr 2005 10:17:07 -0700, HOT FLASH
wrote:

Hi John,
When I enter this “DisplayNum: [FirstAlpha] & ("." + [FirstNumber]) & ("." +
[SecondNumber]) & ("." + [SecondAlpha]) & ("." + [ThirdNumber])
Instead of seeing the following SQl text, it compresses the expression and
removes the spaces.

ok... back to basics here...

What version of Access?
Is this a local Access table, or is it linked from some other data
repository (text, Excel, dBase, SQL/Server,...)?
Are you using a .mdb file or a .adp?

John W. Vinson[MVP]