Solved

Examine a table for a true condition

Posted on 2013-01-04
6
324 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 66

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 85

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 85
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

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