Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    MrOhhmy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    23

    Help with query for archived data..


    I have a table for area reads. After each shift it archives to area_reads_archive. What I want is for my form to populate yesterdays finish number as today's start number. So I'm not sure how to make it a criteria of area_reads_archive and the Date()-1, but also the shift Days or Nights. Since each shift has it's own reads, but only Nights has the totalizer. Thanks for help on this.

  2. #2
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You would normally use a DMax() to query the archive table and find the last number.

    Without seeing some sample data and your desired outputs it's difficult to work out what your criteria should be.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe try
    Code:
    SELECT FinishNumber
    FROM tblShiftReadings
    WHERE (((ShiftDate)=Date()-1) AND ((DN_Shift)="Nights"));
    Change to your field and db names

  4. #4
    MrOhhmy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    23
    Here is an example of my file. So on the reads page, what I need is the starts for totalizer section to grab the data from the previous night shift as described above.Reads.zip

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    When you archive data, you MOVE the record from tbl_DW_Log to tbl_DW_Log_Archive?
    So tbl_DW_Log will only EVER have a maximum of 2 records? (1 Day and 1 Night shift for a specific date?)



    There is no field named "totalizer" or "yesterdays finish number". Do you mean "DW Finish"?



    NOTE: Should not have/use spaces in object names!!

  6. #6
    MrOhhmy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    23
    Sorry Yes, DW Finish, I'll apply the same to D01A finish...etc.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There was a couple of problems. On the form "frm_DW_Reads" there is a text box named "Date". "Date" is a reserved word and a built in function. The Access gnomes didn't like the control.
    I had to delete the control and create a new control (text box) - I named it "tbShiftDate".


    The code I came up with goes in the form ("frm_DW_Reads") Load event. It first checks to see if there is an entry in the [DW Start] control (your name -> t2).
    If there is an existing value in [DW Start], the code exits the sub.
    If the [DW Start] control does not have an entry, the code opens a query and gets the value, then stuffs it into the [DW Start] control.
    If there is not an entry in the previous date nights record the query does not return a record with a value, nothing happens - the control remains blank.

    Code:
    Option Compare Database  '<<-- these two lines should be at the top of EVERY code module
    Option Explicit          '<<-- these two lines should be at the top of EVERY code module
    
    Private Sub Form_Load()
        '==================================
       'enter previous day ending number into current day start field
       ' "frm_DW_Reads"
        '==================================
        Dim r As DAO.Recordset
        Dim sSQL As String
        Dim theDate As Date
    
        theDate = Me.tbShiftDate
    
        If Len(Trim(Me.[DW Start] & "")) > 0 Then
            'already a number entered
            Exit Sub
        Else
            'no number. Get yesterday's ending number
            sSQL = "SELECT [DW Finish]"
            sSQL = sSQL & " FROM tbl_DW_Log_Archive"
            sSQL = sSQL & " WHERE ShiftDate = #" & (Me.tbShiftDate- 1) & "# AND Shift = 'Nights';"
            '        Debug.Print sSQL
            Set r = CurrentDb.OpenRecordset(sSQL)
            If Not (r.BOF And r.EOF) Then
                If Len(r![DW Finish]) > 0 Then
                    Me.[DW Start] = r![DW Finish]
                End If
            End If
        End If
    
        'clean up
        r.Close
        Set r = Nothing
    End Sub

  8. #8
    MrOhhmy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    23
    I deleted the control box and added a new one as you instructed. I get a Run-Time Error '2448': You can't assign a value to this object.

    Debugger points to Me.[DW Start] = r! [DW Finish]

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Open the dB, open the table "tbl_DW_Log". Look at the field "DW Start". It should be empty. Close the table.
    Open the form "frm_DW_Reads" (however you want to do it. I just dbl click on the form name)

    The control for start now should have 2345674 in it. Close the form, open the table and look at the field "DW Start".
    It should have 2345674 in it.


    You really should remove the spaces in object names...... just saying
    Attached Files Attached Files

  10. #10
    MrOhhmy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    23
    Exactly what I needed. Thanks Steve and yes I will remove spaces.

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Good luck with your project.......

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    This is in response to you PM question
    How would I add to your code to also include the D01A,D01B,D01C starts to do the same?
    Add the fields to the query (in code). Then push the values from the query to the controls on the form.

    Note: I would change the code from
    Code:
    Me.[DW Start] = r![DW Finish]
    to this
    Code:
     Me.[DW Start] = Nz(r![DW Finish], 0)
    to handle any NULLs in those fields.

    If you have problems. post back and I will change your code.




    RE:Maximize
    I never us macros... I use code.
    So if you really, really think you need to maximize the form (tabbed document), add the command
    Code:
        DoCmd.Maximize
    just before the "End Sub" line in the FORM_LOAD code. (and delete the macro)

  13. #13
    MrOhhmy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    23
    They are part of the query. I'm just not sure how to add it to your code, something like
    sSQL = "SELECT [DW Finish],[D01A Finish], [D01B Finish] or however I should write it,

    Then on the other line
    Me.[DW Start] = Nz(r![DW Finish], 0)
    Me.[D01A Start] = Nz(r![D01A Finish], 0)


    That's where I'm stuck.

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Close...


    Try this:
    Code:
    Option Compare Database  ' <<-- these two lines should be at the top of EVERY code module
    Option Explicit          ' <<-- these two lines should be at the top of EVERY code module
    
    Private Sub Form_Load()
        '==================================
        'enter previous day ending number into current day start field
        '==================================
        Dim r As DAO.Recordset
        Dim sSQL As String
        Dim theDate As Date
    
        theDate = Me.tbShiftDate
    
        'query to get yesterday's ending numbers
        sSQL = "SELECT [DW Finish], [D01A Finish], [D01B Finish], [D01C Finish]"
        sSQL = sSQL & " FROM tbl_DW_Log_Archive"
        sSQL = sSQL & " WHERE ShiftDate = #" & (Me.tbShiftDate - 1) & "# AND Shift = 'Nights';"
        '        Debug.Print sSQL
        Set r = CurrentDb.OpenRecordset(sSQL)
        If Not (r.BOF And r.EOF) Then
    
            'don't want to overwrite existing entry in DW Start
            If Len(Trim(Me.[DW Start] & "")) = 0 Then
                Me.[DW Start] = Nz(r![DW Finish], 0)
            End If
    
            'don't want to overwrite existing entry in [D01A Start]
            If Len(Trim(Me.[D01A Start] & "")) = 0 Then
                Me.[D01A Start] = Nz(r![D01A Finish], 0)
            End If
    
            'don't want to overwrite existing entry in [D01B Start]
            If Len(Trim(Me.[D01B Start] & "")) = 0 Then
                Me.[D01B Start] = Nz(r![D01B Finish], 0)
            End If
    
            'don't want to overwrite existing entry in [D01C Start]
            If Len(Trim(Me.[D01C Start] & "")) = 0 Then
                Me.[D01C Start] = Nz(r![D01C Finish], 0)
            End If
    
        End If
    
        'clean up
        r.Close
        Set r = Nothing
    End Sub



    You really should remove any spaces in object names.
    If you want to separate letters, use
    [DW_Finish]
    [D01A_Finish]
    [D01B_Finish]
    [D01C_Finish]



    SPACES are a Pain in the neck, only 3 feet lower (for me).

  15. #15
    MrOhhmy is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2018
    Posts
    23
    Works. Thanks

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 09-11-2018, 12:40 PM
  2. Replies: 6
    Last Post: 04-03-2017, 08:02 AM
  3. Replies: 2
    Last Post: 07-12-2016, 12:11 PM
  4. Replies: 2
    Last Post: 12-17-2012, 03:46 PM
  5. Replies: 27
    Last Post: 08-14-2012, 09:05 AM

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