Need help with "Like" in vba?

[bassman197]

I would like to open a custom dialog form if a user includes the word "Addeudum" in a text box AND the property Addendum Yes is No (False).
What I have so far is:

Private Sub Notes_AfterUpdate()
If Me.Notes.Value Like *"ddendum"* And Me.[Addendum Yes] = False Then
DoCmd.OpenForm "See Addendum Error", acNormal
Else
Exit Sub
End If
End Sub

The idea is that if a user includes the word addendum in the notes, but has not set the check box for See Addendum on the contract to "yes", the error form will open. Also, my theory is that whether the user types Addendum or addendum (either case), this will still trigger with the criteria "ddendum", but my first line of code produces an error. I've also tried
#Like "*"&"ddendum"&"*"#.
I'm sure that I'm simply not enclosing something in parenthesis or quotes, but I've never tried using Like in VBA before. Any ideas greatly appreciated!

 

[rainman89]

Private Sub Notes_AfterUpdate()[INDENT] If Me.Notes.Value Like "*ddendum*" And Me.[Addendum Yes] = False Then
[/INDENT][INDENT][INDENT] DoCmd.OpenForm "See Addendum Error", acNormal
[/INDENT][/INDENT][INDENT] Else
[/INDENT][INDENT] Exit Sub
[/INDENT][INDENT] End If
[/INDENT]End Sub

 

[bassman197]

Hey, this is getting closer - the de-bugger actually kicks in now (was just ignoring the code before).

I found that I had to refer to the check box specifically (there is an option group with two check boxes, yes and no). Here is what I tried next:

Private Sub Notes_AfterUpdate()
If Me.Notes.Value Like "*ddendum*" And Me.Check167.Value = False Then
DoCmd.OpenForm "See Addendum Error", acNormal
Else
Exit Sub
End If
End Sub

It now returns Run-time error '2427':
You entered an expression that has no value.

I does this whether the text box contains "ddendum" or not, so I'm getting some syntax wrong somewhere - it just doesn't get that first line.

 

[boblarson]

I don't believe you can use
If Me.Notes.Value Like "*ddendum*"

You would need to use INSTR

If Instr(1, Me.Notes,"ddendum") > 0 then

 

[bassman197]

You were right rainman89! I was referring to a check box in the option group, not the actual Addendum Yes property, so that was what was causing the error, not the LIKE operator. I didn't actually see your reply in time and got the following to work, but it is very similar to your solution:

Private Sub Notes_AfterUpdate()
If Me.Notes.Value Like "*ddendum*" And Me.[Addendum Yes].Value <> 0 Then
DoCmd.OpenForm "Addendum Question", acNormal
Else
Exit Sub
End If
End Sub

And thanks, Bob for the InStr idea - I've never used that, but it sounds cool - will read up on it.

Now, to include a command button in the "Addendum Question" form with the option to "change now"!

Thanks all!

 

[rainman89]

Glad you got it figured out!