In statement + forms

[kbreiss]

I have an sql that runs....

UPDATE ProblemLog SET ProblemLog.Status = "Pending"
WHERE [Problem Number] IN (([Forms]![frmPendEm]![txtProbNo]));

Problem Number is autoGenerated primary key.

When I put in say...26, 27 it updates 0 records.
Put 26 or 27 by themselves and it updates 1 record at a time.

Anybody have any ideas of how this in statment can be accomplished to update multiple records?

Thanks.
________
Side Effects From Zoloft

 

[Jon K]

IN is an operator, so it would treat what is typed in the text box as one whole thing (a parameter).

One way to do what you want is to build or edit the SQL statement on the fly in VBA.

Another way is to use a function (e.g. the InStr() function) instead of the IN operator:-

UPDATE ProblemLog SET ProblemLog.Status = "Pending"
WHERE Instr("," & Forms!frmPendEm!txtProbNo & ",", "," & [Problem Number] & ",");


Since you gave an example of typing 26, 27 in the text box, you will also need to use a function to remove any spaces in the text box:-

WHERE Instr("," & Replace(Forms!frmPendEm!txtProbNo," ","") & ",", "," & [Problem Number] & ",");
.

 

[kbreiss]

Explain

The instr function works, but could you please explain what its doing. I know the instr function returns the position number the first occurrence of a string is found....but I don't understand what this update query is doing.

Thanks,
Kacy
________
Suzuki tu250

 

[Jon K]

I know the instr function returns the position number the first occurrence of a string is found.

That's exactly what the InStr does in the criteria.


Maybe rewriting the criteria like the following can help to make it easier to understand:-

WHERE Instr("," & Replace(Forms!frmPendEm!txtProbNo," ","") & ",", "," & [Problem Number] & ",") > 0

though the > 0 is unnecessary since in evaluating a condition any resultant number other than 0 signifies True.


The adding of leading and ending commas to the text box string and the number in the [Problem Number] field helps to properly delimit the individual numbers for comparison so as to avoid InStr("26,27", "6") etc. returning True because with the added leading and ending commas, it becomes InStr(",26,27,", ",6,") and returns False.

Hope this helps to explain it.
.