Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1

    Exclamation Unanswered: Problem With a Query - How do I Select Records Which Have A Particular Format???

    Hi!

    I am the administrator for a database which currently contains 18,000 records and has about 7,000 added each year. We are currently doing an audit on the records and want to find which records have File Numbers (the FileNo column) which are NOT in the format XXXX/xxxxxx, where XXXX is the year and xxxxxx is the file number for that year e.g 2003/000001.

    Since taking over the database in September, I have put an input mask on the input form to prevent further incorrect file numbers from being entered.

    I am wondering, is there a way of querying the format of a record as opposed to the value of the record???

    Thanks,

    Grant

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb Re: Problem With a Query - How do I Select Records Which Have A Particular Format???

    Originally posted by grantwilliams
    Hi!

    I am the administrator for a database which currently contains 18,000 records and has about 7,000 added each year. We are currently doing an audit on the records and want to find which records have File Numbers (the FileNo column) which are NOT in the format XXXX/xxxxxx, where XXXX is the year and xxxxxx is the file number for that year e.g 2003/000001.

    Since taking over the database in September, I have put an input mask on the input form to prevent further incorrect file numbers from being entered.

    I am wondering, is there a way of querying the format of a record as opposed to the value of the record???

    Thanks,

    Grant
    Try to make a RecordSet then go through it re3cord for record examminintg the following:
    1. How many Char. are in that field
    2. Whether there is a Slash in the fifth postion
    3. Hpw many Char after that slash?

    According to the result of these questions you can make the changes to suite your desire.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    \Try to make a RecordSet then go through it re3cord for record examminintg the following:
    1. How many Char. are in that field
    2. Whether there is a Slash in the fifth postion
    3. Hpw many Char after that slash?

    According to the result of these questions you can make the changes to suite your desire.
    Well said. Here's a very quick start on how to do that:

    SELECT yrField.yrTable
    WHERE LEN(yrField.yrTable) <> 11 OR MID(yrField.yrTable, 5, 1) <> "/"

    Basically you're checking for the total field length, and a slash in the 5'th character. If either of those conditions are false, then it will be returned in this recordset.

    Alternately, if you choose to do this with vb, I would write an error handling event.

    Essentially taking the left 4 characters and trying to cast them to an integer, if it fails, then it's flagged. Then checking the 5th character for "/", again flagging if it throws an exception, then trying to cast the final 5 characters to an int.
    Last edited by Teddy; 12-15-03 at 12:04.

Posting Permissions

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