Using Alpha/Numerics

[HOT FLASH]

I have a form I want to use in Access. It has a numbering system in this
format "A.1.2.a.1" If the property box is set to a text field, it sorts .12
before .2, which is not satisfactory. I have entered the number in separate
fields, the first being "A", the second ".1" the third ".2" the forth ".a"
and the last field ".1". What do I have to do to display, print & sort these
separate fields as one grouped entity?

Associated with each number is a description. Currently, I have the
description on the same record in an adjacent field defined as a memo
property. Am I on the right track? Do I need to do anything to ensure that
each description remains associated with its number?

Lastly, I have a header description which is only used for each appearance
of A", "B", "C", and not on any of the subsets. It is defined as 255 text
characters. As it is used only once every 50 records (approximate & varying)
do I need to do anything differently with this field than I do with the other
description field which is appears on every record?
Your help will be greatly appreciated!

 

[Graham Mandeno]

Hi Hot Flash

Answers inline...

I have a form I want to use in Access. It has a numbering system in this
format "A.1.2.a.1" If the property box is set to a text field, it sorts
.12
before .2, which is not satisfactory. I have entered the number in
separate
fields, the first being "A", the second ".1" the third ".2" the forth ".a"
and the last field ".1". What do I have to do to display, print & sort
these
separate fields as one grouped entity?

You are on the right track using separate fields for each numbering level.
However, I woulkd leave out the dots and make the fields for the numeric
levels type "Integer" (or perhaps "Byte"). That way they will sort
correctly in numerical order.

It's easy to string them all together for display purposes. Just create a
query with a calculated field:
DisplayNum: [Num1] & "." & [Num2] & "." & [Num3] & "." & [Num4] & "." &
[Num5]

Don't sort on the calculated field, but on the individual level fields,
otherwise you will be back to square one.

Associated with each number is a description. Currently, I have the
description on the same record in an adjacent field defined as a memo
property. Am I on the right track? Do I need to do anything to ensure
that
each description remains associated with its number?

Just include the memo field in your query and it will stay with the correct
record.

Lastly, I have a header description which is only used for each appearance
of A", "B", "C", and not on any of the subsets. It is defined as 255 text
characters. As it is used only once every 50 records (approximate &
varying)
do I need to do anything differently with this field than I do with the
other
description field which is appears on every record?

Do you mean that you have a record numbered just "A", with no sub-numbers?

In this case, modify your DisplayNum expression as follows:

DisplayNum: [Num1] & ("." + [Num2]) & ("." + [Num3]) & ("." + [Num4]) &
("." + [Num5])

This uses the fact that:
"some string" & Null gives "some string"
while:
"some string" + Null gives Null

So the dots will be omitted if they are not required, and you can have
records numbered simply "A" or "B.3".

 

[HOT FLASH]

Thank you Graham!
Actually I used table format properties of "." @ as well as < and > in the
obvious Fields which appears to work just fine. Will this change anything you
told me? Every numeric is defined as byte, and the text fields are limited to
2 or something equally small.
G'Day!

Hi Hot Flash

Answers inline...

I have a form I want to use in Access. It has a numbering system in this
format "A.1.2.a.1" If the property box is set to a text field, it sorts
.12
before .2, which is not satisfactory. I have entered the number in
separate
fields, the first being "A", the second ".1" the third ".2" the forth ".a"
and the last field ".1". What do I have to do to display, print & sort
these
separate fields as one grouped entity?

You are on the right track using separate fields for each numbering level.
However, I woulkd leave out the dots and make the fields for the numeric
levels type "Integer" (or perhaps "Byte"). That way they will sort
correctly in numerical order.

It's easy to string them all together for display purposes. Just create a
query with a calculated field:
DisplayNum: [Num1] & "." & [Num2] & "." & [Num3] & "." & [Num4] & "." &
[Num5]

Don't sort on the calculated field, but on the individual level fields,
otherwise you will be back to square one.

Associated with each number is a description. Currently, I have the
description on the same record in an adjacent field defined as a memo
property. Am I on the right track? Do I need to do anything to ensure
that
each description remains associated with its number?

Just include the memo field in your query and it will stay with the correct
record.

Lastly, I have a header description which is only used for each appearance
of A", "B", "C", and not on any of the subsets. It is defined as 255 text
characters. As it is used only once every 50 records (approximate &
varying)
do I need to do anything differently with this field than I do with the
other
description field which is appears on every record?

Do you mean that you have a record numbered just "A", with no sub-numbers?

In this case, modify your DisplayNum expression as follows:

DisplayNum: [Num1] & ("." + [Num2]) & ("." + [Num3]) & ("." + [Num4]) &
("." + [Num5])

This uses the fact that:
"some string" & Null gives "some string"
while:
"some string" + Null gives Null

So the dots will be omitted if they are not required, and you can have
records numbered simply "A" or "B.3".

Your help will be greatly appreciated!

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand