Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Posts
    32

    Unanswered: Format statement in Table Design

    I have been asked to look at a client developed application. The original developer is no longer around. Here's what they want to do:

    There is a field in a table that has the following property settings:

    Fieldname: FormTracking AutoNumber
    Fieldsize: Long Integer
    New Values: Increment
    Format: 0"8-"000
    Indexed: Yes (No Duplicates)

    The values in the field look like this:

    08-001
    08-002
    08-003 etc

    The '08' is supposed to represent the year that the record was added.
    The client is preparing for the new year and they've asked me to make the appropriate changes such that NEW records will have values like 09-001, 09-002 etc.

    I thought if I changed the Format property to 0"9-"000 that would do it but that changed the values in the EXISTING records.

    Is there an easy way to keep the values in the existing records as is and only have new records get the new format statement?

    I'm not prepared at this point to reverse engineer this application. This table and primary key value is used throughout the application. Records could be added from multiple forms.

    Thanks in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if they use a date as part of the row then format the year bit from that date. if there year coding is based on a calendar year that should be easy enough, if its based on financial years it may be a bit harder.

    change the existing values to reflect that.....
    run from new with the 09 prepended either when they key in the data or suggest it, put some code in that validates the number entered is always prepended by yy-

    out of curiosity what do they do with 07 or earlier values?

    the whole saounds like a lashup, I think you may have to open up the forms and reports and correct this particluar problem

    the other technique may be to dump this years values into another table...
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2005
    Posts
    32
    I actually was trying to change the format statement in the table field value to be something like this so it wouldn't have to be changed from year to year:

    Instead of: 0"8-"000
    Change to: format(Date(),"yy")"="000

    But then I realized that I would still have the original problem, that being that all of the existing values in the records get changed as soon as the format statement is modified. So even if I could get the syntax right (which I wasn't able to do), I'd still have the problem.

    It looks like I'll either have to archive the 08 records in its own table and change the format statement to 0"9-"000 in the "new empty table" (realizing this will an ongoing thing everytime the year rolls over) or do this through code behind the form. If I create a separate table then I'll need to modify forms and queries to reference the different tables.

    The application was developed sometime this year so there are no 07 records.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so how are the numbers created, does it start at 0 each year?
    changing the format affect how information is presented not stored
    so applying this change as a fromat mask will not do what you want, short of clearing out the exsiting table

    I think you are going to have to make changes to the application, unless you can clear out all data for the previous year.

    Mebbe the fear of making changes is more significant than the cost of doing it. Id scope the project and go back to the customer and offer them the options as you see it

    its quite possible the original developer recognises this and is keeping their head very very low.. Ive come across that more than once.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2005
    Posts
    32
    Thanks for your replies. I appreciate them!
    I've done a bit more digging and you are right when you say that the format only affects the display, not the data that is stored. I did not realize that. Not only did the developer put a format statement in the table, she also did it on all of the forms that reference that field. But, the actual underlying data only stores the "auto number".

    Now that I know this, I'll go back to the customer to see if they always want to start out the new year with a tracking number of 1. If that's the case, then I'll need to separate the data into different tables (assuming we still use an auto number). If they don't care what the tracking number starts with but are only concerned that the current 2 digit year prefaces the number then I can programmatically preface the number with the year for display and reporting purposes.

    Thanks again!

Posting Permissions

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