Link to home
Create AccountLog in
Avatar of MCaliebe
MCaliebeFlag for United States of America

asked on

Examine a table for a true condition

Hi everyone,

I want to fire off an SQL update query only if an update is required.  My table has a True/False field [TF_Field] and what I am looking to do is evaluate if any of my records have the [tf_field] = true, then run the SQL Update.

I thought setting a variable based on a COUNT entry and evaluation if it was >0, however I coudn't make it work.

Any thoughts?
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

UPDATE YourTable
SET whatever = 'something'
WHERE TF_Field = True

Define for us the frequency at which you need this run (just once, every time a row is added to the table, every day at midnight, ...)
Avatar of MCaliebe

ASKER

I'm currently running this code when an update button on the form is pressed, however I am updating a 35,000 record table whether tbl_EOS_Rank.Select is true or not.  

I'd like to examine the records to see if anything requires updating before firing off two queries.

Private Sub Cmd_Update_excl_Click()
Dim stSQL As String
Dim response As String


    response = MsgBox("Exclude selected items from further review?", vbOKCancel)
        If response = vbCancel Then
        Exit Sub
        End If
        
DoCmd.SetWarnings False
Me.Dirty = False

stSQL = "INSERT INTO tbl_Excl_Item_Numbers ( ITEM_SEQUENC_NO )" & _
        "SELECT tbl_EOS_Rank.ITEM_SEQUENC_NO " & _
        "FROM tbl_EOS_Rank " & _
        "WHERE (((tbl_EOS_Rank.Select)=True))"
    
 DoCmd.RunSQL stSQL
 
  stSQL = "UPDATE tbl_eos_rank " & _
         "SET Excl = True " & _
         "WHERE (((tbl_EOS_Rank.select) =TRUE))"
         
DoCmd.RunSQL stSQL

 DoCmd.SetWarnings True
 FilterClear
 ChkOff
  
 Me.Requery
 
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
This works well, however I did have to change the code slightly.  Not sure why.  Trial and error on my part.

It appears this counts all the ID records if any [select] fields are true. Is this faster then just reporting back that a [select]=true does exist?

If Nz(DCount("ID", "tbl_EOS_Rank", [Select] = True), 0) Then

Open in new window

I note that you did NOT enclose the "Select=True" portion in double quotes. Probably just a typo, but be sure to do that ...
Strange however it works with and without quotes.  Thanks again!