Beginner help running VBA code for each record in SQL query

I have a table with a fluxuating number of records. I would like to periodically look at that table and determine if fieldA is greater than fieldB.

How do I code vba to run a logical comparison on each record in the table?
LVL 5
jrc4728Asked:
Who is Participating?
 
rockiroadsConnect With a Mentor Commented:
ok, an example that goes thru the whole table

Public Sub Example()

    Dim rs As DAO.Recordset
   
   
    'Define your SQL here, the records you want to check
    Set rs = CurrentDb.OpenRecordset("select * from mytable")
   
    'Keep looping until no more records
    Do While rs.EOF = False
   
        'If FieldA > FieldB then tell user (results immediate window)
       
        'Reference field using !
        If rs!FieldA > rs!FieldB Then
            Debug.Print "FieldA is greater"
        ElseIf rs!FieldA < rs!FieldB Then
            Debug.Print "FieldB is greater"
        Else
            Debug.Print "FieldA is the same as FieldB"
        End If
       
        'Check next record
        rs.MoveNext
    Loop
   
    'Close recordset
    rs.Close
    Set rs = Nothing
End Sub
0
 
rockiroadsCommented:
This can be done in sql as well like this

select iif(fieldA>=fieldB,"FieldA is Greater or Equal To FieldB","FieldB is Greater than FieldB") as FieldDiff
from mytable

syntax of iif

iif(condition,value if true,value if false)
0
 
rockiroadsCommented:
I can do you a vba solution as well but for what you want, it can easily be done in sql.
0
 
jrc4728Author Commented:
Thanks. I'd like to see the vba solution. I will be calling some external code if condition is true. As well, I will be performing different actions based on the amount of difference.
0
 
jrc4728Author Commented:
thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.