[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

access 2003 - remove spaces in field.

Posted on 2007-08-07
17
Medium Priority
?
2,471 Views
Last Modified: 2010-05-18
Please help!!
I have an Access Table with spaces in the Telephoen field and want to remove them.

I am a complete access beginner so I need everything explained if you have the time..

From previous posts in EE I thought I have to do this:

Create a Query -- and go into the query editor.

Then choose the table...

From here choose the Field ---- Telephone, then select the table from the drop down box...

Then in the criteria?? put the statement:

"Update Data set Telephone"=Replace([Telephone]," ","")

And hit run ( the ! ) but I get data type mismatch in criteria expresssion...

PLease help me sort this out...

Broad.

0
Comment
Question by:BroadAustralia
  • 6
  • 6
  • 3
  • +2
17 Comments
 
LVL 75
ID: 19651703
Try:

"Update Data set Telephone"=Replace(CStr([Telephone])," ","")

mx
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 19651718

Try this:
"Update Data set Telephone =" & Replace([Telephone]," ","")
0
 
LVL 1

Author Comment

by:BroadAustralia
ID: 19651724
Thanks for reply -- this returns "Invalid use of NULL"

Broad.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 75
ID: 19651732
Add a Not Is Null as a criteria for that cell.

mx
0
 
LVL 1

Author Comment

by:BroadAustralia
ID: 19651733
Mx: Thanks for reply -- this returns "Invalid use of NULL"

PuppyDogBuddy: Thanks aswell ;)    Returns Data Type Mismatch in criteria expression...

Perhaps I have a problem somewhere else??

Broad.
0
 
LVL 1

Author Comment

by:BroadAustralia
ID: 19651736
Mx: Add a what now where??   : )
Sorry
Broad.

0
 
LVL 75
ID: 19651742
UPDATE Data SET Data.[PhoneNumber] = Replace(CStr([PhoneNumber])," "," ")
WHERE (((Data.[PhoneNumber]) Is Not Null));
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 19651743
"Update Data set Telephone ='" & Replace([Telephone]," ","") & "'"
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 19651755
"Update Data set Telephone ='" & Replace(Nz([Telephone],"")," ","") & "'"
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 19651759
*** TYPO ** in Replace:

UPDATE Data SET Data.[PhoneNumber] = Replace(CStr([PhoneNumber])," ","")
WHERE (((Data.[PhoneNumber]) Is Not Null));

mx
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 19651764
Update Data set Telephone"=Replace([Telephone] & " "," ","")
0
 
LVL 1

Author Comment

by:BroadAustralia
ID: 19651861
Hi MX,
I receive the error:
The expression you entered contains invalid syntax
You may have entered an operand without an operator  

and Access highlights "WHERE" in the following code: ( which is in the criteria field )
UPDATE QLD SET QLD.[Telephone] = Replace(CStr([Telephone])," ","")
WHERE (((QLD.[Telephone]) Is Not Null));

Broad.
0
 
LVL 75
ID: 19651890
Don't know.  I just created a table name QLD with a field called Telephone ... added a couple of numbers ... and this works fine:

UPDATE QLD SET QLD.[Telephone] = Replace(CStr([Telephone])," ","")
WHERE (((QLD.[Telephone]) Is Not Null));

Which is what you just posted.

Just as a precaution here ... since this is NOT making sense ... do this:
(I need to zzzzzz now also)

A **DeCompile** may help here ...

But first, if you have not already:
Check for any **Missing References via the VBA Editor>>Tools>>References ....

Then, follow this procedure:

0) **Backup your MDB**
1) Compact and Repair the MDB, as follows:
Hold down the Shift key and open the MDB, then from the menu >>Tools>>Database Utilities>>Compact and Repair ...
2) Execute the Decompile (See example syntax below) >> after which, your database will reopen.
3) Close the mdb
4) Open the mdb and do a Compact and Repair (#1 above).
5) Open the mdb:
    a) Right click over a 'blank' area of the database window (container) and select Visual Basic Editor. A new window will open with the title 'Microsoft Visual Basic' ... followed by then name of your MDB.
    b) From the VBA Editor Menu at the top of the window:
       >>Debug>>Compile
        Note ... after the word Compile ...you will see the name of your 'Project' - just an fyi.

6) Close the mdb
7) Compact and Repair one more time.

*** Executing the DeCompile:
Here is an example of the command line syntax  (adjust your path and file name accordingly) for executing the

decompile:

Run this from Start>>Run, enter the following command line ...

"C:\Program Files\Microsoft Office\Office\Msaccess.exe" /decompile

"C:\Access2003Clients\AzDoc\Pgrm\AzDocPgrm2K3.mdb"

For more detail on the Decompile subject ... visit the Master on the subject (and other great stuff) Michael Kaplan:

http://www.trigeminal.com/usenet/usenet004.asp?1033

mx

0
 
LVL 1

Author Comment

by:BroadAustralia
ID: 19651945
Hi guys!
MX has crashed and all the other solutions dont seem to work..

The problem MX put above is not the issue because I get the fault when I create a blank database with just a few records...

I must be putting the code in the wrong area if this is the case as I still get the same ( previous post error ).

Do I go into Query and create NEW and then:
- Select Table
- Then select Update Query from the Query Menu ( along side Tools ) ?
- Then select the field and table from drop down box?

If this is all correct where do I put my statement?
Sorry I don't know access :(
Broad

0
 
LVL 1

Author Comment

by:BroadAustralia
ID: 19652103
ahhh
go to

View | SQL View
and it works!!

I said i was a beginner!! ;)
Thanks to all
Broad.
0
 
LVL 75
ID: 19655074
you are welcome ...

mx
0
 

Expert Comment

by:ggodwin
ID: 23570155
This resolution worked very well for me also.

However, I have a question.
Is it possible to do multiple fields like this?
Attached is the code that I need. However, I also want to clear our the blanks in a field called QPR/QPI Number.

I can create another query but I thought if I could eliminate a step that would be best.

Thanks!!

UPDATE SkpiUpdate SET SkpiUpdate.[Tag Number] = Replace(CStr([Tag Number])," ","")
WHERE (((SkpiUpdate.[Tag Number]) Is Not Null));

Open in new window

0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

834 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