rrhandle8
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';
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';
Is that the full SQL string you're trying to run? Can you show the full code snippet?
ASKER
Yes, that is the full string. I changed from using % to using *, but that didn't work either.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Is the data stored in Access? Or in SQL Server (or some other server database)?
Try: If using access,
DoCmd.RunSQL "update scratchoff set Reward = Null , Redeemed = Null, Email = Null where Email like ""*r.com"""
In SQL Null is inserted, and when viewed in access it shows blank.
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#
#12:00:00 AM#
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.
No, there's no trouble with update multiple fields based on any specific fields included in the WHERE clause.