What is instead IN statement in VBA?

[Ishim684]

Dear Access experts, excuse me if my question is very simple, but I am entirely new to VBA.

I need to write an IF... Then... Else statement where the conditions run the check whether a certain value is found in a table. Something like:

If strString is IN [tblTable]![Field] Then

But there is no IN statement in VBA.

What is the common practice for executing such kinds of tasks?

 

[pbaldy]

If the string is anywhere in the table? Depends on what your actual goal is, but I'd probably open a recordset on an SQL statement that found the desired record(s). Then test the recordset:

If Not rs.EOF Then

would indicate that the recordset found records mathing the criteria.

 

[sneuberg]

We use DCount for that. For example

If DCount("*", "[tblTable]", "[Field] = '" & strString & "'") > 0 Then

but if there is any chance strString could have a single quote in it, e.g., O'Malley then

If DCount("*", "[tblTable]", "[Field] = '" & Replace(strString, "'", "''") & "'") > 0 Then

Both assume [Field] is a text field.