473,322 Members | 1,314 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Trim spaces before within and after. Please help

Hi. I am using access 97 and am trying to run a query that will crunch up all data in a field and thus removing all spaces.

For example I have a field called Address1:

Example data: 12 Murray Field Road

I want to create a select query that will output "12MurrayFieldRoad"

I know if I create a select query such as Field2: trim([Address1]) it only removes the non visble spaces before and after the entered data.

Does anybody have an idea how to removes the spaces before, within and after?

Your support would be much appreciated.

Thank you.
Sep 26 '07 #1
8 2507
Jim Doherty
897 Expert 512MB
Hi. I am using access 97 and am trying to run a query that will crunch up all data in a field and thus removing all spaces.

For example I have a field called Address1:

Example data: 12 Murray Field Road

I want to create a select query that will output "12MurrayFieldRoad"

I know if I create a select query such as Field2: trim([Address1]) it only removes the non visble spaces before and after the entered data.

Does anybody have an idea how to removes the spaces before, within and after?

Your support would be much appreciated.

Thank you.

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. Create this function in a module
  4.  
  5. Function removespaces(s As String)
  6. On Error Resume Next
  7.     Dim i As Integer    'Counter variable
  8.     Dim C As String     'current character being verified
  9.     Dim Result As String
  10.     'Set it all to upper case.
  11.     s = UCase$(s)
  12.     If Len(s) < 1 Then Exit Function
  13.     Result = ""
  14.     For i = 1 To Len(s)
  15.         C = Mid$(s, i, 1)
  16.         If InStr(1, "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ/\", C) > 0 Then
  17.             Result = Result & C
  18.         End If
  19.     Next i
  20.     removespaces = Result
  21. End Function

Use it in the grid like you would the TRIM function ie
removespaces([Address1])

Regards

Jim
Sep 26 '07 #2
ADezii
8,834 Expert 8TB
Hi. I am using access 97 and am trying to run a query that will crunch up all data in a field and thus removing all spaces.

For example I have a field called Address1:

Example data: 12 Murray Field Road

I want to create a select query that will output "12MurrayFieldRoad"

I know if I create a select query such as Field2: trim([Address1]) it only removes the non visble spaces before and after the entered data.

Does anybody have an idea how to removes the spaces before, within and after?

Your support would be much appreciated.

Thank you.
Expand|Select|Wrap|Line Numbers
  1. Dim strTest As String, intMarker As Integer
  2. Dim strNewString As String
  3.  
  4. strTest = "12 Murray Field Road"
  5.  
  6. For intMarker = 1 To Len(strTest)
  7.   If Mid$(strTest, intMarker, 1) <> " " Then
  8.     strNewString = strNewString & Mid$(strTest, intMarker, 1)
  9.   End If
  10. Next
  11.  
  12. Debug.Print strNewString
  13.  
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. 12MurrayFieldRoad
Sep 26 '07 #3
cyberdwarf
218 Expert 100+
Or, (much quicker):-
Expand|Select|Wrap|Line Numbers
  1. Replace("12 Murray Field Road"," ","")
Steve
Sep 26 '07 #4
Thanks for your responses.

Sorry but this is the first real time I have used Access97. I have never really created code and was hoping on creating this in an Access 97 Select Query pane opposed to writing it in code if possible. If not, if you can please advise where and how to write the code you provided?

On creating a New Access Query, you have the options of design view, datasheet view and SQL view.

I was hoping that once I have this new output field of data is to re-run a query on that with other fields within our database.

Thanks once again for all of your support.
Sep 26 '07 #5
Sorry for my previous reply. I have managed to get the code working.
thank you all once again for your support. I hope I can likewise do the same for you sometime.
Sep 26 '07 #6
ADezii
8,834 Expert 8TB
Or, (much quicker):-
Expand|Select|Wrap|Line Numbers
  1. Replace("12 Murray Field Road"," ","")
Steve
cyberdrarf, I don't think Replace() is supported in Access 97.
Sep 26 '07 #7
cyberdwarf
218 Expert 100+
Oooops!!

Sorry, hadn't thought of that

Steve
Sep 26 '07 #8
missinglinq
3,532 Expert 2GB
ADezii is correct; Replace() didn't come along until ACC2000!

Linq ;0)>

Sep 26 '07 #9

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Andy B | last post by:
I've tried using Trim or RTrim to strip trailing space characters from my data. When I check on the transformed data space characters are still there. We have an address table containing two...
9
by: Durgesh Sharma | last post by:
Hi All, Pleas help me .I am a starter as far as C Language is concerned . How can i Right Trim all the white spaces of a very long (2000 chars) Charecter string ( from the Right Side ) ? or how...
2
by: Alex Shirley | last post by:
HI I'm trying to iterate through all the textboxes on a webpage and trim them for spaces. i.e. If a user enters " hello world " we correct it to "hello world" So far I've come up with...
11
by: Darren Anderson | last post by:
I have a function that I've tried using in an if then statement and I've found that no matter how much reworking I do with the code, the expected result is incorrect. the code: If Not...
7
by: Sascha Herpers | last post by:
Hi, what is the difference between the trim function and the trim String-member? As far as I see it, both return the trimmed string and leave the original string unaltered. Is any of the two...
22
by: Terry Olsen | last post by:
I have an app that makes decisions based on string content. I need to make sure that a string does not contain only spaces or newlines. I am using the syntax 'Trim(String)" and it works fine. I...
4
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - How do I trim whitespace - trim/trimRight/trimLeft...
5
by: aswath | last post by:
i used the below code for trimming.. var str = " blah blah "; var trimmed = str.replace(/^\s+|\s+$/g, '') ; ...
121
by: swengineer001 | last post by:
Just looking for a few eyes on this code other than my own. void TrimCString(char *str) { // Trim whitespace from beginning: size_t i = 0; size_t j; while(isspace(str)) {
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.