InStr() always returns zero in If Then statement

Input is Code("table", "name column", ", ")

I want to truncate a name column that has anything after a comma, but not all names contain a comma. The DoCmd.The RunSQL portion works properly.

Public Function Code(A, B, C)
'From table A, truncates column B at delimiter C

    If InStr(1, "[" & A & "].[" & B & "]", "'" & C & "'") <> 0 Then

    DoCmd.RunSQL "update [" & A & "]" & _
        "set [" & A & "].[" & B & "] = left([" & A & "].[" & B & "], InStr([" & A & "].[" & B & "],'" & C & "')-1)"

    End If

End Function

I believe the InStr inside the If/Then statement is only giving zero, so it doesn't work. I've tested it by setting it to "= 0" and it truncates every value in the column.

Solution

Checking if the column has a comma must be part of the SQL, your If Instr... is only checking the parameters passed to your function (ie it looks for the delimiter C in the combined table and column names [TableA].[ColumnB], not the contents of the table. The query produced should be like

update [TableA] set [ColumnB]=left([ColumnB], Instr([ColumnB],[DelimiterC])-1)
where Instr([ColumnB],[DelimiterC])>1

Based on this your function should be like:

Public Function Code(A, B, C)
    DoCmd.RunSQL "update [" & A & "]" & _
        "set [" & A & "].[" & B & "] = left([" & A & "].[" & B & "], InStr([" & A & "].[" & B & "],'" & C & "')-1) where InStr([" & A & "].[" & B & "],'" & C & "')>1"

End Function

But I suggest that you should review your code with these in mind, as well:

VBA Functions are for evaluating something and returning values; not for updating data, and not returning anything, why not use a Sub here!
Building SQL in this manner (as a concatenated string from parameters) is not preferred due to potential SQL-injection attacks; use parameterised queries, instead (search for it).
Using A, B, C as parameter names is not very useful, the code will be more readable with more descriptive names.