Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Sorting by number (2000)

    My table, tblCourses, lists training courses available in my learning lab. Each course has a number, 1 through 235 (and growing), and we apply the course number to our printed "handouts." I've created a select query to display the handouts by course number. The field "HandoutNbr" is a text field. Therefore, to sort the query in numerical order, those handout "numbers" must display with leading zeros for numbers 1-99.

    My question: Would it be better to format the HandoutNbr field as a number? I remember reading that a "number" that you're not going to use in computations should be formatted as text (e.g., Social Security numbers). I suppose adding those leading zeros is nothing more than an annoyance, but does it really matter whether I format this field as a number or as text? What's the real difference?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting by number (2000)

    In this case, it doesn't matter very much whether HandoutNbr is text or numeric.

    You can still sort on the numeric value while keeping the field as text.
    Add a column
    Val(HandoutNbr)
    to the query, uncheck the Display check box and sort on that column.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Sorting by number (2000)

    I think it is better to keep it as numeric, so I'm going to disagree with Hans on this one, for several reasons. As a general rule, your performance is theoretically better when your primary key is numeric; although it may not be noticable until your database got fairly large. Plus, you will find it easier to work with a numeric field in code and queries; with a text field, you have to worry about putting quotes around the values.

    Your biggest problem, however, will be one of sorting. To sort correctly as text, your numbers must be stored with the leading zeros. Otherwise, 100 will fall before 11. And then what happens when you go to 1000? You have to modify everything below that for another leading zero!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting by number (2000)

    Mark,

    As I pointed out in my previous post, sorting a text field numerically is possible: use Val(textfield) or CLng(textfield). You can do this in queries and also in reports (sorting/grouping window).

    If the HandoutNbr field is part of relations, switching it from text to numeric is going to be a bit of work - all relations must be deleted, the field type must be switched from text to numeric in several tables, the relations must be created anew, and perhaps some queries etc. must be modified.

    If not, switching should be a doddle, and I agree with you that it will make life easier in the long run.

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Sorting by number (2000)

    Hans,

    Yes, it is possible to us CLng() or Val() to convert so your sorts will be correct, but then your performance will really go down the tubes as Access will not be able to rely on its indexes but will have to manually sort each query.

    I think we are in agreement, however, that there is no real need to convert. If it ain't broke......!<g>
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sorting by number (2000)

    Hans and Mark: Thank you both for your insight!

    <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sorting by number (2000)

    Mark and Hans: I considered your advice and chose to format my HandoutNbr field as text. I've been happy with my choice till now. It was a good choice for at least one reason: One (and only one) course has two versions, both on the same CD-ROM. We do not require hourly employees to complete the entire course, but we do require supervisors ("team leaders") to do so. Hourly employees get 2.5 hours' credit, and team leaders get 3.0 hours' credit. Therefore, this one course must appear twice in our system so as to post the correct credit to a student's record. Long ago--long before I started converting our system to Access--we decided to list the hourly employee's version of the course as number 80 and the team leader version as number 80TL.

    Obviously, I can't have a "number" field with alpha characters in it! Thus, formatting as text was the answer. This worked well until I started working on a report where we sort the data by HandoutNbr (almost everything else sorts by course title). Now I'm staring at a report where the numbers appear as "1, 11, 12...2, 21." You know the deal. I tried to change the formatting of the HandoutNbr field to number, but you know all too well the problems I ran into. So I tried Hans's idea and added a column Val(HandoutNbr) to my query, but I hit yet another snag. When I tried to save the modified query, I got an error message: "Extra ) in query expression '[tbl_Courses].[Val([HandoutNbr])]'. So it's telling me I've put an extra right parenthesis mark into the expression...? Not that I can see! If I have a left parenthesis, do I not also need a matching right parenthesis? MS-Access "Help" is characteristically un-Help-ful....

    I'm clueless. Suggestions, anyone? Is there another way to approach this? For example, can you suggest another way to separate these two versions of the same course--something other than 80/80TL? Again, my sincere thanks for your time and help!

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Sorting by number (2000)

    >>error message: "Extra ) in query expression '[tbl_Courses].[Val([HandoutNbr])]'. <<

    If you created a field in the query, it should have a syntax something like this:
    FieldName: Val(HandoutNbr)

    What you used is meaningless.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sorting by number (2000)

    >>What you used is meaningless.<<

    Clearly, I'm missing something here. I think I followed everyone's advice to the letter, to wit: I opened the query in Design view and added a column. In the Field: line, I keyed in precisely this: Val(HandoutNbr). When I dropped down to the Table: line, I selected the table where the HandoutNbr appears. (In fact, that field appears identically in both the tables I use for this query.) When I dropped down to the Table: line, Access changed what I'd keyed into the Field: line to read as follows: Val([HandoutNbr]). Access added the brackets--I did not. Next, I specified ascending order for sorting and unchecked the "Show:" box and clicked on the Save icon. At that point, I got the error message I described in my last post.

    Just in case it's important, I never keyed in anything like the string that appears in the error message: "...query expression '[tbl_Courses].[Val([HandoutNbr])]'."

    Well, methinks, maybe I didn't key in something important, so I started over and entered exactly what you offered in your most recent post, i.e., "FieldName: Val(HandoutNbr)" (minus the quotation marks, natcherly). Again, Access added the brackets and again I got the error message.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting by number (2000)

    Hello Lucas,

    When you enter an expression in the Field: line (instead of just a field name), you should leave the Table: line empty. If the field name in the expression occurs in more than one table, you must refer to the appropriate table as follows

    Field: Val([TableName].[HandoutNbr])

    Replace TableName by the actual name of the table. If the table name doesn't contain spaces, you can omit the square brackets, but Access will add them (as you noted).

  11. #11
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Sorting by number (2000)

    Lucas,

    As soon as you type "Val(HandoutNbr)" (without quotes) in the Field line and then moved off it, Access should have changed it to "Expr1: Val(HandoutNbr)". You can change "Expr1" to a more meaningful expression. DON'T select a table.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sorting by number (2000)

    Thanks, Hans and Mark. Hans, you are correct: Access did add "Expr1" to the Field: line. This time I did as we've discussed earlier, and I did not select a table (left that line blank). I saved the query just fine, and I breathed a sigh of relief. However... when I tried to return to Datasheet view, I got yet another little message: "The specified field '[HandoutNbr]' could refer to more than one table listed in the FROM clause of your SQL statement." Access won't let me outta Design view, so I guess it wants me to specify a table.

    True: this query draws information from only two tables, and both tables contain the field HandoutNbr. Is that the problem? Why is it a problem, and how can I get around it?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting by number (2000)

    Lucas,

    If you read my previous reply carefully, you'll see that I answered your question already. Use

    Val([TableName].[HandoutNbr])

    where TableName should be replaced by the name of the table you want to refer to. Leave the Table: line empty.

    Regards,
    Hans

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    Re: Sorting by number (2000)

    Thank you, Hans. You are exactly right, and I apologize for overlooking that information. I was still focusing on the previous advice about entering "Val(HandoutNbr)" and nothing else. Sometimes all I need is whack on the side of the head! <img src=/S/bash.gif border=0 alt=bash width=35 height=39>

    In the meantime, I thought of another way around the problem I had with that one lonesome course that has a course number containing alpha characters ("80TL"). I added another field to the Courses table: SortNbr, formatted as number/long integer. All the course numbers are, of course, identical to the numbers in the HandoutNbr field, except that 80TL is now 80.1. I added this field to the query, set it to sort ascending and Show: No.

    It works! Thanks again, everybody!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •