empty records

[teiben]

I have been going back in a db to the beginning of time and restructuring...I have a field sales person if its null I need to assign a value of 100 to another field representativeid, I have been using the update query to accomplish the update until now. I've tried is null and "" to get this query to update, any idea?

 

[jzwp22]

Can you provide the SQL text of the query and an example of the data. Also, is the ID field a text or numeric datatype?

 

[teiben]

UPDATE Initial INNER JOIN tblRepresentation ON Initial.RepresentationID = tblRepresentation.RepresentationID SET Initial.RepresentationID = 10000
WHERE (((Initial.Salesperson)="ISNULL"));


The representationid field is numeric field, Long Integer, salesperson is text

 

[georgedwilkinson]

UPDATE Initial INNER JOIN tblRepresentation ON Initial.RepresentationID = tblRepresentation.RepresentationID SET Initial.RepresentationID = 10000
WHERE nz(Initial.Salesperson,'') = '';

or

UPDATE Initial INNER JOIN tblRepresentation ON Initial.RepresentationID = tblRepresentation.RepresentationID SET Initial.RepresentationID = 10000
WHERE coalesce(Initial.Salesperson,'') = '';

 

[doco]

WHERE (((Initial.Salesperson Is Null ));