first of all, varWhere should never equal NULL if it's a string. set it to "" (0 length string) to start with. that's a problem.
next, this:
Code:
varWhere = varWhere & "[FIRST NAME] LIKE """ & Me.FirstName & "*"" AND "
is complicated to read for anyone. what people do in scenarios like this is use the DEBUG.PRINT option to test the clause and get it to where it needs to be before executing it in a procedure. so here, do that until it looks like this in the immediate window:
Code:
[FIRST NAME] LIKE '*' & Me.FirstName & '*' AND
(that ends in a space, but you can't see it)
so when you manipulate it and end up with the above, it will read correctly. hence, you will be able to concat it into the rest of the sql without error. the way I usually do this kind of testing is in the immediate window itself. when you test expressions in that window directly, you have to preceed the expression with a "?" mark. The ? is the same thing as saying "return".