Set to Null in Access query

Posted on 2014-07-16
Last Modified: 2014-07-23
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 '*';
Question by:rrhandle8
    LVL 84
    Is that the full SQL string you're trying to run? Can you show the full code snippet?

    Author Comment

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

    Accepted Solution


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

    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 '*';

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

    update scratchoff set  Email = Null where Email like '*';
    LVL 84
    Is the data stored in Access? Or in SQL Server (or some other server database)?
    LVL 30

    Expert Comment

    Try: If using access,
    DoCmd.RunSQL "update scratchoff set Reward = Null ,  Redeemed = Null, Email = Null where Email like ""*"""

    Open in new window

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

    Expert Comment

    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#

    Author Closing Comment

    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.
    LVL 84
    No, there's no trouble with update multiple fields based on any specific fields included in the WHERE clause.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now