Help with InStr function

[Kenny Louden]

I need some help with 'converting' an Excel FIND function using the InStr in
Access.
Someone gave me the following formula that they currently use in an Excel
file that I'm converting over into Acces, and for the life of me can't get
the InStr function to produce the same results

In the Exel file, they have the following formula in a column that displays
a Yes or No:

IF(LEN(PROGRAM)>6,"No",IF(ISERROR(FIND("COPY",UPPER(PROG_DESC))),"Yes","No")).

Basically the logic starts with if the length of the value in the PROGRAM
cell is greater than 6 characters, the column displays a "No"

If the length is less than or equal to 6 characters, then it checks the
PROG_DESC column to see if it contains the word "COPY"

If return results is the #VALUE, which in turn is handled by the ISERROR,
then the value is "Yes"
If does find the string "COPY" then the value is set to "No".

I'm not sure how to get to the same results using InStr. Any help would be
appreciated.

 

[JimBurke via AccessMonster.com]

I think this is what you're looking for:

IIf(LEN(PROGRAM)>6,"No",IIf(InStr(1,PROG_DESC,"COPY") > 0,"No","Yes"))

Notice that it's the IIf function, not If.

If the length of the PROGRAM field is > 6 this should return 'No'.
If the length of the PROGRAM field is <= 6 then
If the string 'COPY' is found anywhere in the field PROG_DESC it should
return 'No'
Else it should return 'Yes'