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?
MCaliebeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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, ...)
0
 
MCaliebeAuthor Commented:
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

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can check the value of that field before running like this:

If Nz(DCount("IDField", "tbl_EOS_Rank", "Select=True"),0) <>0 Then
  '/ run your update
End If

Note you'll have to change "IDFIeld to be an indexed field in your tbl_EOS_Rank table (the Primary Key is generally a good one to use)
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Download your Guide
 
MCaliebeAuthor Commented:
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

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I note that you did NOT enclose the "Select=True" portion in double quotes. Probably just a typo, but be sure to do that ...
0
 
MCaliebeAuthor Commented:
Strange however it works with and without quotes.  Thanks again!
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.