descriptor indicator to insert nulls 2

[karenmierkalns]

I was looking through previous postings, and found what I was looking for, but not in any detail.

Does this descriptor indicator to insert nulls also work with updates?

My problem is working in VB. I want to be able to set an already defined value as null. It was simple enough with the insert, because I just didn't insert anything into that field. It is a join problem. I either have to have a valid something, or a nothing. How can I go from something to nothing by using this descriptor indicator to update nulls? I haven't heard of this before.

Thanks, - Karen

 

[vtj]

Would you clarify what you are trying to insert. Must it be a NULL or are you trying to "blank" out the current value?

Thanks VTJ

 

[karenmierkalns]

Sorry. Yes, I am trying to blank out the current value. But I cannot enter a space because it must match up with a value in a related table.

Thanks. - Karen

 

[Tim1]

Hi Karen

The easiest way to do this is just to pass the value NULL (no quotes) into the field that you want set to null.

However, don't forget, even if two fields are NULL, they will not be equal (because to an RDBMS, NULL doesn't equal anything, even another NULL).

What you would need is something like

WHERE (tablea.fielda = tableb.fieldb OR
(tablea.fielda IS NULL AND
tableb.fieldb IS NULL)

HTH

Tim

 

[vtj]

Is the field NULL capable? If the field allows NULLS you could do a simple update SQL and set the value to NULL based on your select criteria. If you have a "look up" table that contains the list of valid entries add a "blank" value to that table and you shouldn't have a problem.

I'm sorry if I am still missing the mark on this. I hope this will at least get you thinking of alternate solutions (e.g. temporary table with the one record you need updated. Update and then insert that record into the table overwriting the current information with the new.

Good luck
VTJ

 

[karenmierkalns]

I just figured this out. I'm using an Access database to be integrated with VB programming.

If you are properly doing your join clauses, then you don't need any physical joins in Access. So, I removed the unnecessary joins between tables, and it allows me to enter in null or empty quotes, or anything I want.

Thanks for the tips. It got me thinking to what I could be doing, rather than accepting a completely different approach. - Karen