Replace function

[Khushalay]

Hi friends

Want to know that I have used Replace function in the form so when the user presses enter, the digits change to a particular code. But since the report is only for me, I want the original numbers in the report rather than the coded value. Is it possible?

Thanks in advance.

 

[Khushalay]

Because I need to calculate sum etc and I cant do it on letters so I need to bring it back to the original digits so that I can perform arithmetic operations on it.

 

[CJ_London]

your post is not clear, suggest you provide an example of what you want. Anything is possible....

 

[Khushalay]

I used a Replace function in the afterUpdate event of a form which replaces 1 with A, 2 with B and so forth.
So if I enter 12 and as soon as I press enter, 12 changes to AB. Which is fine, sthat is required.
Now for the report, I dont want it to show AB, I want it to show the original 12. Is it possible?

 

[CJ_London]

yes - just use the replace function to reverse the process, plus a val function to convert the text '12' to a numeric 12

 

[Khushalay]

yes - just use the replace function to reverse the process, plus a val function to convert the text '12' to a numeric 12

I want this in report only. So where to use the replace function. Please elaborate a bit.

Thanking you

 

[Khushalay]

Private Sub Ref_AfterUpdate()
Dim LResult As String
Dim L1Result As String
Dim result As String
result = Me.Ref.Value
LResult = Replace(result, "1", "E")

L1Result = Replace(LResult, "2", "Q")
L2Result = Replace(L1Result, "3", "B")
L3Result = Replace(L2Result, ".", "/")
L4Result = Replace(L3Result, "5", "L")
L5Result = Replace(L4Result, "4", "A")
L6Result = Replace(L5Result, "6", "K")
L7Result = Replace(L6Result, "7", "S")
L8Result = Replace(L7Result, "8", "I")
L9Result = Replace(L8Result, "9", "M")
L0Result = Replace(L9Result, "0", "X")

Me.Ref = L0Result


End Sub

This is the code I have used to Replace and this is working perfectly fine. Now I want the digits back in report.

 

[arnelgp]

add an unbound textbox, that will hold the converted text.
this is the one you will display and not the Ref field.
make the Ref field Visible Property to False, to hide it.
use the format event of the detail section of your report.
this technique will only be available in Print Preview.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
dim s as string
s = Replace(Me.ref, "E", "1")
s= Replace(s, "Q", "2")
s= Replace(s, "B", "3")
s= Replace(s, "/", ".")
s= Replace(s, "L", "5")
s= Replace(s, "A", "4")
s= Replace(s, "K", "6")
s= Replace(s, "S", "7")
s= Replace(s, "I", "8")
s= Replace(s, "M", "9")
s= Replace(s, "X", "0")
Me.yourNewTextbox = s

End Sub

 

[Khushalay]

This seems to be a good idea. So u mean to say I make an unbound field on the form and make it invisible and then in the report I display that field instead of the original Ref field??

 

[arnelgp]

almost, but not quite.
you add an unbound textbox.
drag it where the Ref field is located.
you make the Ref Invisible (setting its Visible property to False).

 

[Khushalay]

No, I need to make the Ref field visible on form n the unbound invisible on form. Because I dont want the data entrants to see the pricing. ref is actually pricing so we have coded it.

 

[Khushalay]

The data entrants will enter digits in Ref and as soon as they press enter, it will be converted to the code. Now how can I copy the exact same number automatically to the unbound textbox Because I dont want them to enter twice.

 

[arnelgp]

whichever you like, if it will satisfy your report.

 

[Khushalay]

But how to achieve that. Where can I get the visible property and how to copy the data there automatically.
I liked ur idea but being a novice, need a little more detail plz. Thanks

 

[Khushalay]

Ok I got the visible property and set it to No for the new unbound box. Now i would need to copy the entry automatically to the unbound box. How to achieve this?