Validation Rule

[Michael]

Hi, Im pretty new to coding in visual basic, so I think this may have an
easy solution

I am setting up a basic validation rule for a form I have designed. The code
(so far) is as follows

Private Sub btn_close_Click()
On Error GoTo Err_btn_close_Click

'VALIDATION RULES ----------------------------------------------------

If IsNull(Me![comments]) And [special_conditions] = "y" Or "Y" Then
MsgBox ("Comments cannot be null if there are special conditions")

'VALIDATION RULES ----------------------------------------------------

Else: DoCmd.Close
End If

I keep getting a type mismatch error which I am sure has to do with the "y"
or "Y" but I am not sure what

Any help would be great

Thanks

 

[Linq Adams via AccessMonster.com]

In Access VBA the correct syntax for

If IsNull(Me![comments]) And [special_conditions] = "y" Or "Y" Then

would have to be

If IsNull(Me![comments]) And ([special_conditions] = "y" Or
[special_conditions] = "Y") Then

You can't simply use

[special_conditions] = "y" Or "Y"

But in point of fact, this is not really necessary at all. Because Access VBA
is ***not*** Case Sensitive, it sees "y" and "Y" as one and the same, so that
all you really need is

If IsNull(Me![comments]) And [special_conditions] = "y" Then

 

[Chris O'C via AccessMonster.com]

You're getting a type mismatch because of your "or" statement. It needs an
algebraic construct: a=b, a>b, a<b, etc. Yours is the string "Y" which is
equivalent to just stating "a" without comparing it to another variable.

Use this code instead:

If IsNull(Me.comments) And Me.special_conditions = "y" Then
MsgBox ("Comments cannot be null if there are special conditions")
Else
DoCmd.Close
End If

Chris
Microsoft MVP

Hi, Im pretty new to coding in visual basic, so I think this may have an
easy solution

I am setting up a basic validation rule for a form I have designed. The code
(so far) is as follows

Private Sub btn_close_Click()
On Error GoTo Err_btn_close_Click

'VALIDATION RULES ----------------------------------------------------

If IsNull(Me![comments]) And [special_conditions] = "y" Or "Y" Then
MsgBox ("Comments cannot be null if there are special conditions")

'VALIDATION RULES ----------------------------------------------------

Else: DoCmd.Close
End If

I keep getting a type mismatch error which I am sure has to do with the "y"
or "Y" but I am not sure what

Any help would be great

 

[Michael]

Works great
Thanks heaps