Link to home
Start Free TrialLog in
Avatar of BroadAustralia
BroadAustraliaFlag for Australia

asked on

access 2003 - remove spaces in field.

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.

Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Try:

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

mx
Avatar of puppydogbuddy
puppydogbuddy


Try this:
"Update Data set Telephone =" & Replace([Telephone]," ","")
Avatar of BroadAustralia

ASKER

Thanks for reply -- this returns "Invalid use of NULL"

Broad.
Add a Not Is Null as a criteria for that cell.

mx
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.
Mx: Add a what now where??   : )
Sorry
Broad.

UPDATE Data SET Data.[PhoneNumber] = Replace(CStr([PhoneNumber])," "," ")
WHERE (((Data.[PhoneNumber]) Is Not Null));
"Update Data set Telephone ='" & Replace([Telephone]," ","") & "'"
"Update Data set Telephone ='" & Replace(Nz([Telephone],"")," ","") & "'"
ASKER CERTIFIED SOLUTION
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Update Data set Telephone"=Replace([Telephone] & " "," ","")
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.
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

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

ahhh
go to

View | SQL View
and it works!!

I said i was a beginner!! ;)
Thanks to all
Broad.
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