Without getting into the necessity of having a parameters table.

I have a table called parameters consisting of several fields the main ones are:

param_id AutoNumber PK
param_name Text 30 > CCCC...
param_value Text 100
param_desc Memo

It would not be unusual to have in the field param_name something like PC_DESIG_FORST; with a param_value '660', '661', '669'; param_desc - not important for this discussion.

Now what I would like to do is use the list stored in param_value in a sub query using the IN() function. And of course something like



Select SomeFiled
From SomeTable
Where SomeValue IN
( Select param_value
From params
Where param_name = "PC_DESIG_FORST"
)


Now the param_value that is returned is the list from the field and of course it is NOT going to give me the results I want. Which to populate the IN( '660', '661', '669' ). Two questions 1. why not? 2. How can this be done?

Normalizing the field is not an option for reasons space and time here won't allow for explanation.

Select SomeFiled
From SomeTable
Where SomeValue IN
( Select param_value
From params
Where param_name = "PC_DESIG_FORST"
)


Now the param_value that is returned is the list from the field and of course it is NOT going to give me the results I want. Which to populate the IN( '660', '661', '669' ).Why won't it give you what you want? In your example, aren't you trying to narrow your search in terms of the param's '660', '661', and '669'??? Two questions 1. why not? 2. How can this be done?A lot more explanation is needed here. Your description was very difficult to follow. If you can, explain the desired outcome a different way. thanks...

Your description was very difficult to follow.


Really? :rolleyes:

Its not that difficult really. I have a list in a field in a parameters table that I wish to bring to an IN() function by means of a sub query

I did discover a solution. I created generic query then created a function to return the results of the updated query.

' Purpose: Pull a parameter table value, with any null value resolved, and
' the result trimmed of extra spaces
'
Public Function drc_GetParameter(ByVal strParamName As String) As Variant
Dim qdf As QueryDef
Dim rst As Recordset
Dim Sql As String
Const cstrQryName As String = "drc_fnGeneric"

Sql = "SELECT param_value, "
Sql = Sql & "param_gov_unit "
Sql = Sql & "FROM wheeler_params "
Sql = Sql & "WHERE param_name = '" & strParamName & "' "
Sql = Sql & " ORDER BY param_gov_unit DESC;"

Set qdf = CurrentDb.QueryDefs(cstrQryName)
qdf.Sql = Sql
Set rst = CurrentDb.OpenRecordset(cstrQryName)

If rst.EOF Then
drc_GetParameter = Empty
Else
drc_GetParameter = Trim(Nz(rst![param_value], Empty))
End If

Set qdf = Nothing
Set rst = Nothing
End Function


Then in my runtime query

select * from parcel_base
-- need to convert the return value to a value for property class
where property_class in( val( drc_GetParameter( "PC_DESIG_FORST" ) ) )


Works like a charm...
Thanks all for letting me think out loud ;)

Really? :rolleyes:

Its not that difficult really.It is when you're not talking to a programmer. I don't think, act, or write like one. I'm guessing you are one. But anyway, it got worked out. great...

No, not a programmer or database admin or not even very smart. Just a guy trying to get through the day - like the vast majority of humanity.

BTW. Need to disregard the code above. For some reason it only returns the first filter in the list. :mad:

I give up!

doco

look at the eval function

although you cant use "in" directly in vba, you can accomplish it with vba using eval, i think - the help gives some info i recall

Tried EVAL() some hours ago. It doesn't work either :(

Cannot populate IN() with the result of a function but will work if a variable is placed


Dim szList As String
Dim SQL As String
Dim rs As DAO.Recordset

szList = GetParameter('PC_DESIG_FORST')

SQL = "select * from parcel_base where property_class in(" & szList & ");"

Set rs = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)

etc, etc


In SQL Server you're allowed to insert variables in the sql string


Declare @List VarChar(100)
Set @List = GetParameter('PC_DESIG_FORST')

select * from parcel_base where property_class in( @List );


Is there an equivalent in Access SQL?