Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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?

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Can you post the code that assembles SC_String and RC_String?

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Hans,

    Attached is the entire script.

    Regards,
    Drew

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

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

  9. #9
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Here ya go...

    I appreciate your help!

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Does the attached version of AgileRemoveSpecialChar do what you want?

  11. #11
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

  12. #12
    2 Star Lounger
    Join Date
    May 2002
    Location
    Quincy, Illinois
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Try the attached version.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •