SQL ===If Then Else===

[scottian]

I have this SQL statement which is contained in a loop.

DoCmd.RunSQL "UPDATE Top1 SET Top1.[Index] = 1 WHERE (((Top1.Index)<> Horse ));"

Is there a way to obtain the value Horse and use in a an if then else statement
Pseudo:

if (SELECT table1.FIELD1 FROM table1 WHERE (((table1.FIELD1)="Horse")); is true Then
DoCmd.RunSQL "UPDATE Top1 SET Top1.[Index]=1"
Else
DoCmd.RunSQL "UPDATE Top1 SET Top1.[Index]=1"
End If


"My God! It's full of stars...

 

[SkipVought]

scottian,

(SELECT table1.FIELD1 FROM table1 WHERE (((table1.FIELD1)="Horse")) returns a Recordset Object and not a Boolean value.

You would need to TEST the Recordset Object to determine if there were at least one value returned or not.


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[scottian]

what im trying to do is select the top record and check a value within one of the fields if the value is true then i want to update another field with a value.

but i cant work out how to test the value first, i thought it could be passed to a variable to be checked.
im stumped

"My God! It's full of stars...

 

[SkipVought]

Set cnn = New ADODB.Connection
cnn.Open strCnn
Set rst = New ADODB.Recordset
rst.Open YourSelectSQL, cnn,,,adOpenStatic
with rst
  .movefirst
  If rst!YourBooleanField Then
    'got it
  Else
    'nada!
  End if
end with

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[PHV]

DoCmd.RunSQL "UPDATE Top1 SET Top1.[Index]=1"
Else
DoCmd.RunSQL "UPDATE Top1 SET Top1.[Index]=1"
What is the difference between the Then and the Else parts in your pseudocode ?


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244