Set Value of Control based on result of Select Case

[jeran042]

Good afternoon all,

I am hoping someone can help me with the very beginnings of an idea.
I am trying to test the value in a form control, [POLICY], to see if it begins with "R", "R0", or 0. If it contains any of these 3 variable, I want to strip the characters away, and leave the remainder of [POLICY].

Here is what I have (very basic) for code:

Private Sub Form_Open(Cancel As Integer)
'strip away "R", "R0", or 0 from the form control me.POLICY
'Set the value of Me.txtTEST with result

Select Case Me.POLICY

    Case Left([POLICY], 1) = "R"
        Me.txtTEST.value = Mid([POLICY],3,Len([POLICY])

    Case Left([POLICY], 2) = "R0"
        Me.txtTEST.value = Mid([POLICY],2,Len([POLICY])

    Case Left([POLICY], 1) = 0
        Me.txtTEST.value = Mid([POLICY],2,Len([POLICY])

    Case Else
    'If none of the above are TRUE, give full value of me.[POLICY]
        me.txtTEST.value = Me.[POLICY].value
        
End Select
'Set the value of me.txtTEST with result of CASE Statement
    Me.txtTEST = result

End Sub

What I am trying to accomplish is a DLookup on a subform. The problem is, in one table the [POLICY] is a data type "Number" and in another table the data type is "Short Text".

I should mention I was able to get the desired result with the formula:

=IIf(Left([POLICY],1)="R",Mid([POLICY],3,Len([POLICY])),IIf(Left([POLICY],1)=0,Mid([POLICY],2,Len([POLICY])),[POLICY]))

Here is what the DLookup looks like:

=DLookUp(Replace("NOTES",Chr(13)," "),"tblInvoiceLog","[VOUCHER_NUMBER]= " & [txtTEST] & "")

NOTE: the Replace is used to strip away a carriage return in the [NOTES] field. And as of yet this is not working correctly either.


Any help or suggestions are welcome and appreciated,

 

[Ranman256]

Change it to:
Select Case True.

 

[BigHappyDaddy]

Why not just two if statements?

If Left(Me.POLICY,1) = "R" then Me.POLICY = Right(Me.POLICY,len(Me.POLICY)-1)
if Left(Me.POLICY,1) = "0" then Me.POLICY = Right(Me.POLICY,len(Me.POLICY)-1)

This will remove all occurances of any leading "R", "R0" or "0".

 

[arnelgp]

firstly, you should make POLICY field in both tables consistent (i suggest short text so you can have mixed letters and numbers on them).
second, you use the Form's Load event and not the Open event.
Form''s controls (except the subform) have no values when in Open event.

you can create a Public Function for the replacement:

Public Function fncReplace(byval thisPolicy As Variant) As String
Const Pattern As String = "^(R0|R|0)"
thisPolicy = thisPolicy & ""
With CreateObject("VBScript.RegExp")
     .Pattern = Pattern
     .Global = True
     .IgnoreCase = True
     fncReplace = Trim(.Replace(thisPolicy, ""))
End With
End Function

so the expression on the query or vba:

[fieldToReplace] = fncReplace([fieldToReplace])

 

[jeran042]

firstly, you should make POLICY field in both tables consistent (i suggest short text so you can have mixed letters and numbers on them).
second, you use the Form's Load event and not the Open event.
Form''s controls (except the subform) have no values when in Open event.

you can create a Public Function for the replacement:

Public Function fncReplace(byval thisPolicy As Variant) As String
Const Pattern As String = "^(R0|R|0)"
thisPolicy = thisPolicy & ""
With CreateObject("VBScript.RegExp")
     .Pattern = Pattern
     .Global = True
     .IgnoreCase = True
     fncReplace = Trim(.Replace(thisPolicy, ""))
End With
End Function

so the expression on the query or vba:

[fieldToReplace] = fncReplace([fieldToReplace])

I couldn't agree more, the problem is that one table is a linked table, and I do not have ownership of it.

Secondly, can you help me a little more with your suggestion.
So I would put this function in the parent forms On Load event, or the subforms On Load event. Next, would I put (in my case) the code below in the function?

[me.txtTEST] = fncReplace([me.txtTEST])

and =fncReplace([me.txtTEST]) in the controls source?

 

[arnelgp]

why don't we just do it once using an Update Query when the form opens/load (whichever event you like).

private sub form_open(cancel as integer)
currentdb.execute "update [yourtable] set [policy]=fncReplace([policy];"
end sub