Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2002
    Location
    New Zealand
    Posts
    19

    Unanswered: Test for a string in a text box inVBA

    Hi,
    Not an expert in VBA, so what I Know comes from Forums and access help.
    This may seem wild, but is it possible to test for a string.
    It is awkward to use the IsNull or the Null, as the flow of the programme, as I wrote doesn't seem to allow it.
    What I want to do is something like
    If (Me![CustomerName] = String) then
    Goto Line1
    Exit Sub

    What would the correct syntax be for the string for say a company name, I can only think of "*".
    Also how can I check for the last 2 characters of a string to be alpha
    as in case is = (Right(QuoteName,2) = String

    I Have tried "AA" to no avail and "A?"
    Its a long shot that both of these can be done.
    Thanks Inadvance
    Regards
    bill

  2. #2
    Join Date
    Feb 2002
    Posts
    403
    If (Me![CustomerName] = String) then

    If Me.CustomerName<>"" or Not IsNull(Me.CustomerName) then

    case is = (Right(QuoteName,2) = String

    Select Case (Right(QuoteName,2)

    Case is <>""

    Or soemthing like that.

  3. #3
    Join Date
    Jun 2002
    Location
    Saudi Arabia / Philippines
    Posts
    126
    Bill,

    It's me again.

    Is Customer Name a bound field? If so and if the underlying field is Text then Customer Name is a String or a Variant of type string - never really had to worry which before this. If you really want to force it to be a String then use the CStr function (read up on 'Type Conversion Functions' in the help).

    However I suspect your question may be how can you tell if the field is empty, blanks being included in the definition of empty. If I'm right then use something like

    If Isnull(Me![CustomerName]) then
    Goto <error routine>
    End if

    If Len(Trim(Me![CustomerName])) <= 0 then
    Goto <error routine>
    End if

    Anything that passes both tests will be a string with at least one non blank character.

    Testing for alphabetic characters? I know of no supplied function that does this so you need to write your own. In the good old days this was simple - you simply tested the ASCII value of the character and determined whether it was a number, a lower case alpha, an upper case alpha or a special character. Then along comes the complication of upper character sets. So if foreign languages are a possibility you need to know what upper character set is being used. Finally along comes Unicode with each character occupying two bytes. If you have Unicode enabled you need to read up on all the string functions.

    I'm intrigued as to why your code does not allow you to use Null and Isnull. Null I can understand if misused but Isnull should work on any numeric or string expression.

    Rod

  4. #4
    Join Date
    Apr 2002
    Location
    New Zealand
    Posts
    19
    Hi Rod and Thanks,
    What you find is a laymans attempt.
    I havn't imlemented your suggestions so what follows is what I had that prompted the questions.
    What I have is the following, the test for string for the last two characters
    is in the following, It relates to a switch to electronic numbering from manual records.


    Select Case Me![Quotation No:]

    Case Is < 5910
    'Do nothing
    [CustomerName:].SetFocus
    Exit Sub
    Case Is = Null
    'Do Nothing
    [CustomerName:].SetFocus
    Exit Sub
    Case Is = (Right([Quotation No:], 2) = "AA")
    'Do Nothing
    [CustomerName:].SetFocus
    Exit Sub

    End Select

    The following is the Works card which before it closes checks that certain feilds are correct and entered and stops sales reps selling things below a certain value. Agian it is in the form it was in prior to your reply.
    So here is my attemt at some logical VBA.

    Private Sub Exit_Form_Click()
    On Error GoTo Err_Exit_Form_Click


    If IsNull(Me![CustomerName:]) And IsNull(Me![Works No:]) Then
    DoCmd.close acForm, "Works Card Form"
    Else

    If (Me![CustomerName:] = "*") Then
    GoTo Line1
    Exit Sub
    Else



    If MsgBox("The Works Card Is Now active" & vbNewLine & "If You have Made a Mistake" & vbNewLine & "Click Yes To EXIT!!!" & vbNewLine & "Or No to CONTINUE!!!!", vbCritical + vbYesNo, "Cockup") = vbYes Then

    DoCmd.RunCommand acCmdUndo
    DoCmd.close acForm, "Works Card Form"
    Exit Sub
    Else
    GoTo Line1
    Exit Sub
    End If


    Line1:
    If IsNull(Me![CustomerName:]) Then
    MsgBox "Please select Customer Name from Drop Down List"
    Me![CustomerName:].SetFocus
    Exit Sub
    End If


    If IsNull(Me![Country]) Then
    MsgBox "Please enter Country From The drop down List"
    [Country].SetFocus
    Exit Sub
    End If

    If (Me![Material Cost] <= 0) Then
    MsgBox "Material Cost is required, it cannot be This Value, please re-enter correct amount.", vbOKOnly, ""
    [Material Cost].SetFocus
    Exit Sub
    End If

    If (Me![Labour Cost] = 0) Then
    MsgBox "Labour Cost is required, it cannot be $0, please re-enter correct amount.", vbOKOnly, ""
    [Labour Cost].SetFocus
    Exit Sub
    End If

    If (Me![Sell Price] <= Me![Cost Price] * 1.15) Then
    ' Display message box to inform the user that Management OK is needed for this price.
    strTitle = "Management OK is needed for this sell price"
    strMsg = "The Sell Price is incorrect. The Value is Less Than 15%!!!." & vbNewLine & "Press Yes to correct Sell Price." & vbNewLine & "Or No to Open Controls for Management to Accept Sell Price."
    If MsgBox(strMsg, vbQuestion + vbYesNo) = vbYes Then
    [Sell Price].SetFocus
    Exit Sub

    Else

    Dim Password As String, strinput As String, strPW2 As String, strMsg1 As String, strTitle1 As String
    strMsg1 = "Incorrect Password" & vbNewLine & "Please Try Again"
    strTitle1 = "Invalid Password"
    Password = DLookup("Password", "Password")
    strPW2 = "wapfu"
    Do Until intCnt = 6 Or strinput = Password Or strinput = strPW2
    strinput = InputBox("Enter Password")
    Select Case strinput

    Case Is = strPW2
    MsgBox "Entry was Successfull, Thankyou."
    Me![Special] = 1
    DoCmd.SAVE
    DoCmd.PrintOut acSelection
    DoCmd.close acForm, "Works Card Form"
    Exit Sub

    Case Is <> Password
    If MsgBox(strMsg1, vbCritical + vbYesNo, strTitle1) = vbYes Then
    intCnt = intCnt + 1
    Else
    Exit Sub
    End If

    Case Is = Password
    MsgBox "Entry was Successfull, Thankyou."
    Me![Special] = 1
    DoCmd.SAVE
    DoCmd.PrintOut acSelection
    DoCmd.close acForm, "Works Card Form"
    Exit Sub


    End Select
    Loop


    End If
    End If

    DoCmd.SAVE
    If MsgBox("If you Haven't already printed this form" & vbNewLine & "Press YES to Print, and NO to exit", vbCritical + vbYesNo, "Final Chance") = vbYes Then
    DoCmd.PrintOut acSelection
    DoCmd.close acForm, "Works Card Form"
    Else
    DoCmd.close acForm, "Works Card Form"
    Exit Sub
    End If
    End If
    End If

    Exit_Exit_Form_Click:
    Exit Sub

    Err_Exit_Form_Click:
    MsgBox Err.Description
    Resume Exit_Exit_Form_Click


    End Sub


    So you see I ended up with checking for a null feild sending the programme somewhere if it was and then rechecking tht same feild whuch if I used null or Is null would have presented me with a programming problem.

    Thanks
    Regards
    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •