Using Replace Function with a table of Variables (Access 2003 / VBA)

Topic

New Reply

Hello…
I’m working on a VB script that when ran would take a field (LDesc – memo) in table T_Agile and move the data to an alternate table T_AgileRSC (same structure). This script would also replace certain characters or character strings in the LDesc field that are defined in another table (T_ChrRepl – includes SoughtChar, and ReplaceChar along with if it is Ascii code or Character (boolean)). The T_ChrRepl–>SoughtChar and ReplaceChar can include multiple replacement strings seperated by comma’s (i.e. SoughtChar Chr(162),Chr(163) would be replaced by ReplaceChar Chr(13),Chr(10)). I can get everything to work correctly when using character strings, but can’t when using Ascii code. It treats the Ascii code as a literal.

Below is the line of code I’m having problems with. My question is… Is their anyway to have the below SC_String use Chr(162) & Chr(163) instead of indicating “Chr(162) & Chr(163)” quoted?
If this isn’t enough information, I can attach the complete code for reference.

rstAgileRSC![LDesc] = Replace(rstAgileRSC![LDesc], SC_String, RC_String)
RC_String and SC_String is a string compelated from a loop further up in the script.

Thanks in advance for any help.

Reply | Quote

Replies

What do the SoughtChar and ReplaceChar fields in T_ChrRepl contain? Numbers such as 162 or strings such as Chr(162)? And do these fields contain single values or multiple values?

Reply | Quote

Hans,

They contain only the number of the ascii character. (“162”, not “Chr(162)”)
These can contain single values or multiple values seperated by comma’s. If the SoughtChar had multiple values, it would concatinate and replace it with the concatinated ReplaceChar (ex. SoughtChar=65,66,67; ReplaceChar=88,89,90; IsAscii field is checked… when the script would run, it would replace ABC with XYZ in the LDesc field.

(The current code I have is at home. I will try to attach it this evening.)

Thanks,
Drew

Reply | Quote

Can you post the code that assembles SC_String and RC_String?

Reply | Quote

Hans,

Attached is the entire script.

Regards,
Drew

Reply | Quote

Instead of

“Chr(” & CSC_String & “)”
and
“Chr(” & CRC_String & “)”

use

Chr(CSC_String)
and
Chr(CRC_String)

BTW, you can use the Split function to split the string in comma-delimited parts. Much more efficient. Look it up in the online help.

Reply | Quote

The CSC_String and RSC_String in the script is used to compile a set of ascii characters. The SC_String uses CSC_String to compile this set one character at a time.
I haven’t tried this in VB, but did as a test in an Access query and when ran gave me #error.

Reply | Quote

I think we’d have to see the database if you want more help. See post 401925 for instructions on how to create a stripped down copy of your database.

Reply | Quote

Here ya go…

I appreciate your help!

Reply | Quote

Does the attached version of AgileRemoveSpecialChar do what you want?

Reply | Quote

Works Great!!! I see what I did wrong.
Thank you

Reply | Quote

Hans,

One other thing. You mentioned in a prior thread that I should look into useing the split function. I have, but can’t seem to get it to work. I always get a ‘Type Mismatch’ error.
Could you give me an example on how to use this (preferrably using the code I already have out there)?

Thanks,
Drew

Reply | Quote

Try the attached version.

Reply | Quote