How would I run an update query in Access 2000 to null all cells that contain a last name?

Posted on 2009-02-19
Last Modified: 2012-05-06
I would like to run an update query on a table that contains two fields. One field contains either
a last name or descriptors of multiple words. I would like to delete all last names, i.e. all cells in
the field that contain only one word. What would be the simplest way to achieve this update?
I primarily need to know what would go in the criteria field to null  the "last name" cells.
Question by:PDSWSS
    LVL 92

    Accepted Solution

    UPDATE SomeTable
    SET SomeColumn = Null
    WHERE InStr(1, SomeColumn, " ") = 0

    Now, anyone with 2+ word last names will slip through the net...

    Author Comment

    Thanks for your quick response. Your code did the job.

    Could you explain the logic  behind the code      (1, Column, " ") = 0

    Thanks again.

    Author Closing Comment

    Thanks for your very quick response.

    Author Comment


    Could you also let me know what  the Where statement would be for an Update query that would delete those cells in the same
    field I mentioned above where the word "trial" was one of the words in the cell.  Thanks again

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    A quick step-by-step overview of installing and configuring Carbonite Server Backup.
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    754 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

    19 Experts available now in Live!

    Get 1:1 Help Now