Detect if a character is within a String

[Bill Bisco]

Hi everyone,

I am trying to solve the problem in Access 2003 of users wanting to type in a Percentage. I am aware of Allen Browne's tip to Enter a Value as a percent at http://www.allenbrowne.com/casu-16.html

However, his example requires setting the Afterupdate Event to: =MakePercent([Text23]) when I already have VBA Code in my AfterUpdate Event.

Basically I'd like my Code to work like this:

        If txtProposedMix.Value Contains "%" character Then
            NewValue = OldValue
        Else
            NewValue = OldValue / 100
        End If

Any help would be sincerely appreciated,
Bill

 

[gemma-the-husky]

i dont think you can do that

the value is entered either as

0.01, or as 1%, but is STORED as 0.01

the problem is someone just entering 1, I think, which is treated as 100%

---------
the easiest way is in the beforeupdate update

ie

if mypercent>=100% then
ask whether entry correct ...
end

 

[Bill Bisco]

Thanks gemma-the-husky,

The problem seems to be twofold because if the user leaves the % at the end, then the user can simply type the percentage value directly, i.e. (99 for 99%).

However, if the user enters their value without the % at the end, what they type gets interpreted as a decimal (i.e. 99 as 9900%).

Is there another way besides a pop up box? I can see my users getting confused when they typed in 99 and it asked them if they're sure they want 9900%.

Surely someone else has solved this problem differently than Allen.

Any help is appreciated.

Sincerely,
Bill

 

[Atomic Shrimp]

I don't understand what it is that Allen Browne's proposed solution isn't doing for you. (his code only produces a message box on error)

 

[Atomic Shrimp]

        If txtProposedMix.Value Contains "%" character Then
            NewValue = OldValue
        Else
            NewValue = OldValue / 100
        End If

     If Not IsNull(txt) Then
          If InStr(txt.Text, "%") = 0 Then
                txt = txt / 100
          End If
     End If

There really isn't all that much difference

 

[Bill Bisco]

I'm just trying to code this without using the function, and I coudn't get it to work.

I tried:

        If InStr(Me.txtProposedMix.Value, "%") = 0 Then
          NewValue = OldValue / 100
        Else
           NewValue = OldValue
        End If

Any help would certainly be appreciated.

Sincerely,
Bill

 

[Atomic Shrimp]

Fair enough. Is it incompatible with something the code already in your AfterUpdate event is trying to do? If not, could it not just be incorporated there?

I think all you need is something like:

        If InStr(Me.txtProposedMix.Value, "%") > 0 Then
          Me.txtProposedMix.Value = val(Me.txtProposedMix.Value) / 100
[COLOR="gray"]        Else
           'do nothing[/COLOR]
        End If

That way if they type in 99%, it gets converted to 0.99. If they type in any number without the %, it just gets left alone (the lines in grey just aren't needed at all).

 

[Bill Bisco]

Dear Mike,

I don't think there's anything incompatible with the Afterupdate event and my current code. If there's a way to properly call that function within the code, and if that would work that would be fine (I failed at calling the function).

Thanks for your suggested code. I've tried it, however, Access always gives an output of 0 whether the % is included in the textbox or not.

As always, any help is appreciated.

 

[jdraw]

Bill,

Why not post your complete AfterUpdate event code?

 

[Bill Bisco]

Sure,

Private Sub txtProposedMix_AfterUpdate()

On Error GoTo Err_cmdFirst_Click

    DoCmd.SetWarnings True

    Dim qryName As String
    Dim qryName2 As String
    Dim qryName3 As String
    Dim qryName4 As String
    Dim strQryName As String
    Dim strQryName2 As String
    
    Dim OldValue As String
    Dim NewValue As String
    
    qryName = "qupdPropMix%Proposed"
    qryName2 = "qupdPropMix%Current"
    
    strQryName = "qryProcessSelectProposed"
    strQryName2 = "qryProcessSelectCurrent"

    NewValue = OldValue / 100
    
    Forms!frmProcessSelectSingle!frmProcessSelectStationOptions.Form!txtProposedMix.Value = NewValue
    
    DoCmd.RunCommand acCmdSaveRecord

    DoCmd.SetWarnings False
    
    If Forms!frmProcessSelectSingle![frmProcessSelectSingleSubform].Form.RecordSource = strQryName Then
    
        DoCmd.OpenQuery qryName

    ElseIf Forms!frmProcessSelectSingle![frmProcessSelectSingleSubform].Form.RecordSource = strQryName2 Then
    
        DoCmd.OpenQuery qryName2
        
    End If
    
    DoCmd.SetWarnings True

Exit_cmdFirst_Click:
    Exit Sub

Err_cmdFirst_Click:
    MsgBox Err.Description
    Resume Exit_cmdFirst_Click

End Sub