Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2006
    Posts
    110

    Question Unanswered: Multiple If Not Statements

    Not sure if the title is correct, but what i am trying to do is allow only the word none and the - (Dash) and numeric values in my field. What I have isnt working, I know how to write it for just "none" and not isnumeric, but when I add the and "-" it doesnt want to work. Any help is greatly appreciated.. thanks
    Chris

    Private Sub TRACKING1_BeforeUpdate(Cancel As Integer)
    If Not (Me.TRACKING1) = "none" And "-" And Not IsNumeric(Me.TRACKING1) Then
    MsgBox "This Field Must Be NONE or a Numeric Entry."
    Cancel = True
    End If
    End Sub

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If I understand you correctly, you only want your field to hold one of three values, either "none" or "-" or a number. If this is correct this should do the job.

    Be sure you get all of the bold line when you copy the code.

    Code:
    Private Sub TRACKING1_BeforeUpdate(Cancel As Integer)
    
      If (Me.TRACKING1.Value <> "none") And (Me.TRACKING1 <> "-") And Not IsNumeric(Me.TRACKING1) Then
    
         MsgBox "This Field Must Be NONE or a Numeric Entry."
         Cancel = True
      End If
    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    I suggest:

    If IsNumeric(Me.TRACKING1) OR Me.TRACKING1 = "none" Or Me.TRACKING1 = "-" then
    'nothing
    Else
    MsgBox ".........................."
    Cancel = True
    End If

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Obviously, both our solutions work, MStef-ZG, but does an If...Else...End If structure perform better than a simpler If...End If structure? Or is OR preferable to AND? I'm being serious here! I've only ever had a 100 level intro class to this stuff, and the Prof simply read from the text!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Oct 2006
    Posts
    110
    thanks guys, that is working so far, but I want to include the - (dash) anywhere in the field, like the value entered is 157-159 or 15444-15449.
    How would I go about that, like the field contains a dash?
    Thanks

    If (Me.TRACKING1.Value <> "none") And (Me.TRACKING1 <> "-") And Not IsNumeric(Me.TRACKING1) Then
    MsgBox "This Field Must Be NONE or a Numeric Entry."
    Cancel = True
    End If

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Questions:

    At anytime will the field only contain a dash?

    If the field contains "numerical" data, will it always contain a dash, or will it sometimes be just numbers?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Oct 2006
    Posts
    110
    It will never contain just a dash, the dash will be used when the field contains more than 1 tracking number,like 4210 thru 4215, entered as 4210-4215.

    The field will either contain 1 tracking number, like "4210" or it will contain more than 1, such as "4210-4215", or the text "none".

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    OK, cmays637, this should do it! It checks for "none" of course (and BTW, it IS NOT case sensitive) and the uses the Replace function to remove the dash, then checkis the resultant string to see if it's numeric. This will also work for Tracking numbers that contain more than one dash.

    Private Sub Tracking1_BeforeUpdate(Cancel As Integer)

    If (Me.Tracking1.Value <> "none") And Not IsNumeric(Replace(Me.Tracking1, "-", "")) Then
    MsgBox "This Field Must Be NONE or a Numeric Entry."
    Cancel = True
    End If

    End Sub
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Oct 2006
    Posts
    110
    Thanks Missinglinq, thats working great.

Posting Permissions

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