asked on

Set to Null in Access query

I have a Date/Time, Number, and text field in one table, and want to reset them all to NULL.
Access gives me a "type conversion" error. Any ideas what I am doing wrong?

update scratchoff set Reward = Null , Redeemed = Null, Email = Null where Email like '*r.com';

Scott McDaniel (EE MVE )

Is that the full SQL string you're trying to run? Can you show the full code snippet?

rrhandle8

ASKER

Yes, that is the full string. I changed from using % to using *, but that didn't work either.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott McDaniel (EE MVE )

Is the data stored in Access? Or in SQL Server (or some other server database)?

Hamed Nasr

Try: If using access,

DoCmd.RunSQL "update scratchoff set Reward = Null ,  Redeemed = Null, Email = Null where Email like ""*r.com"""

Open in new window

In SQL Null is inserted, and when viewed in access it shows blank.

Helen Feddema

What data type is each field? It may be better to save a zero-length string ("") to a text field, a 0 to a numeric field, and this peculiar value (representing an unselected date) to a Date field:

#12:00:00 AM#

rrhandle8

ASKER

Rey, when I did it one field at a time, all three fields update. Hmm.... wonder if there is a problem with updating multiple fields when one of the fields is included in the WHERE clause? Anyways, thanks, I got the job done.

Scott McDaniel (EE MVE )

No, there's no trouble with update multiple fields based on any specific fields included in the WHERE clause.