In (Form.TextBox)

[Kraeg]

I have a query with the criteria for one field being
In (Form.TextBox)
where the value of the textbox is a comma delimited row of numbers.
However, while the query will return rows when a single number is in the textbox, it won't return any if there are multiple numbers.
Am I missing something? (Well, obviously I am..... but I have no idea what.)

 

[nicsin]

It would be better if you could paste your code/query here.

nicsin

 

[Kraeg]

Query:
SELECT Contacts.ContactTypeID
FROM Contacts
WHERE Contacts.ContactTypeID In ([Forms]![frmReports]![txtSalesRepsSelected]);

If the textbox txtSalesRepsSelected on form frmReports contains, for example, "4", then rows are returned.
If it contains "4, 1", no rows are returned (I know for sure they should be).

 

[dhookom]

As you have probably experienced, this will not work. You may need to modify the sql of the query or possibly use the Instr() function.
WHERE Instr("," & Forms!frmReports!txtSalesRepsSelected & ",", "," & [ContactTypeID] & ",&quot > 0
Make sure you don't have any spaces between the values in your form control.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[nicsin]

Is this in vb/vba?

for vb/vba you'd better write:

sql = "SELECT Contacts.ContactTypeID
FROM Contacts
WHERE Contacts.ContactTypeID In (" & [Forms]![frmReports]![txtSalesRepsSelected] & ""

 

[dhookom]

nicsin,
I agree that there is a better method if using code. My suggestion was sql expression only and can be used in a query. It has limitations but is a possible alternative to using In with a form control.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Kraeg]

dhookom, thanks for that..... it works perfectly.

nicsin, I'd tried that (it worked), but I really needed it in a query.

Thanks.

 

[nicsin]

dhookom,

I had't seen your solution when I posted mine! It is indeed better in this case.

all the best,
nicsin