[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

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';
0
rrhandle8
Asked:
rrhandle8
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Is that the full SQL string you're trying to run? Can you show the full code snippet?
0
 
rrhandle8Author Commented:
Yes, that is the full string.  I changed from using % to using *, but that didn't work either.
0
 
Rey Obrero (Capricorn1)Commented:
try

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

for testing to find which field is giving the error, update the fields one at a time using three update queries

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

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

update scratchoff set  Email = Null where Email like '*r.com';
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Is the data stored in Access? Or in SQL Server (or some other server database)?
0
 
hnasrCommented:
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.
0
 
Helen FeddemaCommented:
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#
0
 
rrhandle8Author Commented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
No, there's no trouble with update multiple fields based on any specific fields included in the WHERE clause.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now