0

Ok, I asked this question in another post but it became too messy...

Here is what I would like to do...

First, there is a table called Age where the user enters age ranges, and there can be multiple age ranges entered into the table... In field names in the table are "MinAge" and "MaxAge".

I have a toggle button whereby the user validates the data to ensure four things:

  1. That there aren't any null fields in the table
  2. That the MaxAge is greater than the MinAge for each row
  3. That the MaxAge on a row is greater than the Min age on the previous row...
  4. That the Min Age on a row is sequentially by one than the Max Age on the next row

Some examples

- Min Age Max Age ROW 1: 0 5 ROW 2: 6 8

PASS because Min age is > than Max age for each row, Min age on row 2 is > than max age on row 1, and Min age on row 2 is sequentially greater by 1 than the max age on row 1.

          Min Age    Max Age
   ROW 1:               5
   ROW 2:   6

FAIL because there are null values in row 1 and 2

          Min Age    Max Age
   ROW 1:   10           5
   ROW 2:   6            8

FAIL because the Min Age is greater than the Max age in row 1

         Min Age    Max Age
   ROW 1:   0            5
   ROW 2:   4            8

FAIL because the Min Age on Row 2 is less than the Max age on row 1.....

Improve this question
2
  • 1
    Post the code you've written to so far and tell us where you're stuck. You're not likely to have someone just write your code for you (though stranger things have happened...) Taking the Tour will give you a better idea how to ask a good question that will get you the help you're after. Commented Jul 31, 2017 at 18:55
  • I started working with dlookup's but that only examines the first row of the data. That is where I am stuck Commented Jul 31, 2017 at 19:12

1 Answer 1

Reset to default
1

I would handle it within an array. Seems pretty straight forward past that

Dim i as long, j as long, z as long q as long
Dim arr As Variant
Dim arr2() As String, strMsg as string
Dmi rs as recordset
Dmim errBool as Boolean

set rs = CurrentDb.OpenRecordset("your query statement")
With rs
    rs.MoveLast
    q = rs.RecordCount
    rs.MoveFirst
    z = rs.Fields.Count
End With

ReDim xaRR(q, z)  
arr = rs.GetRows(q)

For j = LBound(arr, 2) To UBound(arr, 2)
    For i = LBound(arr, 1) To UBound(arr, 1)
        arr2(j, i) = arr(i, j)
    Next i
Next j

errBool = True
for i = lbound(arr2,1) to ubound(arr2,1)
    if i > 0
        If arr2(i,0)= arr2(i-1,1)+1 then 
            errBool = false
            strMsg = "Start point isnt an increment by 1 of the last position"
        end if 
        if (IsNull(arr2(i,0)) Or (IsNull(arr2(i,0))) = True Then 
            errBool = false
            strMsg = "You have null values"
        End if
        If arr2(i,0) > arr2(i,1) then 
            errBool = False
            strMsg = "You min is larger than your max for row " & i
        End
    end if
next i

if errBool = False then
    MsgBox strMsg
End if 

rs.Close
Set rs = Nothing
Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Benevolent soul
@n8 Makes up for the all the times I post answers to bad questions but still leave the "tricky parts" out for them to figure out on their own, lol
@ doug coats... Thanks! I am having issues with the portion "if i > 0" and "if (IsNull(arr2(i,0)) Or (IsNull(arr2(i,0))) = True Then...."
"if i > 0" was missing the "then"
"if (IsNull(arr2(i,0)) Or (IsNull(arr2(i,0))) was missing a ()
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.