Solved

Examine a table for a true condition

Posted on 2013-01-04
6
323 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 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
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 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

726 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