Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2003
    Location
    Ohio
    Posts
    1

    Red face Unanswered: Problem with field format (input mask)

    Hello all.
    It seems like this problem would be fairly simple to fix, but it has been driving me crazy for over a week now.
    I recently took over managing an Access database at work. It was designed simple as its purpose is to log and track certain incidents in daily operations. Each incident is given an unique identifier. The format mask is: &&&"-K03";;
    The problem is, the year is half over and we're approaching # 1000 real quick. I've tried to change the format to allow the fourth digit, but the field is a text data type and it sorts as text (so that 1000 immediately follows 100). I've tried to manually enter a leading "0" as a placeholder, but the relationships won't allow that.
    I would really appreciate any advice on this one. I'm ready to just build a separate database!
    Thanks much!

  2. #2
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Personally I would handle it this way:

    one field - being an autonumber (incID)

    I would then have an unbound control (txtIncID) on the form
    when a user adds an incident I would

    txtIncID = incID & "K03"

    that will display the incidents as you need them and allow you to sort accordingly.

  3. #3
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133

    Wink Re: Problem with field format (input mask)

    Originally posted by MonkeyGirl
    Hello all.
    It seems like this problem would be fairly simple to fix, but it has been driving me crazy for over a week now.
    I recently took over managing an Access database at work. It was designed simple as its purpose is to log and track certain incidents in daily operations. Each incident is given an unique identifier. The format mask is: &&&"-K03";;
    The problem is, the year is half over and we're approaching # 1000 real quick. I've tried to change the format to allow the fourth digit, but the field is a text data type and it sorts as text (so that 1000 immediately follows 100). I've tried to manually enter a leading "0" as a placeholder, but the relationships won't allow that.
    I would really appreciate any advice on this one. I'm ready to just build a separate database!
    Thanks much!
    Hi Monkey(girl),

    It should be possible to add the leading zero : just make sure you change existing values at the 1-side of the relationship and your relationship should allow cascade-updates.
    Like this, you can add the fourth digit and continue working with the database. But next time you make such a database, use an incrementing autonumber instead ...

    greetings,
    Her(man)

  4. #4
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    Leading zeros do not exist for integers

    This is acces not excel

    If a control excepts a leading zero it is either text or when you lose focus of that control it will drop the leading zero

    concantonate an integer with the text in a seperate control

  5. #5
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133

    Wink

    Originally posted by axsprog
    Leading zeros do not exist for integers

    This is acces not excel

    If a control excepts a leading zero it is either text or when you lose focus of that control it will drop the leading zero

    concantonate an integer with the text in a seperate control
    If I can still read correctly, we are talking here about a text field.....

  6. #6
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    you do read correctly - but remember our assistance is not for each other it for the one who posted - I did not want monkey girl to assume that the leading zeros would indicate that the field would somehow be "converted" to numeric and therefore have the potential of being sorted that way.

    I was not trying to be condescending to Herman - he always has good solutions

    bottom line - both solutions are just that - viable solutions that will work.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If its a text field and the mask accepts text, why not start using alpha-numeric values?

    .
    .
    .
    997
    998
    999
    A00
    A01
    A02
    .
    .
    .
    etc...

    should sort correctly.

  8. #8
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    because 333 will sort befor 22 in a text field

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I was under the impression that it is already a text field, so isn't it already sorting like this?

    If not, I agree with Herman that it should be possible to add the leading zero and cascade the updates to maintain relational integrity.

    blindman

Posting Permissions

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