Still Stuck on Alpha Numerics

[Guest]

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!

 

[John Vinson]

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]

 

[Guest]

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)

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]

 

[John Vinson]

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]

 

[Guest]

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;

 

[Guest]

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]

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]

 

[Guest]

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!

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]

 

[John Vinson]

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]

 

[John Vinson]

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]

 

[Guest]

Hi John,
The MS Access version is Office Pro2003, and I have an XP. I am building
this myself, so I have not used any code or structures from any other project.

The database is stand alone. My computer is not linked either. So far I
have created 3 tables, and one small form to enter user names and
information, and this query to try to group the 5 fields associated with the
numbering system I must use, and be able to sort numerically. Does this
help? Thanks for helping me slog through this.

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]

 

[Guest]

Hi Ya’ll,
I have been trying different scenarios, and here is what I have come up with.
1) No matter if it is a SELECT, PARMETIZED, UNION or whatever type of query,
the expression does not work as long as the “.†portion of the expression is
included.
2) I already have 1000’s of records. The most that any one field will
contain is:
FirstAlpha = 1, FirstNumber=2, SecondNumber=3; SecondAlpha=1, ThirdAlpha – 2
This make creating an input mask hard, as it will insert the “.†When there
is a null, or in the case of SecondNumber=3, it will insert a “>†after the
leading left number and before the middle number.
Try as I might, I cannot find any SQL formatting instructions. If you enter
the expression I was given without the “.â€, all of the capitalizations are
reversed.
I have to turn this puppy in today. I do hope you can help me to 1) figure
out the appropriate type of query, and the expression that will ultimately
express the 5 concated (maybe grouped, aggragated?) fields into the proper
format and which will allow sorting as a numeric.
Again, I appreciate all your good thoughts.

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]