Results 1 to 2 of 2
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    Need help with condition in a query

    I have a query that finds duplicate [SerialNumber] and gives all the information for each record. I have been trying to limit the results so if there is more than 7 days between [TestedDate] of each record it will be omitted from the results. Could someone help me with this. Here is the current SQL for the query.

    Code:
    SELECT qry_duplicates343sTested_Step1.SerialNumber, qry_duplicates343sTested_Step1.FixturePosition, qry_duplicates343sTested_Step1.TankTestedIn, qry_duplicates343sTested_Step1.LoadTested, qry_duplicates343sTested_Step1.TestedDate
    FROM qry_duplicates343sTested_Step1
    GROUP BY qry_duplicates343sTested_Step1.SerialNumber, qry_duplicates343sTested_Step1.FixturePosition, qry_duplicates343sTested_Step1.TankTestedIn, qry_duplicates343sTested_Step1.LoadTested, qry_duplicates343sTested_Step1.TestedDate;
    Thank you in advance
    Walker

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Make this query a Make-Table query, add a field to it (I call it "Omit", with Y/N data type), then run this code

    Code:
    Public Function CheckDates()
        Dim rst As Recordset, strSQL As String, HoldF As String, HoldDt As Date
        
        strSQL = "SELECT * FROM Table4 Order By SerialNumber, TestedDate;"
        Set rst = CurrentDb.OpenRecordset(strSQL)
        HoldF = rst!SerialNumber
        HoldDt = rst!TestedDate
        
        Do Until rst.EOF
            If rst!SerialNumber = HoldF Then
                If (rst!TestedDate - HoldDt) > 7 Then
                    With rst
                        .Edit
                            !Omit = True
                        .Update
                    End With
                End If
            End If
            HoldF = rst!SerialNumber
            HoldDt = rst!TestedDate
            rst.MoveNext
        Loop
        
    End Function

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

Similar Threads

  1. Marco Condition based off a Query
    By bigbox486 in forum Macros
    Replies: 1
    Last Post: 03-30-2015, 09:02 AM
  2. Replies: 3
    Last Post: 08-19-2014, 09:07 AM
  3. Add Query Based on if Condition
    By shakeelmscw in forum Queries
    Replies: 17
    Last Post: 01-18-2013, 02:35 AM
  4. If Condition Query in Access
    By Vishan in forum Queries
    Replies: 3
    Last Post: 10-30-2012, 12:05 PM
  5. Help with writing Query If Then Condition
    By taimysho0 in forum Queries
    Replies: 4
    Last Post: 01-20-2012, 06:09 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