If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
help using a parameter in an 'In (...)' criteria
I have a series of queries that all have the same criteria: "In (2,3)" (plus
a few others). They are being called from VBA code, where the parameters are generated and passed to the queries. I want the "In" criteria to be passed as a parameter, since the list may not be '2,3' in every case. I can't seem to make it work! I've tried putting '[parameter]' as the criteria and passing 'In (2,3)' to it, but I get an error message saying the expression is too complicated. I've tried putting 'In ([parameter])' as the criteria and passing '2,3' to it, but it doesn't return any records. Any suggestions? Thanks in advance, Jey |
#2
|
|||
|
|||
help using a parameter in an 'In (...)' criteria
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 You can use the InStr() trick: 1. Set up a Public function in the criteria form that passes a comma delimited string of the parameters. If there aren't any parameters the function should return zero (see below). If the function is passing comma-delimited strings be sure to use single quotes - like this: FunctionName = "'abcd', 'efghi', 'xyz'" 2. Set up the query to read that Public function: PARAMETERS Forms!FormName!FunctionName Text(255); .... 3. Set up the WHERE clause with something like this: IIf(Forms!FormName.FunctionName="0",True,InStr("," & Forms!FormName.FunctionName & ",","," & column_name & ",")0) Substitute the correct column name that holds the data for which you want to search for "column_name." An english translation of the above: If the function returns 0 then use "True" as a default for this expression. Otherwise, use the InStr() trick to determine if any of the comma-delimited parameters are in the "column_name" column. -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) ** Respond only to this newsgroup. I DO NOT respond to emails ** -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBSX9lOIechKqOuFEgEQLu/gCgpV4yAEdxNI6gOMuwPPjXsl/6iGoAoIPm LXfTZlg2g7qOfG3ZAvYt1bix =nc4N -----END PGP SIGNATURE----- Jey wrote: I have a series of queries that all have the same criteria: "In (2,3)" (plus a few others). They are being called from VBA code, where the parameters are generated and passed to the queries. I want the "In" criteria to be passed as a parameter, since the list may not be '2,3' in every case. I can't seem to make it work! I've tried putting '[parameter]' as the criteria and passing 'In (2,3)' to it, but I get an error message saying the expression is too complicated. I've tried putting 'In ([parameter])' as the criteria and passing '2,3' to it, but it doesn't return any records. Any suggestions? Thanks in advance, Jey |
#3
|
|||
|
|||
help using a parameter in an 'In (...)' criteria
Hi,
Sorry, but I'm not really clear on all that! Ideally, I'd like to make a variable, define it as "2,3", then be able to use the variable to define the paramater every time I call the query. Such as: Dim DB As Database Set DB = CurrentDb() Dim rs As Recordset Dim qdf As QueryDef Dim strList As String strList = "2,3" Set qdf = DB.QueryDefs("qryMyQuery") qdf.Parameters("parameter1") = ..... qdf.Parameters("parameter2") = ..... qdf.Parameters("parameter3") = strList Set rs = qdf.OpenRecordset(dbOpenDynaset) ..... It works for all the other parameters!!! The query is currently something like: SELECT .... FROM ..... WHERE (((Field1.Table1)=[parameter1]) AND ((Field2.Table1)=[parameter2]) AND((Field3.Table1) In (2,3))); What is it about the In () clause that won't accept "In ([parameter3])"??? Thanks, Jey |
#4
|
|||
|
|||
help using a parameter in an 'In (...)' criteria
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 When you set a parameter like: qdf.Parameters("parameter3") = strList The parameter looks like this: "1,2,3" That's ONE parameter instead of the required 3 parameters. That's why it won't work in the IN () operator 'cuz instead of reading (1,2,3) it is reading ("1,2,3"). It "thinks" it's looking for the string "1,2,3" instead of the three numbers 1 2 3. That's why the InStr() trick is the way to go. You can assign the string to the parameter using: qdf.Parameters("parameter3") = strList Then, in the query, instead of using IN () use InStr("," & parameter3 & "," , "," & column_name & ",") 0 If one of the numbers in the parameter "parameter3" is stored in the column the InStr() function returns a number 0 - the expression 0 evaluates to True, which selects the record (depending on the other criteria). Substitute your column's name for "column_name." -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) ** Respond only to this newsgroup. I DO NOT respond to emails ** -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBSX+ijoechKqOuFEgEQLUrQCfbQAOI0JM3txA6MqrEcfqFM 9Hs9UAn065 FKkOUNqtNtb5F+/J/tmyBlFu =bW+K -----END PGP SIGNATURE----- Jey wrote: Hi, Sorry, but I'm not really clear on all that! Ideally, I'd like to make a variable, define it as "2,3", then be able to use the variable to define the paramater every time I call the query. Such as: Dim DB As Database Set DB = CurrentDb() Dim rs As Recordset Dim qdf As QueryDef Dim strList As String strList = "2,3" Set qdf = DB.QueryDefs("qryMyQuery") qdf.Parameters("parameter1") = ..... qdf.Parameters("parameter2") = ..... qdf.Parameters("parameter3") = strList Set rs = qdf.OpenRecordset(dbOpenDynaset) .... It works for all the other parameters!!! The query is currently something like: SELECT .... FROM ..... WHERE (((Field1.Table1)=[parameter1]) AND ((Field2.Table1)=[parameter2]) AND((Field3.Table1) In (2,3))); What is it about the In () clause that won't accept "In ([parameter3])"??? Thanks, Jey |
#5
|
|||
|
|||
help using a parameter in an 'In (...)' criteria
Hi,
I can't get it to work!?! In the code I have: strSurveyList = "2,3" qdf.Parameters("parSurveyList") = strSurveyList But I get an error when the code hits the qdf.Parameters... line, because the query isn't asking for [parSurveyList]. In the query when I put InStr("," & "parSurveyList" & ",","," & "SurveyType" & ",") 0 as a criteria for the field SurveyType, table Survey... after saving & closing the query when I re-open it the field is now InStr("," & "parSurveyList" & ",","," & "SurveyType" & ",") and the criteria is 0. Shouldn't I have [parSurveyList] (in square brackets) somewhere so it knows to ask for that parameter?? When I change the field to InStr("," & [parSurveyList] & ",","," & "SurveyType" & ",") it runs, but doesn't return the counts I'd expect if it was working right, just nulls. Jey |
#6
|
|||
|
|||
help using a parameter in an 'In (...)' criteria
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1 Switch to SQL view. At the top of the screen, before SELECT, put the PARAMETERS clause: PARAMETERS parSurveyList Text, the other parameter names and data types. Don't enter the angle brackets. End the clause with the semi colon. ; SELECT ... FROM ... NB: qdf.Parameters(0) is the first parameter in the PARAMETERS list, so always use the parameter name when assigning values to parameters - this will avoid errors. You're correct about the square brackets, but you're putting them in the wrong place. Should be like this: WHERE InStr("," & parSurveyList & ",", "," & [SurveyType] & ",")0 other criteria Don't put quotes around the parameter name, nor the column name (SurveyType). When you work in the Design Grid, Access "helpfully" places quotation marks around just about everything! That's why I work a lot in the SQL View. -- MGFoster:::mgf00 at earthlink decimal-point net Oakland, CA (USA) ** Respond only to this newsgroup. I DO NOT respond to emails ** -----BEGIN PGP SIGNATURE----- Version: PGP for Personal Privacy 5.0 Charset: noconv iQA/AwUBSYKDf4echKqOuFEgEQIEnACdFd5KD+6a21VP/yqqnFHH/Q6xfYIAoMIL pR2Sa3W2EyB0pGjVH0eFARXq =V4tz -----END PGP SIGNATURE----- Jey wrote: Hi, I can't get it to work!?! In the code I have: strSurveyList = "2,3" qdf.Parameters("parSurveyList") = strSurveyList But I get an error when the code hits the qdf.Parameters... line, because the query isn't asking for [parSurveyList]. In the query when I put InStr("," & "parSurveyList" & ",","," & "SurveyType" & ",") 0 as a criteria for the field SurveyType, table Survey... after saving & closing the query when I re-open it the field is now InStr("," & "parSurveyList" & ",","," & "SurveyType" & ",") and the criteria is 0. Shouldn't I have [parSurveyList] (in square brackets) somewhere so it knows to ask for that parameter?? When I change the field to InStr("," & [parSurveyList] & ",","," & "SurveyType" & ",") it runs, but doesn't return the counts I'd expect if it was working right, just nulls. Jey |
#7
|
|||
|
|||
help using a parameter in an 'In (...)' criteria
Hi,
OK, I got it to work. Thanks! I don't understand why my three other parameters are in square brackets in the WHERE clause, but for the survey list parameter it's the field name that is in square brackets? Hmm, now that I look at the SQL more closely I also don't understand why in the SELECT clause the table/field are as [Table]![Field] but in the WHERE they are as (Table.Field). Are those two formats interchangeable? maybe that's where my confusion is coming from? Here is my SQL: PARAMETERS parSurveyList Text ( 255 ), parHerd Long, parPreDate DateTime, parPostDate DateTime; SELECT Sum([GroupLocation]![AdultUnknown]+[GroupLocation]![Unknown]) AS [Unknown] FROM Survey INNER JOIN GroupLocation ON Survey.Survey_ID = GroupLocation.Survey_ID WHERE (((GroupLocation.Herd_ID)=[parHerd]) AND ((InStr("," & parSurveyList & ",","," & [SurveyType] & ","))0) AND ((GroupLocation.Date)[parPreDate] And (GroupLocation.Date)[parPostDate])); |
Thread Tools | |
Display Modes | |
|
|