IF then statement

[TSO456]

Hello,

I have an Access Table:

Values (1) Values (2)
$200 $200
$250 $250
$300 $300

I need to write VBA code that checks if EVERY amount in the values(1) column is less or equal to the amounts in the values (2) by 5%. Each amount in Values (1) need to be checked against all the amounts in Values (2). So i need to know how to write an if then statement that steps through Values (1) Column and loops in values (2).

Thank you in advance
Jill

 

[MoLaker]

If I understand it, you need the following check performed on each record:

values(1) <= (values(2) + .05 * values(2))

Is that correct? If so, then loop through an open recordset and test each record.

do while not rs.eof
    if rs("values(1)") <= (rs("values(2)") + .05 * rs("values(2)")) then
        ' do whatever if it is true here
    else
        ' do whatever if it is NOT true here
    end if
    rs.movenext
loop

You can get how to open and close a recordset from Help.

 

[EBGreen]

Couldn't you do it in the query instead?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[MoLaker]

You could do it in a query, but a whole lot would depend on what you want to do when/if the test fails.

 

[TSO456]

How can I do it in a query?
How can a query compare each value to multiple values?

thanks
Jill

 

[EBGreen]

So in the example above, each value in (1) must be less than all of the values in (2)?

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Tranman]

Jill,
This may be backward from what you want. It's a bit hard to understand which set you want to compare to each single value, but the idea will work either way. Just fill one recordset with a complete column, then for each row in the first recordset, open the second recordset and fill it with all of the amounts that don't match your criteria. If EOF is true (no rows returned), you know that all rows match.

Private Sub CompareFld()
Dim rs1 As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
rs1.Open "Select fld1 from myTable", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Do While Not rs1.EOF
rs2.Open "Select fld2 from myTable where fld2 >= " & rs1.Fields("fld1") * 1.05, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
Select Case rs2.EOF
Case True
'none are > fld1 * 1.05
MsgBox "none greater"
Case False
'Whatever you want to do when you find one > fld1 * 1.05
'You could iterate through rs2, reporting all that are over the limit
MsgBox "at least one greater"
End Select
rs2.Close
Set rs2 = Nothing
rs1.MoveNext
Loop
rs1.Close
Set rs1 = Nothing
End Sub

 

[Zion7]

You hit the nail on the head TranMan!