If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Using Alpha/Numerics
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! |
#2
|
|||
|
|||
Hi Hot Flash
Answers inline... "HOT FLASH" wrote in message ... 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 |
#3
|
|||
|
|||
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! "Graham Mandeno" wrote: Hi Hot Flash Answers inline... "HOT FLASH" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|