If statement in VBA that returns messages based on data entered

[joflow21]

Hi,

I have two text boxes for which I am trying have show and error message when the proper data is not entered. One text box is for PLANID and the other is for Date. I would like the PLANID to be checked 1st. The PLANID needs to be either 7 digits or 10. If it is not, the message would be "Please enter a valid PLANID". If it is 7 or 10 digits, then it should check the Date text box. If it is blank, the message would be "Please enter a valid Date". If they are both blank, the PLANID message should appear 1st. Below is what I have written. The problem that I am having is that when the PLANID is 10 digits and the date is blank, the "Please enter a valid PLANID" message is showing rather than the "Please enter a valid Date" message.

Can someone please let me know what I am doing wrong? Thanks.

Private Sub Btn_Refresh_Data_for_One_Plan_Click()


Me.txtboxPLANID.Value = UCase(Me.txtboxPLANID.Value)


If ((Len(Me.txtboxPLANID.Value) = 7 And Len(Me.txtboxPLANID.Value) = 10) And (Not IsNull(Me.txtboxPLANID.Value)) And (Not IsNull(Me.TxtboxDate.Value))) Then


Dim stDocName As String

stDocName = "Mcr_RUN_MATCH_DIFFERENCES"
DoCmd.RunMacro stDocName
Else
If ((Len((Me.txtboxPLANID.Value) < 7) And Len((Me.txtboxPLANID.Value) > 10)) Or IsNull(Me.txtboxPLANID.Value)) Then
InvalidPlanIdMsg
Else

If ((Len((Me.txtboxPLANID.Value) = 7) Or Len((Me.txtboxPLANID.Value) = 10)) And IsNull(Me.TxtboxDate.Value)) Then
InvalidDateMsg
End If
End If
End If

End Sub
Private Sub InvalidDateMsg()
MsgBox ("Please enter a valid Date!")
Me.TxtboxDate.SetFocus
End Sub
Private Sub InvalidPlanIdMsg()
MsgBox ("Please enter a valid Planid!")
Me.txtboxPLANID.SetFocus
End Sub

[ItsMe]

The following will not work because of the AND operator.

If ((Len(Me.txtboxPLANID.Value) = 7 And Len(Me.txtboxPLANID.Value) = 10) And (Not IsNull(Me.txtboxPLANID.Value)) And (Not IsNull(Me.TxtboxDate.Value))) Then

Same thing here. How is the field going to be less than 7 AND greater than 10 at the same time?

If ((Len((Me.txtboxPLANID.Value) < 7) And Len((Me.txtboxPLANID.Value) > 10)) Or IsNull(Me.txtboxPLANID.Value)) Then

Maybe something like this

If ((Len((Me.txtboxPLANID.Value) < 7) or Len((Me.txtboxPLANID.Value) > 10)) or IsNull(Me.txtboxPLANID.Value)) Then
MsgBox "This is not a valid ID"
Else
MsgBox "This value meets the criteria"
End If

[ItsMe]

I Just now edited my post

[joflow21]

I do see that mistake. I have changed the Or and And so many times that I didn't realize where I left it. I did correct this and I am having the same problem. I have the code below and I am entering a 10 digit entry in the PLANID field and leaving the date field blank and I am still getting the PLANID message rather than the Date message. Why would this be?

Else
If ((Len((Me.txtboxPLANID.Value) < 7) Or Len((Me.txtboxPLANID.Value) > 10)) Or IsNull(Me.txtboxPLANID.Value)) Then
InvalidPlanIdMsg
Else

If ((Len((Me.txtboxPLANID.Value) = 7) Or Len((Me.txtboxPLANID.Value) = 10)) And IsNull(Me.TxtboxDate.Value)) Then
InvalidDateMsg

[ItsMe]

If you want custom messages for different validations you will need to seperate out the validations....

If Len(Me.txtboxPLANID.Value) >= 7 And Len(Me.txtboxPLANID.Value) <= 10 Then
'Things look ok so far
'No need to do anything here
Else
Me.txtboxPLANID.SetFocus
MsgBox "InvalidPlanIdMsg"
Exit Sub
End If
'Now check for null
If IsNull(Me.TxtboxDate.Value) Then
Me.TxtboxDate.SetFocus
MsgBox "InvalidDateMsg "
Exit Sub
End If

[joflow21]

It is still returning the wrong error message. The PLANID entered is 10 digits and the date is blank and the message it is returning is that the PLANID is not valid.

There are two fields that need to be validated when one button is clicked. The first field is the PLANID and the other is the Date. If the PLANID is 7 or 10 digits and the date is not blank, then run the macro. If the PLANID is not 7 or 10 digits, or the date is blank, then return the invalid PLANID message. If the PLANID is 7 or 10 digits and the date is blank, then return the invalid date message. I can get the code to work for every scenario except when the PLANID is correct and the date is blank.

This is the code I have at this time:
Private Sub Btn_Refresh_Data_for_One_Plan_Click()
Me.txtboxPLANID.Value = UCase(Me.txtboxPLANID.Value)


If ((Len(Me.txtboxPLANID.Value) = 7 Or Len(Me.txtboxPLANID.Value) = 10) And (Not IsNull(Me.TxtboxDate.Value))) Then


Dim stDocName As String

stDocName = "Mcr_RUN_MATCH_DIFFERENCES"
DoCmd.RunMacro stDocName
Else

If (Len((Me.txtboxPLANID.Value) < 7) Or Len((Me.txtboxPLANID.Value) > 10)) Then
InvalidPlanIdMsg
Exit Sub
End If

If IsNull(Me.txtboxPLANID.Value) Then
InvalidPlanIdMsg

Exit Sub
End If

If ((Len((Me.txtboxPLANID.Value) = 7) Or Len((Me.txtboxPLANID.Value) = 10))) Then
Exit Sub
End If

If IsNull(Me.TxtboxDate.Value) Then

InvalidDateMsg
Exit Sub
End If
End If

End Sub


Private Sub InvalidPlanIdMsg()
MsgBox ("Please enter a valid Planid!")
Me.txtboxPLANID.SetFocus
End Sub
Private Sub InvalidDateMsg()
MsgBox ("Please enter a valid Date!")
Me.TxtboxDate.SetFocus
End Sub


Please, any help or suggestions would be greatly appreciated. Thanks.

[ItsMe]

I copied the code from post #5 and pasted it into a blank Test DB. It worked fine for me "Out of the Box".

LengthTest.zip

Here is the database I created to test the code in Post #5

[joflow21]

I used your code from post #5 and I changed it to =7 Or =10 rather than >=7 And <=10 (since they can only be 7 or 10) and it works now. Thanks again for all of your help.

[ItsMe]

OK then, I was thinkling it was a range between 7 and 10 characters. Glad you got this one knocked. I got a little bonkers and typed the wrong combination a few times myself.

[ItsMe]

When you get a chance try "acFormatHTML"Post back if it works or not. If not, I will post the other code so you can get it formatted in HTML.