Results 1 to 3 of 3
  1. #1
    HeadGasket's Avatar
    HeadGasket is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2011
    Location
    Sonoma County, Northern California
    Posts
    33

    Discovering what is triggering a weekly reset. Trying to duplicate in new DB.

    Originally posted in the General Access area but thought it might be more related to the Programming section.



    Greetings Oh Exhaulted and Divine Wizards of Access!

    I have a field that is used to create a unique invoice/event/report number that resets back to '1' at the beginning of the week. For the life of me I cannot find the VB code, macro, expression, whatever, that allows this field to reset each week. I'm trying to duplicate this type of thing in a new database. A guru helped me with this a couple of years ago and I don't remember how we did it. Anyway....

    Question is, how might one go about finding what is triggering the weekly reset?

    Below is more specific info in case you need it.

    Three fields in my table (tbl_NCMain) are:

    ReportYear = Text field with Default Value as =Format(Date(),"yy"). Returns two-digit number based on last two digit of current year (yy)

    ReportWeek = Text field with Default Value as =Format(Date(),"ww"). Returns the two-digit week based on 52/53 weeks per year.

    ReportID = Number field that starts at '1' for the first entry of the week and increments by +1 for each additional entry (up to 99 max). Resets back to '1' at the beginning of the following week.

    These three fields are then concatenated in my form and report to create a event/invoice number for printing purposes.

    The only VB code I can find that is related is below. I can see how it increments but it doesn't seem to be what is controlling the weekly reset of ReportID back to '1'.

    Private Function GetReportNumber()
    Dim strCriteria As String
    strCriteria = "ReportYear = """ & Me.ReportYear & """ " & "And ReportWeek = """ & Me.ReportWeek & """ "
    Me.ReportID.DefaultValue = """" & Nz(DMax("ReportID", "tbl_NCMain", strCriteria), 0) + 1 & """"
    End Function

    Let me know if you need more info or have it presented differently. Thanks.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,640
    Please don't post the same question twice:

    https://www.accessforums.net/access/...set-20975.html
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    HeadGasket's Avatar
    HeadGasket is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Dec 2011
    Location
    Sonoma County, Northern California
    Posts
    33
    No problem. I was used to a different forum from a couple years ago in which questions might not be answered if they weren't placed in the appropriate category. Wasn't sure if this forum opperated similarly.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Weekly Total
    By coffeyja in forum Access
    Replies: 3
    Last Post: 01-13-2012, 12:30 AM
  2. Weekly Totals
    By tcheck in forum Access
    Replies: 4
    Last Post: 09-27-2011, 09:35 AM
  3. Is weekly / monthly automation possible?
    By 10 Gauge in forum Access
    Replies: 4
    Last Post: 03-17-2011, 07:23 AM
  4. Weekly calculation query?
    By katie_88 in forum Queries
    Replies: 1
    Last Post: 07-13-2010, 10:54 AM
  5. weekly total
    By nkuebelbeck in forum Queries
    Replies: 2
    Last Post: 03-24-2010, 02:59 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums