Multiple values passed to UDF with two parameters

[AkAlan via AccessMonster.com]

I have a UDF with two parameters and I'm trying to pass multiple values to
either parameter and can't figure it out. I'll show what I have tried.

Me.RecordSource = "SELECT * FROM fn_QcQuestions(IN(1,2,3),In(4,5,6))"

Me.RecordSource = "SELECT * FROM fn_QcQuestions(1 OR 2 OR 3, 4 OR 5 OR 6 )"

Any help would be appreciated. Thanks.

 

[Sylvain Lafontaine]

As far as I know, you can't pass multiple values to an UDF. Maybe by
telling us what you want to achieve, another solution could show up.

 

[AkAlan via AccessMonster.com]

I have a table with 1900 questions that my QC department uses to perform
inspections. We have 15 sites to inspect and 9 different areas to inspect
(Admin, Saftey,etc). Each question is tied to 1 or more sites and 1 or more
areas. I have an unbound form which lists all the sites and all the areas. If
the QC inpector is going to more than one site and wants to inspect a few
areas he only wants to print out the relevent questions.

Table structures area as follows

tblQcQuestions:

QuestionId Pk
Question text
Reference text

tblQcQuestionsXAreas:

AreaId Pk
QuestionId PK

tblAreas:

AreaId Pk
Area text


tblQcQuestionsXSites:

SiteId PK
QuestionId PK

tblSites:

SiteId Pk
Site text

Thanks Sylvan for your help.

As far as I know, you can't pass multiple values to an UDF. Maybe by
telling us what you want to achieve, another solution could show up.

I have a UDF with two parameters and I'm trying to pass multiple values to
either parameter and can't figure it out. I'll show what I have tried.

[quoted text clipped - 5 lines]

Any help would be appreciated. Thanks.

 

[Sylvain Lafontaine]

You don't tell us what the function fn_QcQuestions() is doing here. I
suppose it's some kind of View but without it's source code, it's impossible
to tell.

From your description of the problem in your second post, I suppose that you
will have to build two IN statements or store the values for the sites and
the areas in a table somewhere - along with the information about which
Inspector - and use this table to filter for the required sites and areas.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

I have a table with 1900 questions that my QC department uses to perform
inspections. We have 15 sites to inspect and 9 different areas to inspect
(Admin, Saftey,etc). Each question is tied to 1 or more sites and 1 or
more
areas. I have an unbound form which lists all the sites and all the areas.
If
the QC inpector is going to more than one site and wants to inspect a few
areas he only wants to print out the relevent questions.

Table structures area as follows

tblQcQuestions:

QuestionId Pk
Question text
Reference text

tblQcQuestionsXAreas:

AreaId Pk
QuestionId PK

tblAreas:

AreaId Pk
Area text


tblQcQuestionsXSites:

SiteId PK
QuestionId PK

tblSites:

SiteId Pk
Site text

Thanks Sylvan for your help.

As far as I know, you can't pass multiple values to an UDF. Maybe by
telling us what you want to achieve, another solution could show up.

I have a UDF with two parameters and I'm trying to pass multiple values
to
either parameter and can't figure it out. I'll show what I have tried.

[quoted text clipped - 5 lines]

Any help would be appreciated. Thanks.

 

[AkAlan via AccessMonster.com]

Yes the function returns all the questions based on two parameters, SiteId
and AreaId. There can be 1 or more Sites and 1 or more Areas. As you can see
from my first post I was trying to build two IN statements within my WHERE
clause, I just don't know how. Thanks.

You don't tell us what the function fn_QcQuestions() is doing here. I
suppose it's some kind of View but without it's source code, it's impossible
to tell.

From your description of the problem in your second post, I suppose that you
will have to build two IN statements or store the values for the sites and
the areas in a table somewhere - along with the information about which
Inspector - and use this table to filter for the required sites and areas.

I have a table with 1900 questions that my QC department uses to perform
inspections. We have 15 sites to inspect and 9 different areas to inspect

[quoted text clipped - 44 lines]

 

[Sylvain Lafontaine]

You still don't tell us how you were intending to use your multi-valued
parameters inside the UDF. I would suggest that you dynamically build a
full sql string using two IN statements in order to retrieve the required
sites and areas.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Yes the function returns all the questions based on two parameters, SiteId
and AreaId. There can be 1 or more Sites and 1 or more Areas. As you can
see
from my first post I was trying to build two IN statements within my WHERE
clause, I just don't know how. Thanks.

You don't tell us what the function fn_QcQuestions() is doing here. I
suppose it's some kind of View but without it's source code, it's
impossible
to tell.

From your description of the problem in your second post, I suppose that
you
will have to build two IN statements or store the values for the sites and
the areas in a table somewhere - along with the information about which
Inspector - and use this table to filter for the required sites and areas.

I have a table with 1900 questions that my QC department uses to perform
inspections. We have 15 sites to inspect and 9 different areas to
inspect

[quoted text clipped - 44 lines]

Any help would be appreciated. Thanks.

 

[AkAlan via AccessMonster.com]

Thanks again for your patience Sylvain. I am using the parameters to filter
the returned records. I will try and build the SQL String you suggest.

You still don't tell us how you were intending to use your multi-valued
parameters inside the UDF. I would suggest that you dynamically build a
full sql string using two IN statements in order to retrieve the required
sites and areas.

Yes the function returns all the questions based on two parameters, SiteId
and AreaId. There can be 1 or more Sites and 1 or more Areas. As you can

[quoted text clipped - 19 lines]

 

[AkAlan via AccessMonster.com]

I solved the problem and will post the solution for others benefit.

I first parsed all the check boxes to see which were selected and
concatenated a string variable. Then checked to make sure at least one check
was selected, then made the WHERE clause.

Dim strSite As String

If Me.chkKingSalmon = True Then
strSite = strSite & ",1"
End If
If Me.chkPointBarrow = True Then
strSite = strSite & ",2"
End If
If Me.chkBarterIsland = True Then
strSite = strSite & ",3"
End If

If Len(strSite) = 0 Then
MsgBox "You must enter at least one site"
Exit Sub
End If

'remove leading comma
strSite = Right(strSite, Len(strSite) - 1)
'finish WHERE clause for sites
strSite = "QcSitesId IN (" & strSite & ")"

DoCmd.OpenReport "rptQcQuestions", acViewPreview, , strSite

Thanks again for your patience Sylvain. I am using the parameters to filter
the returned records. I will try and build the SQL String you suggest.

You still don't tell us how you were intending to use your multi-valued
parameters inside the UDF. I would suggest that you dynamically build a

[quoted text clipped - 6 lines]