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?
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?
Microsoft Access
Last Comment
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.
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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?
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
I note that you did NOT enclose the "Select=True" portion in double quotes. Probably just a typo, but be sure to do that ...
ASKER
Strange however it works with and without quotes. Thanks again!
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, ...)