BroadAustralia
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([Teleph one]," ","")
And hit run ( the ! ) but I get data type mismatch in criteria expresssion...
PLease help me sort this out...
Broad.
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([Teleph
And hit run ( the ! ) but I get data type mismatch in criteria expresssion...
PLease help me sort this out...
Broad.
Try this:
"Update Data set Telephone =" & Replace([Telephone]," ","")
ASKER
Thanks for reply -- this returns "Invalid use of NULL"
Broad.
Broad.
Add a Not Is Null as a criteria for that cell.
mx
mx
ASKER
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.
PuppyDogBuddy: Thanks aswell ;) Returns Data Type Mismatch in criteria expression...
Perhaps I have a problem somewhere else??
Broad.
ASKER
Mx: Add a what now where?? : )
Sorry
Broad.
Sorry
Broad.
UPDATE Data SET Data.[PhoneNumber] = Replace(CStr([PhoneNumber] )," "," ")
WHERE (((Data.[PhoneNumber]) Is Not Null));
WHERE (((Data.[PhoneNumber]) Is Not Null));
"Update Data set Telephone ='" & Replace([Telephone]," ","") & "'"
"Update Data set Telephone ='" & Replace(Nz([Telephone],"") ," ","") & "'"
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Update Data set Telephone"=Replace([Teleph one] & " "," ","")
ASKER
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.
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\AzDo c\Pgrm\AzD ocPgrm2K3. 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
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
"C:\Access2003Clients\AzDo
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
ASKER
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
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
ASKER
ahhh
go to
View | SQL View
and it works!!
I said i was a beginner!! ;)
Thanks to all
Broad.
go to
View | SQL View
and it works!!
I said i was a beginner!! ;)
Thanks to all
Broad.
you are welcome ...
mx
mx
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!!
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));
"Update Data set Telephone"=Replace(CStr([T
mx