Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2004

    Unanswered: tricky text formatting issue

    I have a tricky formatting issue
    the allowed data in a text field has to be in one of the formats below

    v99.9 , 999.9 v99, v99.99, 999, 999.99

    the 9's represent numbers

    the allowable data may be any combination of nuk=mbers


    v21.3 or 213.4 or v46 or v34.56 etc....

    79.90 does not work...

    any help is appreciated


  2. #2
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    79.90 doesn't work because it has two digits before and after the decimal, is that correct?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Aug 2004
    79.90 is not an acceptable entry - because it is not in one of the formats listed
    and yes - because two digits before and after the decimal (differing from the allowed formats)

    v is not a number - correct - it is an allowable entry v21.3
    always will be the letter "v" if entered

    I explained it pretty well??


  4. #4
    Join Date
    Aug 2009
    Up Nort' Wi
    I'm not sure if you're looking for code or just a suggestion, here's how I would probably do it, if you need help with coding a quick and dirty module to check it let me know.

    I have to do something similar to verify certain in-house barcodes. Basically you will want to check Left(string,1) and then run a loop through the rest (I generally use mid()) to check if the text is Like ("#").
    If you want to find out how many digits are before and after the decimal you can set up a couple/few variables: beginLen (before the decimal), endLen (after the decimal), and a boolean to let it know when the decimal was found.
    If you aren't worried about how many numerals are before/after then strip off the "v" and try converting what's left to a single (and be prepared to handle the error it will kick up if it can't convert it)...or just use the loop to make sure there's no text characters in there.

    Sam, hope this is what you were looking for
    Good, fast, cheap...Pick 2.

  5. #5
    Join Date
    Feb 2004
    One Flump in One Place
    Data integrity checks should be as close to the data as possible, using declarative DML SQL (ok - so you don't really see that you are using SQL with the Access GUI but under the hood you are. You can actually make these changes using SQL but it's pretty rare with JET\ ACE). There is no way then for people to bypass your validation and the rules are less likely to be lost if porting to another RDBMS.

    Enter this into the Validation Rule property for the column:
    Like "V##.##" Or Like "V##.#"
    In other RDBMSs this is more commonly known as a "Check Constraint". As usual, Access uses its own names.
    I'm sure you can extend that to the rest of your valid formats.
    pootle flump
    ur codings are working excelent.

Posting Permissions

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