 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / SQL Server / ADP / May 2008Multiple values passed to UDF with two parameters
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. 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.  SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) >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. 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. [quoted text clipped - 4 lines] >> >> Any help would be appreciated. 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. SignatureSylvain 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 [quoted text clipped - 44 lines] >>> >>> Any help would be appreciated. Thanks. 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 [quoted text clipped - 10 lines] >>>> >>>> Any help would be appreciated. Thanks. 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. SignatureSylvain 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 [quoted text clipped - 19 lines] >>>>> >>>>> Any help would be appreciated. Thanks. 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] >>>>>> >>>>>> Any help would be appreciated. Thanks. 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. [quoted text clipped - 4 lines] >>>>>>> >>>>>>> Any help would be appreciated. Thanks.
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.