Beginner help running VBA code for each record in SQL query

Posted on 2009-04-21
Last Modified: 2012-05-06
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?
Question by:jrc4728
    LVL 65

    Expert Comment

    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)
    LVL 65

    Expert Comment

    I can do you a vba solution as well but for what you want, it can easily be done in sql.
    LVL 5

    Author Comment

    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.
    LVL 65

    Accepted Solution

    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"
                Debug.Print "FieldA is the same as FieldB"
            End If
            'Check next record
        'Close recordset
        Set rs = Nothing
    End Sub
    LVL 5

    Author Closing Comment


    Featured Post

    Courses: Start Training Online With Pros, Today

    Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

    Join & Write a Comment

    This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now