Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Auto Control Number (2002/SP2)

    Our plant runs 24/7 and each shift files a shift report. They have been doing this using e-mail, with each supervisor "free forming" their report. We want to stanardize this, and put the information into a database. I would like to create a control number that would consist of the date and shift, something like 032105_1, to indicate that it was on March 21, 2005 and for the first shift. Can I set it up so that when a new record is created, the date is entered automatically and the supervisor enters the shift number, then the Control number is created? Anyone ever do anything like this? Thanks for your help.

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

    Re: Auto Control Number (2002/SP2)

    Why not use two fields - a date field that has Date() as default value, and a number field for the shift? You can create a unique index on the combination of the two fields. For printing purposes, you can concatenate the fields.

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Auto Control Number (2002/SP2)

    I agree with Hans, it would be better to have just the 2 fields (ShiftDate and ShiftNo) and forget about the concatenated field. You can either make these 2 fields the PrimaryKey, or just make an Index containing the 2 fields and make it unique (and use an autonumber for PK).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Auto Control Number (2002/SP2)

    I never even thought of that but it works perfectly. I have always thought in terms of one master number. Combining is a great idea. Thank you so much.

    Now, one little problem. I put a combo box in the header of my form to search for the form that I need. I used the wizard and had it create it, I put in the two fields, date and shift and when I use the combo box, it finds just what I need. Here is the problem, I assumed that I could start typing the date, and I have it in Short Date format, and it would narrow down to the one I am looking for. In other words, I would like to be able to start typing 03 and have it go to march, then say 23, to go to the day, then I would see the three shifts for that day and I could click on the one that I want. It doesn't work that way and eventually, the list is going to get very long. Is there a different format that I should be using? Looking up a report by date would seem to be the most logical approach. Thanks for your help.

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

    Re: Auto Control Number (2002/SP2)

    The combo box wizard creates code that searches for a record when select an item in the list, or type something and then press Enter or Tab. It doesn't search or filter as you type. I don't think that would be desirable - say you are looking for the record for 12/21/04. You'd type 1 and jump to records for January, then type 2 and jump to December; type 2 again and jump to records for December 2, type 1 and jump to records for December 21. It would make me dizzy.
    By the way, Access does try to complete the date I'm entering in a combo box from the dates in the list.

Posting Permissions

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