?
Solved

Access 2003, Update Query is killing my text

Posted on 2009-03-31
3
Medium Priority
?
293 Views
Last Modified: 2013-11-27
I have a field called [QPR/QPI Number] in a table called  SkpiUpdate.
I need to clean all the special characters from the field.

However when I run the below Update query it clears out many of fields from the record set.
Currently there are 93 fields that are NULL in this field after I run the query there are 134 NULL records.

All I am trying to do is remove the "-" from a couple of the records. The field is a text field. Mostly all numbers though. (Which is fine)

Here is my SQL: ( I use this in another area and it works fine)
UPDATE SkpiUpdate SET SkpiUpdate.[QPR/QPI Number] = Replace(Mid([QPR/QPI Number],8),"-","");
0
Comment
Question by:ggodwin
3 Comments
 
LVL 5

Expert Comment

by:defi0
ID: 24030854
If your field contains a value but does not contain a '-' after the 7th character, it will indeed be nullified. You should first test that Mid([QPR/QPI Number],8) contains a dash. Also, is it normal that it gets rid of the first character in all cases?
0
 

Author Comment

by:ggodwin
ID: 24030933
Is there easier way to just remove all the "-"?
Now that I think about it. I am not sure where this query came from?
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 1000 total points
ID: 24030987
Your query is stripping first 7 chars and replacing all - from 8th character

Does that ring any bells?

If u just want to remove the dashs, you can just use replace without using the mid


update mytable set myfield = replace(myfield,"-","")

be warned, it will remove ALL occurrences. It may not be what you want. Before big changes, its recommended to backup db
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

568 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