Help with Sql statement 1

[osurfc]

I created an update query that I want to convert to a sql. The query works but when I view the sql and copy, the code errors at the " marks. I have tried replacing " with ' and # to try to get it to work to no avail ([start_postOp] is a time field)

This is the sql statement from the query. I cant figure out what I'm doing wrong. Tempting to run as an update qry but then I dont learn .....

Update tblAts Set tblAts.Post_opBloodLoss =
IIF("isnull[start_postOp]"),'',[totalBloodLoss];"

Thanks
Mark

 

[jedraw]

osurfc

Not exactly sure I understand what you're trying to do.

What the SQL is saying is

"For all records in tblAts

if the value of start_postOP is NULL , then
set the value of Post_opBloodLoss to "",
otherwise
set the value of Post_opBloodLoss to TotalBloodLoss"

Update tblAts Set tblAts.Post_opBloodLoss =
IIF("isnull[start_postOp]"),'',[totalBloodLoss];"

Try this for proper syntax

Update tblAts Set tblAts.Post_opBloodLoss =
IIF(isnull([start_postOp]),"",[totalBloodLoss];

 

[jedraw]

Hit the submit a little too early,
note the ) at the end of the statement, this closes the
iif statement

 Update tblAts Set tblAts.Post_opBloodLoss =
IIF(isnull([start_postOp]),"",[totalBloodLoss]);

 

[osurfc]

Sorry I missed [totalBloodLoss]-[Intra_opBloodLoss]. That may make a little more sense.

I copied/pasted your code into the module and I still get a runtime error #3075 syntax error in string in query expression.

This is what I have
strSQL_P_opBlood = "Update tblAts Set tblAts.Post_opBloodLoss = IIF(isnull([start_postOp]),"",[totalBloodLoss]-[intra_opBloodLoss]);"

But it still wont run

Mark

 

[JerryKlmns]

Either

strSQL_P_opBlood = "Update tblAts " & _
                   "Set Post_opBloodLoss = IIF(IsNull([start_postOp]), [COLOR=red yellow]''[/color], [totalBloodLoss]-[intra_opBloodLoss]);"

or

strSQL_P_opBlood = "Update tblAts " & _
                   "Set Post_opBloodLoss = IIF(IsNull([start_postOp]), [COLOR=red yellow]""""[/color], [totalBloodLoss]-[intra_opBloodLoss]);"

 

[JoeAtWork]

[totalBloodLoss]-[intra_opBloodLoss]

This part of your Iif function indicates to me that the field you are trying to update is numeric. In which case, trying to update it to anything with quotes is a syntax error. Depending on whether the field being updated is Required, I think it should be either:

strSQL_P_opBlood = "Update tblAts Set tblAts.Post_opBloodLoss = IIF(isnull([start_postOp]),Null,[totalBloodLoss]-[intra_opBloodLoss]);"

or (if it's a Required field)

strSQL_P_opBlood = "Update tblAts Set tblAts.Post_opBloodLoss = IIF(isnull([start_postOp]),0,[totalBloodLoss]-[intra_opBloodLoss]);"

 

[osurfc]

Thanks JoeAtWork. not a required field so option 1 is the correct choice for me. Works great thanks