Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2015
    Posts
    3

    Unanswered: sequence numbers?

    I need to automatically generate a 5-character value for my Business Key. Without any user interaction.

    2 character -> from 01,02,03,04 to 05
    +
    3 character -> Sequential Number (001,002,003).

    The Sequential Number must reset on each new day.


    This is my Example:
    ComboBox= 01 or 02 or 03 or 04 or 05

    if ComboBox= 01 and MyDate = 4/6/2015
    MyTable:

    PK Castumer_Number Seq_Number
    1 1 01-001
    2 2 01-002
    3 3 01-003

    if ComboBox= 02 and MyDate = 4/6/2015
    MyTable:

    PK Castumer_Number Seq_Number
    1 4 02-001
    2 5 02-002
    3 6 02-003

    if ComboBox= 01 and MyDate = 4/6/2015
    MyTable:

    PK Castumer_Number Seq_Number
    1 7 01-004
    2 8 01-005
    3 9 01-006

    and The Sequential Number must reset on each new day.

    I hope it's clear now
    thanks

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    CUSTOMER# SHOULD HAVE ITS OWN FIELD
    (if not , you should create one, from the SEQ#)

    get the combo#
    Get the max Seq# for that day


    Code:
    dim vID, vCust, vDate, vNewNum, vMAx
    dim iSeq as integer
    
    vCust = cboBox
    vDate = date()
    
      'get the highest value of the 1 customer on file for that day
    vMax = Dmax("[Castumer_number]", "table", "[cust]=" & vCust & " and [date] = #" & vDate & "#"
    
    vVal = right(vMax,3)
    iSeq = vVal + 1
    
    vNewNum = vCust & "-" & format(iSeq,"000")   'this is the next Seq#

  3. #3
    Join Date
    Apr 2015
    Posts
    3

    sequence numbers?

    This Is My Order:

    Click image for larger version. 

Name:	Untitled.png 
Views:	2 
Size:	26.9 KB 
ID:	16289
    Attached Files Attached Files

Posting Permissions

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