Access string manipulation.

[SpartanX]

I've created a form with its entire field locked. Purpose of the form is to copy certain fields w/o someone accidently changing its information from the table. How do I create an expression that counts the number of "-" and then inserts characters to the front of the string? Also, do I need to create and unbound text field in the form so that it can get manipulated by the expression?


if string F1DS1SDFCFA = "250-2-1"

Count the number of "-", if the number = 2, concatenate "VT1-" to the front of the string. If else send the string along w/o any changes.

string F1DS1SDFCFAu = "VT1-250-2-1"

 

[dhookom]

This seems like a business rule that can and will change. I would not create an expression in a query or control source. Consider creating a small function in a standard module like:

Public Function FixString(strText as String) as String
  If Len(strText)-Len(Replace(strText,"-","")) = 2 Then
    FixString = "VT1-" & strText
   Else
    FixString = strText
  End If
End Function

Save the module with the name like "modBusinessCalcs".

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

hi,

It seems to me that you are updating the field named F1DS1SDFCFA. Strange, as this field name looks more like a DATA value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}