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 in appending a sequential number to a date field

Discussion in 'Business Applications' started by NEWBIE57, Jan 18, 2011.

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

    NEWBIE57 Thread Starter

    Joined:
    Jan 18, 2011
    Messages:
    65
    I am trying to add a sequential number to a date field in an Access form.
    The format of the field is PS 18 Jan 11 xx, the PS is fixed, the current date and the xx is my sequential number I want to add.

    Is there a way, to do this when the date is entered? And I would need the sequential number to reset to 01 with a new day.

    I'm new to this, any help would be great!

    Thanks!
     
  2. Sponsor

  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,493
    NEWBIE57, welcome to the Forum.
    This requires a Query to find the last Number for the Current Date (if it returns no records it resets to 01) and some VBA code that I can supply for the After Update event of the Date field.
    So how do you currently populate the field?
     
  4. NEWBIE57

    NEWBIE57 Thread Starter

    Joined:
    Jan 18, 2011
    Messages:
    65
    thanks for your quick response!

    A date is inputted in the form and from there I want to add the PS in front of the date and the sequential number behind the date.

    Does that make sense?
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,493
  6. NEWBIE57

    NEWBIE57 Thread Starter

    Joined:
    Jan 18, 2011
    Messages:
    65
    Thank you!
     
  7. NEWBIE57

    NEWBIE57 Thread Starter

    Joined:
    Jan 18, 2011
    Messages:
    65
    Ok, in looking at the code (really don't have toomuch idea what I'm looking at...) but this brings in my date field, and will look at the day part of the week? And if it changes, then the sequential number starts over at 01? Do I need to store my date field in a query by itself? Sorry for the confusion, but kind of understand, but need more help.
    Thanks so much for your attention to this, appreciate!
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,493
    If you can tell me the exact names of your table and the field where you want the sequential number I can create the Query & VBA for you like that database.
     
  9. NEWBIE57

    NEWBIE57 Thread Starter

    Joined:
    Jan 18, 2011
    Messages:
    65
    Fantastic! Sorry I know nothing to do with coding.... just a little Access!
    Ok, field name where I want it to go is KTIfile#, table is KTIOrders.

    Thanks millions!
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,493
    What is the first field on your form where you enter actual data?
    I need that put the code in.
     
  11. NEWBIE57

    NEWBIE57 Thread Starter

    Joined:
    Jan 18, 2011
    Messages:
    65
    The first field is EFEtable, a text field.
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,493
    Have a look at this database, I think it is doing what you want.
    You need to unzip it first.
     

    Attached Files:

  13. NEWBIE57

    NEWBIE57 Thread Starter

    Joined:
    Jan 18, 2011
    Messages:
    65
    ok, having some problems, but it's my fault. The field KTIfile# is the primary key (didn't tell you that), so when I enter the same date more than once, of course it won't take. And also, I want the field KTIfile# to have a PG at the beginning. So it would be PG11811xx, PG, the date 11811 and xx is the sequential number.

    Thanks for your help!
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,493
    I can do that, but 11811 should be 011811.
    As the KTIfile# has an incremented number on the end you should not be able to get duplicates?
     
  15. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,493
    Try this one instead.
     

    Attached Files:

  16. NEWBIE57

    NEWBIE57 Thread Starter

    Joined:
    Jan 18, 2011
    Messages:
    65
    I agree on the date, it should be 011811. But I didn't get the code to work. I have the KTIorderquery in place and added the code for the afterupdate on the EFEtable field.

    Not sure what to do?
     
  17. 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/975443