Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-17-2019, 02:27 AM   #1
aziz rasul
Newly Registered User
 
aziz rasul's Avatar
 
Join Date: Jun 2000
Location: Earth
Posts: 1,736
Thanks: 20
Thanked 1 Time in 1 Post
aziz rasul is an unknown quantity at this point
Using the Replace function

I have a field where I want to replace specific values to a different value as part of an Encryption process. I then want to Decrypt the data back to what it was.

Hence if I have say the following data which forms a part number of a product

yEr456

I want to Encrypt this so that I end up with

sGb!()

I am using the following code to do this. If I use the code to replace the y to s, I use

Code:
Current.Execute "UPDATE tblTempEncryption INNER JOIN [" & strTableName & "] ON tblTempEncryption.[DATAAUTONUMBER - " & strFieldName & "] = [" & strTableName & "].[DATAAUTONUMBER - " & strFieldName & "] SET [" & strTableName & "].[" & strFieldName & "] = Replace([" & strTableName & "]![" & strFieldName & "],'y','s');"
However when the following code is run afterwards to replace any 'S' to 'C', it replaces the 's' to 'C'.

Code:
Current.Execute "UPDATE tblTempEncryption INNER JOIN [" & strTableName & "] ON tblTempEncryption.[DATAAUTONUMBER - " & strFieldName & "] = [" & strTableName & "].[DATAAUTONUMBER - " & strFieldName & "] SET [" & strTableName & "].[" & strFieldName & "] = Replace([" & strTableName & "]![" & strFieldName & "],'S','C');"
In other words my code doesn't distinguish between 's' and 'S'. Hence I end up with 'y' becoming a 'C' rather an a 's'.

__________________
Aziz

Human reproduction process - one spermatozoon, out of 100's of millions, enters the egg and is secured, the fertilized egg becomes a clot and eventually implants itself to the wall of the uterus, referred to as implantation.
Moreover We placed him as a drop (nutfah) in a safe lodging. Then We created the drop (nutfah) into a clinging clot (alaqah).
Qur’an – Surah al-Mu’minun (The Believers) 23:13-14
See also Qur'an 21:30, 21:104 regarding creation and end of the universe (and the creation of a new universe).
aziz rasul is offline   Reply With Quote
Old 07-17-2019, 02:39 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,138
Thanks: 40
Thanked 3,613 Times in 3,488 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Using the Replace function

take a look at the documentation for the replace function here https://support.office.com/en-us/art...8-e0a4ef67d181
you need to add a value to the compare parameter - vbBinaryCompare

?replace("S23","s","X",,,vbBinaryCompare)
S23
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
aziz rasul (07-17-2019)
Old 07-17-2019, 02:58 AM   #3
aziz rasul
Newly Registered User
 
aziz rasul's Avatar
 
Join Date: Jun 2000
Location: Earth
Posts: 1,736
Thanks: 20
Thanked 1 Time in 1 Post
aziz rasul is an unknown quantity at this point
Re: Using the Replace function

Many thanks

__________________
Aziz

Human reproduction process - one spermatozoon, out of 100's of millions, enters the egg and is secured, the fertilized egg becomes a clot and eventually implants itself to the wall of the uterus, referred to as implantation.
Moreover We placed him as a drop (nutfah) in a safe lodging. Then We created the drop (nutfah) into a clinging clot (alaqah).
Qur’an – Surah al-Mu’minun (The Believers) 23:13-14
See also Qur'an 21:30, 21:104 regarding creation and end of the universe (and the creation of a new universe).
aziz rasul is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Replace function, to replace ' to blank spaces mfaqueiroz Modules & VBA 2 05-30-2016 07:51 PM
[SOLVED] Replace function in vba Ben_Entrew Modules & VBA 4 02-28-2014 03:51 AM
Replace Function hycho Queries 1 01-18-2013 10:29 AM
Replace Function ? - how to replace the a 4th position null with a dash techexpressinc Queries 1 06-07-2012 05:03 AM
REPLACE function srbooth Queries 7 10-23-2007 12:06 PM




All times are GMT -8. The time now is 08:11 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World