Results 1 to 8 of 8

Thread: Combine Fields

  1. #1
    Join Date
    Jun 2013
    Posts
    4

    Unanswered: Combine Fields

    Hello Forums (First Time posting)

    Name is Bill and I have a question in regards to generating an sequential number.

    I have a database and need to generate a new number when any customer orders new equipment. The number consist of the following.

    Julian Date (12345)(this is a user input field) and serial number (2001) these are combined to generate a workorder number.

    Now everyday the serial number restarts at 2001. So i will need it to reset everyday. In addition the next customer is 2002,2003 and so on.

    I have a database that has this currently working but can not figure out the code. If you need the code I can provide it.

    Thank you in advance for all your support.

    Bill

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    In your forms Before Update Event you can create your workorder number:
    Code:
    dim GetCnt as Integer
    If Me.NewRecord Then
    GetCnt = 2001 + Dcount("*", "tblName","[fldPurchaseDate]=date())
    Me.Workorder = Me.Julian & GetCnt
    End If

  3. #3
    Join Date
    Jun 2013
    Posts
    4
    Thank you
    I have placed this in script and have a Compile Erro Syntax error.
    GetCnt = 2001 + Dcount("*", "issues","[current date]=date()")

  4. #4
    Join Date
    Jan 2005
    Posts
    146
    Missing closing quote:

    GetCnt = 2001 + Dcount("*", "issues","[current date]=date()")

  5. #5
    Join Date
    Jun 2013
    Posts
    4
    is there a way to display the GetCnt in a field/Text Box?

  6. #6
    Join Date
    Jan 2005
    Posts
    146
    Instead of declaring the variable in VBA, add an unbound control named GetCnt to your form and change the code to:

    Code:
    If Me.NewRecord Then
    Me.GetCnt = 2001 + Dcount("*", "tblName","[fldPurchaseDate]=date())
    Me.Workorder = Me.Julian & Me.GetCnt
    End If

  7. #7
    Join Date
    Jun 2013
    Posts
    4
    Perfect got it working thank you so much for all the knowledge.

  8. #8
    Join Date
    Jan 2005
    Posts
    146
    Your welcome. Good luck with your project.

Posting Permissions

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