[RESOLVED] SQL IN WHERE condition using textbox on form

[rack]

I'm trying to get a report to limit to the regions/departments that are specific to the ones in a textbox in a form.

I can get them to display in a report by using the .value, however I now matter what I do, I can't get it to display anything if I put it in the IN condition.

SELECT Rgn_dept, forms!frmReports!txtAllRegions.value
FROM MyTable
WHERE  Rgn_dept IN (forms!frmReports!txtAllRegions.value)

I have even tried

SELECT Rgn_dept, forms!frmReports!txtAllRegions.value
FROM MyTable
WHERE  Rgn_dept IN (forms!frmReports!txtAllRegions)

[Siddharth Rout]

Try this

SELECT Rgn_dept, Trim(forms!frmReports!txtAllRegions.value)
FROM MyTable
WHERE  Rgn_dept IN (Trim(forms!frmReports!txtAllRegions.value))

[rack]

Koolsid,

Nope that didn't work either.

Its like the IN command is having issues with the fact the items to look at, the comma seperated list of values, is in a textbox on a form, instead of hard coded values.

If I replace the forms!frmReports!txtAllRegions.value with the comma seperated values 14,19,5,18,4,3,17,23,28,24,16,15,6,7,2,8,9,1,11,22,21,26,12,25,20,10,27 everything works fine.

This list above I copied directly from the textbox.

I guess I'm going to have to go with an exists query. I'm trying to limit the regions/departments that the managers can see, to the ones they manage.

I don't want to just throw it in as another joined table, because admins have only 1 region they belong to, but there level is Admin, which automatically gives them access to all regions.

I'm thinking about just doing a where

EXISTS (
SELECT tblManagers.Rgn_Dept 
FROM tblManagers 
WHERE MyTable.Rgn_Dept = tblManagers.Rgn_Dept 
AND tblManagers.EmployeeID = forms!frmReports!txtEmployeeID)

I wanted to avoid this method, as I'm sure it requires more processing/data to be sent accross the network.

[si_the_geek]

Its like the IN command is having issues with the fact the items to look at, the comma seperated list of values, is in a textbox on a form, instead of hard coded values.

That is entirely expected... because the textbox contains a single value (a string containing multiple numbers and commas), whereas what you type in to the query is multiple number values separated by commas.

I'm not aware of a way to write a query that would treat the single value as multiple for an IN list, and suspect the best way for a query would be a table to store records for each of the values.

[rack]

[QUOTE=si_the_geek;3774030]That is entirely expected... because the textbox contains a single value (a string containing multiple numbers and commas), whereas what you type in to the query is multiple number values separated by commas.
QUOTE]

Ah so its looking to match "14,19,5,18,4,3,17,23,28,24,16,15,6,7,2,8,9,1,11,22,21,26,12,25,20,10,27" with each Rgn_Dept field value. That would make sense for it not showing any records then.

The Exists query in the WHERE condition works. I don't create tables on the fly in any of my code/database(s), or that would be a good idea Si_the_geek. I"ll use the Exists where condition instead for now.

Thank you both for the input, and Si_the_Geek for the explanation, that helps a lot knowing what it is trying to do.

[si_the_geek]

I don't create tables on the fly in any of my code/database(s), or that would be a good idea Si_the_geek.

There are times when it can be useful, but in general within Access it should be avoided.

As you've got something that works, I'd recommend sticking with it.