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

 

[sjgreatdeals]

Thanks a bunch!

 

[jrbarnett]

Scott

Rather than email 4Mb of database back to you as I am on a dial up connection and my web mail attachment facility is rather flakey at the moment, I will tell you how to do it yourself, for future reference:

1. Open their database
2. Put a copy of my Replace2 function in it, as you did in yours earlier.
3. Open the immediate window by pressing Ctrl G
4. Type the following in the immediate window (or copy and paste) then press Enter:

DoCmd.RunSQL &quot;Update results set caption = replace2 (Caption, &quot;&quot;*&quot;&quot;, &quot;&quot;<li>&quot;&quot;)&quot;

What this means is:
Update the column &quot;caption&quot; in the table &quot;results&quot;, setting it to the result of replace2 function, using the caption column as input, replacing every asterisk (&quot;*&quot with &quot;<li>&quot; (the HTML code for a List Item).

The reason for the double quotes within the brackets is because the whole string is kept within them, and this is Access's way of determining quotes within a quoted string.

This should do it.

John

 

[sjgreatdeals]

Thank you very much for all of your help. Let me know if there is any way to give you a rating on anything for all that you have done.

Thanks,
Scott

 

[sjgreatdeals]

Sorry to bug you again, but we have another little problem. The code you gave me works great. In the database, our Names do not have the Brand in front of it with the code right next to that. I was wondering if there is any way we can do the following:

Toshiba 24AF42 24-Inch FST Pure Flat

Right now in the discount tools part, we have just
24-Inch FST Pure Flat and we need to add Toshiba from the BRAND field and 24af42 from the Code field. We need to add them in that order to. Let me know if there is any way of doing this.

Thanks,
Scott

 

[jrbarnett]

Another SQL statement for running through the immediate window:

DoCmd.RunSQL &quot;update results set caption = brand & &quot;&quot; &quot;&quot; & code & &quot;&quot; &quot;&quot; & caption&quot;

John

 

[sjgreatdeals]

Thanks alot, is there a feedback place in this forum that i can give to you?

Thanks for all of the help,
Scott

 

[jrbarnett]

Not really to give to me directly; I am doing this just to stop me from going mad while I look for a job in the totally crazy IT job market at the moment.

I'm sure there must be a feedback address somewhere.

John

 

[sjgreatdeals]

John,

I got to looking at the Caption, and when we added the brand name and then the code, the part of the end of the caption was cut off. I am not sure if this moved the caption over and deleted it as we added this. Let me know what you think.

Scott

 

[jrbarnett]

Scott,

Apologies for the delay in responding, it is only now that I have had a chance to test to see if multiline items are both updated or truncated at the end of the first line when used with my replace2 function.

I have found that the data is not truncated, but if you are looking at the table view of the data (rather than through a form with a textbox), you may only see the first line. Try pressing F2 to edit then using the arrow keys to scroll through the data.

John