Variable in a string..

[mud2]

Seems to me I've been here many times before...
I want to run a SELECT INTO SQL. It contains a WHERE clause which contains a STRCOMP.
The fields it compare are
ID: The field in a table, and
Sfor, a string returned from a combobox.
A working (!!!) clause is
WHERE(((Strcomp(ID,'" & Sfor & "',0)=0)).
These quotes are single, double and double, single
The construction "' & Sfor & "' is cumbersome, and I want to write a function that will redefine Sfor so that I can use the redefined Sfor in the Strcomp, without all the single and double quotes.

Something like Sfor = "'& Sfor & '"
After exhausting the 600 or 700 permutations of Chr(34), Chr(39), " , ', and &'s I give up!

[Oorang]

The solution can vary a bit depending on where the code is being run from. Can you post the code for viewing?

[Norie]

Why are you using StrComp in the first place?

[Oorang]

Just a guess, but StrComp allows for null values.

[Norie]

Aaron

I know StrComp works differently from a 'straight' comparison but I've got to say I've never seen it used like this.

If there is an issue with Null values then perhaps the OP could use Nz or even an IIf expression.

[Oorang]

You could indeed, but StrComp should work. 'Course the whole conversation is academic if the OP doesn't respond:)

[CreganTur]

Too bad VBA doesn't allow you to Dim variables as Nullable, like you can in Visual Basic. That would solve a number of problems.

[Oorang]

Yah, that's because VBA is based on VB6. So far VBA/VB6 you use varaints, which can handle null values. This is also why you will see a lot of Access UDFs using variant parameters.