isnull and not isnull

[webisti]

Hi
I am trying to make a vba code to handle an update query:

vba should look like: If sampling is not null and flagged is null then update comment with text " Buy"

where the fields are named:


sampling
comment
flagged

how to do that (I am sure is smth easy, but my brain has a black out)

thanks for your help

Webisti

[orange]

IS NULL , NOT IS NULL is SQL related

IsNull() and Not IsNull() are vba functions

see this for more info

[webisti]

I meant IsNull() and Not IsNull()

[orange]

Did you look at the link I gave you?

[webisti]

Yes I did but is not solving my issue
because instead of updating only where both conditions are met , the code is updating all records on the comment field.
my code is RunSQL: UPDATE orders SET order.COMMENT = ' OK TO BUY ' WHERE ISNULL(sampling) and ISNULL(flagged) ;

[June7]

Saving calculated data (data dependent on other data) is usually a bad idea. This status can be calculated whenever needed with expression in query or textbox.

However, try:

CurrentDb.Execute "UPDATE orders SET COMMENT = 'OK TO BUY' WHERE sampling Is Null And flagged Is Null;"

[orange]

As I said earlier and June7 mentions IsNull is a vba function; Is NULL is sql.

Are you sure the fields are NULL (unknown or undefined).

Could the fields be blanks or zero length strings????

Before doing an UPDATE, you should verify your logic by doing a SELECT (which is read only) and ensure the fields and values involved.

[webisti]

Hi
The fields are blank...empty. nothing there.
I will try to modify the code as you said and will let you know once done.

thanks

webisti

[webisti]

I TRIED THIS CODE BUT IS REPLACING ALL records on the comment field:

Private Sub update_Click()
CurrentDb.Execute "UPDATE orders SET orders.COMMENT = '' WHERE NOT ISNULL(sampling) AND ISNULL(FLAGED_BY) ;"
End Sub

instead of just updating those 5 records where the sampling is not blank (flagged_by is blank), it is updating everything....
any ideas

[orange]

Did You try the code that June7 suggested?

CurrentDb.Execute "UPDATE orders SET COMMENT = 'OK TO BUY' WHERE sampling Is Null And flagged Is Null;"

[webisti]

Yes I did, but nothing....same result as before..

[orange]

Select Comment
, iif(IsNull(sampling),"S_Is_Null", "S_Not_NULL, Flagged_Is(" & Flagged &")"
, iif(IsNull(flagged,"F_is_Null", "F_Not_NULL, Sampling_Is(" & Sampling &")"
FROM
Orders;

Try running this. copy it and paste it in the SQL view of the query wizard.