Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2006
    Posts
    11

    Red face Unanswered: How to reset the counter if the dates does not match?

    Hello Anyone,

    I know this question may sound silly to some but I need advice or guidance in how to do this.

    I am trying to create a method so that it can be used while creating new purchase orders. See below my example:

    There are two tables (Temp_POCounter and Temp_PurchaseOrder). The function of Temp_POCounter is to count the # of times a purchase order was created and give the current date. The function of Temp_PurchaseOrder is to capture all of the information in order to create a purchase order using the information from Temp_POCounter to create the Purchase Order #.

    The link between the two tables is a field called PO_Count.

    The business rule for creating a new purchase order is if the current date (from Temp_PurchaseOrder) does not match the last given date (from Temp_POCounter), then the table should be cleared out and the count should restart back to '1' and post the new date.

    eg.

    Temp_POCounter Table
    PO_Count PO_CountDate
    1.0 10/19/2011
    2.0 10/19/2011

    Temp_PurchaseOrder Table
    PO_OrderNO PO_Count PO_OrderDate
    101901-11 1.0 10/19/2011
    101902-11 1.0 10/19/2011


    If you are entering a new purchase order today, the table will be cleared and the new record should look like:
    Temp_POCounter Table
    PO_Count PO_CountDate
    1.0 10/20/2011

    Temp_PurchaseOrder Table
    PO_OrderNO PO_Count PO_OrderDate
    102001-11 1.0 10/20/2011

    Can anyone help me?

    I really appreciate this. Thank you.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You do not supply the definitions of both tables, which is important for being able to help you.
    Have a nice day!

  3. #3
    Join Date
    Mar 2006
    Posts
    11

    How to reset the counter if the dates does not match?

    Quote Originally Posted by Sinndho View Post
    You do not supply the definitions of both tables, which is important for being able to help you.
    ----------------------------------
    Hi Sinndho,

    To make it clearer:

    Both tables are ms access database tables and they are defined by:

    Temp_POCounter's primary function is to give the end user a count of how many POs were created in a day. When the end user go to the add new purchase order form, the process should review this table first and check for the last date given. If the date matches the current date, then we should add '1' to PO_Count field. Then we will use that value and include it in the actual Purchase Order # field (Temp_PurchaseOrder table). If the date does not match the current date, then we wish to clear the records and restart at '1' and insert the new date.

    To summarize this - the table will constantly be removing records and starting over each day.

    Temp_PurchaseOrder's primary function is to gather information from the end user in order to create new purchase order(s). The key field (PO_OrderNo) is created by taking the current date and the PO_Count field (from Temp_POCounter). The format is "mmdd[PO_Count]-yy". So this table relies on Temp_POCounter table in creating the Order No.

    I hope this helps.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I was not asking for a functional description of the tables but for their actual definition: Column names, data types, indexes, constraints.
    Have a nice day!

  5. #5
    Join Date
    Mar 2006
    Posts
    11

    How to reset counter if the dates does not match

    Quote Originally Posted by Sinndho View Post
    I was not asking for a functional description of the tables but for their actual definition: Column names, data types, indexes, constraints.
    -------------------------
    Sorry about that Sinndho.

    Temp_POCounter
    PO_Count Number Indexed (yes, includes dups)
    PO_CountDate Date/Time (Short Date)

    Temp_Purchase Order (it is alot of fields, just going to give you a gist of it)
    PO_Order_No Text Indexed (yes, no dups)
    PO_Count Number
    PO_Order_Date Date/Time (Short Date)
    Vendor Name Text (lookup to ROR.Vendor table)

    I hope this helps.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a solution. I'm not sure about how to handle the table Temp_PurchaseOrder and how to compute the value for PO_OrderNO, though. You should be able to adapt the function easily.
    Code:
    Function CreateNewPurchaseOrder(ByVal PODate As Date)
    
        Dim strSQL As String
        Dim strNewPONumber As String
        
        If PODate > Nz(DMax("PO_CountDate", "Temp_POCounter"), #1/1/1999#) Then
        
            ' For the table Temp_POCounter.
            '
            strSQL = "DELETE FROM Temp_POCounter;"
            CurrentDb.Execute strSQL, dbFailOnError
            strSQL = "INSERT INTO Temp_POCounter ( PO_Count, PO_CountDate ) VALUES ( 1, #" & Format(PODate, "mm/dd/yyyy") & "# );"
            CurrentDb.Execute strSQL, dbFailOnError
            
            ' For the table Temp_PurchaseOrder.
            '
            strSQL = "DELETE FROM Temp_PurchaseOrder;"
            CurrentDb.Execute strSQL, dbFailOnError
            strNewPONumber = Format(Month(PODate), "00") & Format(Day(PODate), "00") & "01" & "-" & Mid(Year(PODate), 3)
            strSQL = "INSERT INTO Temp_PurchaseOrder ( PO_OrderNO, PO_Count, PO_OrderDate ) " & _
                     "VALUES ( '" & strNewPONumber & "', 1, #" & Format(PODate, "mm/dd/yyyy") & "# );"
            CurrentDb.Execute strSQL, dbFailOnError
        End If
    
    End Function
    Have a nice day!

  7. #7
    Join Date
    Mar 2006
    Posts
    11
    Thank you Sinndho!

    I will give it a try and let you know the outcome.

    Thanks again!

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

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