Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Apr 2001
    Location
    Oklahoma City, Oklahoma, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting Problem (A2KSR1)

    I use a field to designate the location of inmates in a county jail. The housing is all in the form of ##(letter)## such as 12B15. Whenever someone updates or adds a record they RARELY include the leading zero. This messes up my sorting when I go to print out the different reports based on housing location. All housing would, ideally, be sorted by floor, 02/04/06/08/10/12 etc. (Only even floors have housing units). What I get now is 10/12/2/4/6/8.

    How can I set up a format so the leading zero is inserted automatically? <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

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

    Re: Formatting Problem (A2KSR1)

    Just setting a format won't change the stored value, so it wouldn't help in sorting.

    You could set the Input Mask property of the field to >00L00. This means the user *must* enter 2 digits followed by a letter and 2 digits; the letter is converted to upper case.

    Another approach is to use a query as record source for the reports. The query can be sorted on Val([Location]) and then on Location itself.

    Yet another idea: correct data entry using the AfterUpdate event of the text box on the data entry form:

    If Left$([Location], 1) <> "0" Then
    [Location] = "0" & [Location]
    End If

  3. #3
    Star Lounger
    Join Date
    Oct 2001
    Location
    Western Massachusetts, USA
    Posts
    99
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Problem (A2KSR1)

    Your fields are set up as text and not numbers so that is why you get the sort you do. If you just want to sort the fields ascending by value, create a query and in a new column of the query add an expression
    MySort:Format(LocationField, "00") and then sort this field ascending. That should give you the leading 0 and allow you to sort your reports properly.
    Paul

  4. #4
    Star Lounger
    Join Date
    Apr 2001
    Location
    Oklahoma City, Oklahoma, USA
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formatting Problem (A2KSR1)

    Thanks, I tried it and it worked great. I used the after update idea.
    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

Posting Permissions

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