Solved

Examine a table for a true condition

Posted on 2013-01-04
6
319 Views
Last Modified: 2013-01-07
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?
0
Comment
Question by:MCaliebe
  • 3
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38745180
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
 

Author Comment

by:MCaliebe
ID: 38745301
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 150 total points
ID: 38746747
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:MCaliebe
ID: 38750781
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
 
LVL 84
ID: 38751886
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
 

Author Comment

by:MCaliebe
ID: 38751928
Strange however it works with and without quotes.  Thanks again!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

14 Experts available now in Live!

Get 1:1 Help Now