VBA change characters. Use Replace or Instr

bobdydd

New member
Local time
Today, 00:28
Joined
Oct 23, 2012
Messages
5
Hi

I have a field called txtTransactionNumber which currenty has
16 numbers in it.
I am trying to find a way to replace the first 12 numbers with an asterisk *
and just leave the last 4 showing normally.

so that 1234 5678 9123 4567
will become **** **** **** 4567

I am trying the following

Private Sub Form_Current()
Me.txtTransactionNumber = Replace([txtTransactionNumber], , )
End Sub

But I'm not sure where to go from there

Can anyone help
Best Regards
 
Are you really trying to replace the numbers so you don't have the others at all?

If so

Me.txtTransactionNumber = Format(Left(Me.txtTransactionNumber, 15), "**** **** ****") & Right(Me.txtTransactionNumber, 4)

But it would probably be better as an update query and do it once instead of each time it gets to a record. And then also update in the form's Before Update event if it is a new record (If Me.NewRecord Then...etc.).

But if you just want to display it as such then set the control source of the control to
= Format(Left([TransactionNumber], 15), "**** **** ****") & Right([TransactionNumber], 4)
assuming that the field name is TransactionNumber and the text box was named differently as txtTransactionNumber. But if txtTransactionNumber is the actual name of the field too then use the same as the control source I put but change the name from TransactionNumber to txtTransactionNumber (but you don't use ME outside of VBA so that is why that is gone).
 
Thanks I will give that a try
 

Users who are viewing this thread

Back
Top Bottom