1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Replacing in Access

Discussion in 'Business Applications' started by benhur1978, Oct 14, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. benhur1978

    benhur1978 Thread Starter

    Joined:
    Jan 23, 2007
    Messages:
    33
    Hi all,

    I have a download of about 500,000 records, if there is a "Null" value in the field the system that the data is downloaded from inserts the word "Unspecified".

    On a "normal" download there are 10 columns where "Unspecified" could apprear, (in total about 1.6 million times) rather than creating 10 queries for each column, does anyone know how to replace "Unspecified" with null on all columns in one query please.

    I have notepad++ which is one option, but I wanted to build it into an automated process.

    Thanks in advance for your help.

    B
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Have you tried using a "Replace" Query on more than one column at the same time?
     
  3. benhur1978

    benhur1978 Thread Starter

    Joined:
    Jan 23, 2007
    Messages:
    33
    Hi OBP,

    Thanks for the suggestion.

    I have just tried this and it cames back with an error saying that there is a validation rule violation.

    I have checked the table I am working on and all the fields are text, there are no keys, and no indexes, or compression, field sizes are all 255.

    Any suggestions.

    Thanks again

    B
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I would try just 2 Column to see if it accepts that and continue adding Columns until you hit a problem.
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What Syntax did you use for the Replace Statement?
    This in the "Update to Row" works for me
    Replace([data],':','/')
     
  6. benhur1978

    benhur1978 Thread Starter

    Joined:
    Jan 23, 2007
    Messages:
    33
    Thanks for you help OBP.

    The syntex used is Replace([Title],"Unspecified","")

    I had a look at the design of the table and there is an option in the General Tap that says "Allow Zero Length" this was set to "No", I have updated all the columns to "Yes" an the replace now works.

    Thanks again

    B
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/759081

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice