1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

HELP! Access 2007 expression for incremental number

Discussion in 'Business Applications' started by lenestopage, Aug 7, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. lenestopage

    lenestopage Thread Starter

    Joined:
    Aug 7, 2012
    Messages:
    7
    I have an access db with a table (Table A). I have a date field (Date - Present) and a Integer field (Sequence). I am trying to append a number to the end of my date in the following format: 20120807-01 and have it reset back to 01 daily.

    I was able to get this: 20120807-1 but the end number just keeps going it doesn't reset to 1 the next day.

    This is the expression I have so far: =Format([Date - Present],"yyyymmdd" & "-" & [Sequence]

    Any suggestions to make this expression reset to 1 daily or does anyone have any other expressions that might work?

    Thanks...I have also searched high and low and found a few possible solutions but none of them have worked thus far.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    lenestopage, welcome to the Forum.
    You have found the limitation of an Expression or Calculated field.
    I am not sure how you are Incrementing the "Sequence" field, possibly as an Autonumber.
    The control over the Sequence field is the key to what you want to do.
    So you have to use VBA to do so, either using a VBA Recordset or a Dlookup function to establish whether or not the Current date - present is the same as the "last" date - present record in the table.
    Can you describe the table structure or show a screen print of it?
     
  3. lenestopage

    lenestopage Thread Starter

    Joined:
    Aug 7, 2012
    Messages:
    7
    I have attached some screens of my database. Unfortunately I am a very inexperienced with VB, any help would be greatly appreciated.

    The Sequence field I spoke of earlier is represented as Catalog Id
    Date - Present is represented as Present Date
    Table A is represented as Table1

    Thanks again for any and all help!
     

    Attached Files:

  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    So you want the value to go in the AutoCatalog ID?
     
  5. lenestopage

    lenestopage Thread Starter

    Joined:
    Aug 7, 2012
    Messages:
    7
    the catalog Id field if possible - the auto catalog field was created when I was trying different expressions and ideas.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Are you using a normal data entry form to enter the new records or a special "only new records" form?
     
  7. lenestopage

    lenestopage Thread Starter

    Joined:
    Aug 7, 2012
    Messages:
    7
    just a normal data entry form
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Do you know how to find the After Update Event Procedure of the "Date Received" field?
     
  9. lenestopage

    lenestopage Thread Starter

    Joined:
    Aug 7, 2012
    Messages:
    7
    yes I just don't know what to put in there :)
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    OK, we will start off slowly, the code below expects to find at least one record already in the table and showing on the form.
    Add this to the actual VBA Event of the "Date Received" field in the VBA Editor (select Event Procedure and then click the 3 small dots in the after update event)

    Dim rs As Object
    On Error GoTo errorcatch
    if not me.newrecord then exit sub
    Set rs = Me.Recordset.Clone
    rs.movelast
    msgbox rs.[Present Date]

    rs.close
    set rs = nothing

    exit sub
    errorcatch:
    MsgBox Err.Description

    At the moment all this code should do is when you have a new record and enter the "Date Received" value and enter or tab you should get a Message telling you the date of the previous record (providing the records are in date order)
     
  11. lenestopage

    lenestopage Thread Starter

    Joined:
    Aug 7, 2012
    Messages:
    7
    I have done that so far and Im getting the message box.
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Does it give you the correct date?
     
  13. lenestopage

    lenestopage Thread Starter

    Joined:
    Aug 7, 2012
    Messages:
    7
    yes it gives me the last date of the previous input date
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    OK, next step, we need the number as well so add this to the message box

    Replace this line

    msgbox rs.[Present Date]

    with

    msgbox rs.[Catalog Id] & " - " & rs.[Present Date] & " - " & me.[Present Date]

    That should show you the previous CatalogId & Date and the present date from the current record.
    After replacing the line of code add the following lines

    if rs.[Present Date] = me.[Present Date] then
    me.[Catalog Id] = format(me.[Present Date], "yyyymmdd"
    else
    me.[Catalog Id] = format(me.[Present Date], "yyyymmdd"
    end if

    Now I can't add the number to the id yet because we haven't discussed how many records per day you are likely to get, if it is less than 100 we should use
    01 - 99
    if it is more than 100 we should use
    001 - 999

    So what value is currently in your last record, the reason we need this is to identify how many characters we need to cout out to get just the number.
     
  15. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1064174