Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    6

    Unanswered: Query to Search for a string of Zeros on a table

    I have the following example table where I want to find if any 3 month period has all Zeros between the START DATE and the FINISH DATE as they are for MAR-MAY 1986. Any help would be appreciated.

    Year JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC START DATE FINISH DATE
    1983 0 0 0 0 0 1 1 1 1 1 1 1 1-Jun-83 31-Jul-86
    1984 1 1 1 0 1 1 1 1 1 1 1 1 1-Jun-83 31-Jul-86
    1985 1 1 1 0 1 1 1 1 1 1 1 1 1-Jun-83 31-Jul-86
    1986 1 1 0 0 0 1 1 0 0 0 0 0 1-Jun-83 31-Jul-86
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In your sample data and for every row, the value of the column [START DATE] is "1-Jun-83" and the value of the column [FINISH DATE] is "31-Jul-86", whatever the value of the column [Year] can be. Is this normal?
    Have a nice day!

  3. #3
    Join Date
    Nov 2009
    Posts
    6
    Yes, the start and finish date referes to the entire set of data. In other words the first 5 months of 1983 and the last 5 months of 1986 should not be included in the query. That would leave MAR, APR and MAY of 1986 being the only 3 month gap, which is what I'm trying to do with a query.

    Thanks for your help.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure that it can be done in a query, at least with the subset of the SQL language the JET Engine can use.

    Here's a VBA function that returns the desired result in a Variant array:
    Code:
    Public Function FindThreeZero(ByVal TableName As String) As Variant
    
        Dim rst As DAO.Recordset
        Dim strBuffer As String
        Dim strRow As String
        Dim varArray As Variant
        Dim lngPos As Long
        Dim lngStartYear As Long
        Dim lngStartMonth As Long
        Dim lngEndYear As Long
        Dim lngEndMonth As Long
        Dim i As Long
        
        lngStartYear = DatePart("yyyy", DMin("[Start Date]", TableName))
        lngStartMonth = DatePart("m", DMin("[Start Date]", TableName))
        lngEndYear = DatePart("yyyy", DMax("[Finish Date]", TableName))
        lngEndMonth = DatePart("m", DMax("[Finish Date]", TableName))
        Set rst = CurrentDb.OpenRecordset(TableName, dbOpenSnapshot)
        With rst
            Do Until .EOF
                Select Case !Year
                    Case lngStartYear
                        strBuffer = ""
                        For i = lngStartMonth To 12
                            strBuffer = strBuffer & .Fields(i).value
                        Next i
                    Case lngStartYear + 1 To lngEndYear - 1
                        strBuffer = ""
                        For i = 1 To 12
                            strBuffer = strBuffer & .Fields(i).value
                        Next i
                    Case lngEndYear
                        strBuffer = ""
                        For i = 1 To lngEndMonth
                            strBuffer = strBuffer & .Fields(i).value
                        Next i
                End Select
                Do
                    lngPos = InStr(lngPos + 1, strBuffer, "000")
                    If lngPos > 0 Then strRow = strRow & ";" & lngPos
                Loop Until lngPos = 0
                If Len(strRow) > 0 Then
                    strRow = .Fields(0).value & strRow
                    If IsArray(varArray) = False Then ReDim varArray(0) Else ReDim Preserve varArray(0 To UBound(varArray) + 1)
                    varArray(UBound(varArray)) = Split(strRow, ";")
                    strRow = ""
                End If
                .MoveNext
            Loop
            .Close
        End With
        Set rst = Nothing
        FindThreeZero = varArray
        
    End Function
    Have a nice day!

  5. #5
    Join Date
    Nov 2009
    Posts
    6
    If I create a module with this code how do I run it in Access?

Posting Permissions

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