Trim function question? 2

[sjgreatdeals]

I am having troubles using the trim function on the my caption column in the table. I am inserting captions and need to delete the spaces in between each sentence. There are over 2000 captions, and would take for ever to each at a time. Can someone let me know how i can do this. Have a little knowledge with access, not much with modules. If you can be specific.

Thank you,
Scott

 

[jrbarnett]

The trim function is used to remove excess spaces at each end of a string variable.

Thus
" Fred "
becomes

"Fred"

after trimming

There is also LTrim and RTRim which trim only the left and right hand ends respectively.

You can't use Trim to remove spaces from the middle of sentences or paragraphs. For that, you would need to use Mid$ to extract each character.


John

 

[sjgreatdeals]

Can you explain in detail how i would go about doing this. Here is one of the descriptions i need done, out of 2000.

<li> 54.6 yds (50 meters) in a dispensing box <li> Foam tape designed to be used in masking jambs, hood or deck lid openings or other areas of a vehicle which need to be sealed during the painting operation. <li> Provides a &quot;&quot;soft edge&quot;&quot; to prevent lines in the jamb areas.

I need the spaces taken out between the <li> and the sentences. If you can, let me know in detail how i should do this. I appreciate the help.

Thanks,
Scott

 

[jrbarnett]

DoCmd.RunSQL &quot;&quot;Update Table Set Caption = replace (Caption, &quot;<li> &quot;, &quot;<li>&quot&quot;&quot;

This will replace any <li> followed by a space with <li>.

Run the above statement from the Immediate window (Ctrl G)

John

 

[sjgreatdeals]

I am going to sound stupid, but do I add following

DoCmd.RunSQL &quot;&quot;Update Table Set Caption = replace (Caption, &quot;<li> &quot;, &quot;<li>&quot&quot;&quot;

to the immediate field and hit run. I don't use macros much. After i hit run it takes me to macro name. What should i name it and then what do i do?

Thanks,
Scott

 

[jrbarnett]

Go to the database. Press Ctrl G and visual basic should open. Put the cursor in the immediate window (should be at the bottom of the window) and copy and paste the code above.

You must replace the word &quot;table&quot; with the name of your database table as it appears in the database window.

This isn't really a macro, it is a one off thing to update every field in your table. It won't do it for new entries or force it to change back if you edit any at a later date.

John

 

[sjgreatdeals]

I tried that, and it is giving me a compile error. &quot;Expected: End of Statement&quot; I put that in like you told me, and added the name of the table &quot;Discount Tools Master Inventory&quot; in for the word table. Not sure how to fix this. I appreciate all of the help.

Scott

 

[jrbarnett]

DoCmd.RunSQL &quot;&quot;Update Table Set Caption = replace (&quot;[Discount Tools Master Inventory]&quot;, &quot;<li> &quot;, &quot;<li>&quot&quot;&quot;

Should do it.

John

 

[sjgreatdeals]

Is there anyway i could email you part of this database, I will just have one record in it, and add this. I am still getting this error. I hate to bug you like this.

Thanks,
Scott

 

[jrbarnett]

Mail it to j.barnett @ iname.com (without the spaces).

John

 

[sjgreatdeals]

I sent the database to you.

Thanks Again,
Scott

 

[jrbarnett]

Hi Scott,

Sorry - I got my quote marks in a muddle. The following should do it.

DoCmd.RunSQL "Update [discount tools master inventory] Set 
Caption = replace (Caption, &quot;&quot;<li> &quot;&quot;, &quot;&quot;<li>&quot;&quot;)&quot;

John

 

[sjgreatdeals]

I put this in and now I am getting an error. It does not recognize replace. We also have another database that one of our distributor sent us. All of the description will not upload into our store right. We pretty much need the same thing done, but this one you can't even use the replace, and change the &quot;*&quot; to an <li>, things like that. It will put an <li> for the entire description. I would appreciate it very much if you could look at a sample of this and be able to help me out. We are exporting these into txt. and uploading it by mass into our yahoo store. I really appreciate your help. Let me know if I can send the sample database to you.

Thanks,
Scott

 

[jrbarnett]

Which version of access are you using?

John

 

[sjgreatdeals]

Access 2000

 

[jrbarnett]

I am using 2002. I will try and roll a version of the Replace function for 97/2000 as it shouldn't be too difficult and has many useful applications besides this one.

John

 

[sjgreatdeals]

Would you mind looking at the other distributor's database that I talked about earlier. I can either send it to you in excell form or access. Let me know, and thanks for all or your help.

Thanks,
Scott

 

[jrbarnett]

One replace function.

go to the modules tab, click new, copy and paste this below into it, click save and save it as modReplace.

Public Function Replace2(Expression As String, Find As String, Replace As String, Optional Start As Long = 1) As String
' Author: John Barnett, handle jrbarnett
' In response to: thread700-565966 Date: 4th June 2003
' Purpose: A Find and replace VBA function for Access 97 and 2000, which didn't have the Replace() function of 2002(XP).
' although it does work in 2002 as well.  It implements the mandatory functionality of the 2002 function plus the optional start, but not the Count of replacements and Binary Compare method.
' It has the same function header, so can be dropped in as a replacement.

Dim strResult As String ' variable to store result
Dim intPosition As Integer ' variable to store current position in Expression
Dim intStartPos As Integer ' variable to store current starting position within 'expression'
Dim intReplaceCount As Integer

If IsMissing(Start) Then
    intStartPos = 1 ' start position not supplied; so set it to beginning
Else
    intStartPos = Start ' otherwise set it and...
    strResult = Left$(Expression, Start - 1) ' start by copying over first chars before start position
End If

intPosition = InStr(Expression, Find) ' locate first occurrence of 'find' data
' Remember that intPosition will = 0 if no occurrences are found.

Do While intPosition > 0
    strResult = strResult & Mid$(Expression, intStartPos, (intPosition - intStartPos)) ' copy everything over from it that hasn't been copied yet
    intStartPos = intPosition + Len(Find) ' increase the pointer by the length of the "to find" data so it won't find the current occurrence
    strResult = strResult & Replace ' add the replacement data
    intPosition = InStr(intPosition + Len(Find), Expression, Find) ' and reset the position for the new start point
Loop

' In case we aren't changing the very last part of the string...
If intStartPos < Len(Expression) Then
    ' copy over rest to result
    strResult = strResult & Mid$(Expression, intStartPos)
End If
Replace2 = strResult ' and return a value
End Function

Your SQL code to update the database then becomes:

DoCmd.RunSQL "Update [discount tools master inventory] Set 
Caption = replace2 (Caption, &quot;&quot;<li> &quot;&quot;, &quot;&quot;<li>&quot;&quot;)&quot;

I can't call it &quot;replace&quot; because I am using 2002 and it would conflict with the built in name, but you could rename it if you wished. It doesn't support all the features of the original either, but enough for 99% of all purposes.
I will create an updated version of my Tek-Tips library and post it on my website later today or tonight.

John

 

[sjgreatdeals]

Thanks, that worked! Do you mind if I send you the other database sample? Either in Excell or Access.

Thanks,
Scott

 

[jrbarnett]

Send it too me (same address as before) and I will see what I can do.

John