Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2003
    Posts
    66

    Unanswered: Incrementing record number by 1, if number already exists.

    Hi,

    I'm trying to find a way of automatically numbering records (Events, ie Meetings, Presentations etc) based on the date the Event is to take place. The numbering needs to be based on date for purposes of ordering in a Report later.

    User adds in Event details, and Date, and the system creates a number for the Event made out of the date - once the date is input (eg 19/03/2004 ) the Number becomes 19032004. So far so good.

    Problem is that different Events can be on the same date - if I'm adding a new event for 19/03/2004, and an event has already been input with number 19032004 I need to check for this, and increment the new Event's number by 1, as it's input, so as to differentiate it.

    Is this possible? I was thinking of using a select query on the Events table to search for an existing Number that matches the one currently being input - if the query returns a result, increment the value of the record being entered by 1. If the query returns nothing, do nout.

    What expression do I use to check if the select query has returned a result?

    Hope someone can follow this - was more difficult to explain than I expected...

    D

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Logically that wouldnt make a whole lot of sense if you think about it -

    Say you have the ID:

    19032004

    If you increment it by 1, that would indicate a year of 2005 rather than 2004. Why dont you want to give an autonumber PK instead of the date?

    If you gave each event an autonumber, you could still have the event details and date. Just write a simple query, and order the query by the date. This would accomplish the same thing, and save you from writing code.

    The only other way you could do it if you insist on having the date as a PK is to include the timestamp it was created - That way if the date was entered even 2 seconds apart, they would have different values.

  3. #3
    Join Date
    Nov 2003
    Posts
    66
    I know what you mean. I was originally using an autonumber, but when I got to the Reports, I found it's not good enough.

    The Reports need to list the Events in descending date order. However, I have to group the Events by Number in order to associate subreports with each.
    Problem is that Events can be entered into the system retrospectively. User can add an Event that hasn't happened yet - it gets its autonumber 10001. User can also add an event that occurred 3 months ago - it gets it's autonumber 10002. So in the report, an event that occurred in January, can theoretically appear after one that's scheduled for April, 'cos its Autonumber is higher. If I can somehow associate Event Numbers with Dates, at least as a starting point, the Events will always appear in sequence.

    Incrementing 19032004 to 19032005 doesn't really bother me, cos at least such an event will appear before 20032004, and after, say, 17032004.
    Problem is with possible duplicates in a years time, where an Event input on 19032005 that doesn't need incrementing, duplicates one that was entered on 19032004 that did!
    Jesus - I'm even confusing myself now.

    Best solution, I reckon, is to take the date as input, 19/03/2004, and convert it to 190320040 - for duplicates, increment the '0', and so on.
    I dunno how to tack the '0' onto the end of the date string though....

    D

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Incrementing record number by 1, if number already exists.

    Originally posted by daver
    Hi,

    I'm trying to find a way of automatically numbering records (Events, ie Meetings, Presentations etc) based on the date the Event is to take place. The numbering needs to be based on date for purposes of ordering in a Report later.

    User adds in Event details, and Date, and the system creates a number for the Event made out of the date - once the date is input (eg 19/03/2004 ) the Number becomes 19032004. So far so good.

    Problem is that different Events can be on the same date - if I'm adding a new event for 19/03/2004, and an event has already been input with number 19032004 I need to check for this, and increment the new Event's number by 1, as it's input, so as to differentiate it.

    Is this possible? I was thinking of using a select query on the Events table to search for an existing Number that matches the one currently being input - if the query returns a result, increment the value of the record being entered by 1. If the query returns nothing, do nout.

    What expression do I use to check if the select query has returned a result?

    Hope someone can follow this - was more difficult to explain than I expected...

    D
    If you want to keep this code free, you could create another field that is used in combination with the date as the primary key, or at least as an index with the date field. Set the default value of the other field call it DateCount to 1 and set the index property to unique. When you enter the date and the number comes up with one for a date that is already there, Access will let you know that you are trying to create a duplicate value in an index or primary key and you can increment it by one until you have a unique combination of fields.

    It's not the ideal way, and not as automatic as you specified, but if you want Access to do the work, this can do it.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  5. #5
    Join Date
    Nov 2003
    Posts
    66
    I have it kinda doing that already - the Event Number field is set to unique. I have it now that when the user inputs a date, 20/03/2004, the Event Number field populates with the date in reverse sequence, and an extra '0' at the end - 20043200. If there's already such a number, it increments by one. Any further events on the same date, and Access throws up the Duplicate values message, where the user'd have to manually change the number.

    I really need the select query to search on the first 7 digits of the number, find the Max, and then increment THAT by one - that way the user wouldn't have to do anything, and the system would create the number each time. I'm not sure how to write a query that searches on a specific number of characters in a field.

    D

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by daver
    I have it kinda doing that already - the Event Number field is set to unique. I have it now that when the user inputs a date, 20/03/2004, the Event Number field populates with the date in reverse sequence, and an extra '0' at the end - 20043200. If there's already such a number, it increments by one. Any further events on the same date, and Access throws up the Duplicate values message, where the user'd have to manually change the number.

    I really need the select query to search on the first 7 digits of the number, find the Max, and then increment THAT by one - that way the user wouldn't have to do anything, and the system would create the number each time. I'm not sure how to write a query that searches on a specific number of characters in a field.

    D

    Select substr(your_col, 1, 7)
    from your_table

    substr take 3 arguments - the column name you want to search on, the starting character, and the number of digits to go to. So this will start at one, and read 7 characters.

    Take this function and mold it to your requirements

  7. #7
    Join Date
    Nov 2003
    Posts
    66
    Cheers, ss - maybe there's light at the end of this particular tunnel after all..!

    What I'm trying to do with the query is to select ALL instances of Event numbers created for a particular date, ie all those beginning with the same 1st 7 digits, eg 2004320 (2004, march, 20th), irrespective of the extra digit at the end that's tacked on to differentiate them. The select query (I hope) would base the criteria for the query on the first 7 digits of the Event number currently being entered on the form. In theory, I could then make a temp table out of this list of values, select the one with the highest increment (ie 2004320/8) and increment THAT record to 20043209 - thereby creating a new unique number for the event that's still associated with it's date.

    So what I'm hoping for is something similiar to what you've given me, but rather than searching on the first 7 digits in the table, specifying that the search is on the first 7 digits of the number on the form. I'm making no sense at all..!
    Maybe if I paraphrase what I need the SQL to say....

    SELECT (Events.Event_No) INTO event_no_temp
    FROM Events
    WHERE (((Events.Event_No) LIKE??? or HAVING???? 1st 7 digits of [Forms]![AddNewEvent]![Event_No]));

    End of burnt out babble. No worries if you've no clue what I'm shiting on about - I barely do myself at this stage. Time for a beer.

    D

  8. #8
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by daver

    SELECT (Events.Event_No) INTO event_no_temp
    FROM Events
    WHERE (((Events.Event_No) LIKE??? or HAVING???? 1st 7 digits of [Forms]![AddNewEvent]![Event_No]));

    End of burnt out babble. No worries if you've no clue what I'm shiting on about - I barely do myself at this stage. Time for a beer.

    D
    You are going to want something like this: I guess you'd put this in your form - im a lot better with SQL than I am with the form stuff - Substr is a SQL function. I believe you can add the term LEFT to the form part with the same arguments as substr (LEFT is a VB term I believe)

    Code:
    SELECT event.event_no into event_no_temp
    from events
    where substr(event.event_no, 1, 7) = ([Forms]![AddNewEvent]![Event_No])
    I too am kind of tired, but that is the basic form you will want -
    Last edited by ss659; 03-19-04 at 19:23.

Posting Permissions

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