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
|
|||
|
|||
passing a parameter based on multiple selections
Hi,
I have a form in which several choices are selected, one of which is a list box in which the user can make multiple selections. I followed the various suggestions to create a String that has the selected items and I set a textbox in the form as that final String as follows: Dim Criteria As String Dim ctl As Control Dim Itm As Variant ' Build a list of the selections. Set ctl = Me![lboSTATID] For Each Itm In ctl.ItemsSelected If Len(Criteria) = 0 Then Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34) Else Criteria = Criteria & " OR " & Chr(34) & ctl.ItemData(Itm) _ & Chr(34) End If Next Itm Me.tboCriteria = Criteria However, when another form (based on a query with the passed parameters)opens to show me the selection, there are no data. The correct String for the query parameter appears to be correct, i.e., "item1" OR "item2" and if I replace the same String in the query instead of using the [Forms]![frmSearch]![tboCriteria] the query works fine. Where am I going wrong? Any help is appreciated. Thanks, -- javablood |
#2
|
|||
|
|||
passing a parameter based on multiple selections
The syntax
"Los Angeles" OR "Baltimore" OR "Dallas" is only valid in very few circumstances. It is far better to have: IN( "Los Angeles", "Baltimore", "Dallas" ) and even then, you have to specify which field has to satisfy this IN-ness constraint in many cases. So, without knowing HOW you use that criteria, I strongly suggest that, at least, you change the OR to a coma, and in the end, use Me.tboCriteria = " IN (" & criteria & ")" It may help (but again, depends on what you really do with that tboCriteria thing). Vanderghast, Access MVP "javablood" wrote in message ... Hi, I have a form in which several choices are selected, one of which is a list box in which the user can make multiple selections. I followed the various suggestions to create a String that has the selected items and I set a textbox in the form as that final String as follows: Dim Criteria As String Dim ctl As Control Dim Itm As Variant ' Build a list of the selections. Set ctl = Me![lboSTATID] For Each Itm In ctl.ItemsSelected If Len(Criteria) = 0 Then Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34) Else Criteria = Criteria & " OR " & Chr(34) & ctl.ItemData(Itm) _ & Chr(34) End If Next Itm Me.tboCriteria = Criteria However, when another form (based on a query with the passed parameters)opens to show me the selection, there are no data. The correct String for the query parameter appears to be correct, i.e., "item1" OR "item2" and if I replace the same String in the query instead of using the [Forms]![frmSearch]![tboCriteria] the query works fine. Where am I going wrong? Any help is appreciated. Thanks, -- javablood |
#3
|
|||
|
|||
passing a parameter based on multiple selections
Here is the SQL I am using :
PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ), [Forms]![frmSearch]![lbodatbegin] DateTime, [Forms]![frmSearch]![lbodatend] DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ), [Forms]![frmSearch]![cboSamptype] Text ( 255 ), [Forms]![frmSearch]![cboDet] Bit, [Forms]![frmSearch]![tboCriteria] Text ( 255 ); SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE, tblHardage.PARAMETER, tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS, tblHardagePAR.F1_GROUP, tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain, tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN tblHardageSiteIdentification ON tblHardage.STATION_ID = tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY = tblHardage.QC WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin] And [Forms]![frmSearch]![lbodatend]) AND ((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND ((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND ((tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is Null,"*",IIf([Forms]![frmSearch]![cboDet]=Yes,True,False)))) ORDER BY tblHardagePAR.F1_GRPORDR; The tboCriteria is for the STATION_ID field from which I want to see the records of all the locations that are in the tboCriteria string, e.g., "A" OR "B" OR "C", etc. Does this help you help me? I have not used the IN before and am not sure how. I am not an experienced Access person. Is there anything else I can provide to clear up what I am trying to do? thanks, -- javablood "vanderghast" wrote: The syntax "Los Angeles" OR "Baltimore" OR "Dallas" is only valid in very few circumstances. It is far better to have: IN( "Los Angeles", "Baltimore", "Dallas" ) and even then, you have to specify which field has to satisfy this IN-ness constraint in many cases. So, without knowing HOW you use that criteria, I strongly suggest that, at least, you change the OR to a coma, and in the end, use Me.tboCriteria = " IN (" & criteria & ")" It may help (but again, depends on what you really do with that tboCriteria thing). Vanderghast, Access MVP "javablood" wrote in message ... Hi, I have a form in which several choices are selected, one of which is a list box in which the user can make multiple selections. I followed the various suggestions to create a String that has the selected items and I set a textbox in the form as that final String as follows: Dim Criteria As String Dim ctl As Control Dim Itm As Variant ' Build a list of the selections. Set ctl = Me![lboSTATID] For Each Itm In ctl.ItemsSelected If Len(Criteria) = 0 Then Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34) Else Criteria = Criteria & " OR " & Chr(34) & ctl.ItemData(Itm) _ & Chr(34) End If Next Itm Me.tboCriteria = Criteria However, when another form (based on a query with the passed parameters)opens to show me the selection, there are no data. The correct String for the query parameter appears to be correct, i.e., "item1" OR "item2" and if I replace the same String in the query instead of using the [Forms]![frmSearch]![tboCriteria] the query works fine. Where am I going wrong? Any help is appreciated. Thanks, -- javablood |
#4
|
|||
|
|||
passing a parameter based on multiple selections
You can still use the IN( ) syntax, if your statement is used to open a form and if you open the form using DoCmd.OpenForm, which has an optional argument, whereCondition, like: DoCmd.OpenForm "formName", WhereCondition := " Station_id IN('Los Angeles', 'New York', 'Dallas') " but if your intention is to use the query in some other way, and that you don't plan to write the whole SQL statement as a string, to produce what we call an ad hoc query, then even the IN syntax cannot be used. You can use InStr( ) or, more portable, the operator LIKE though, as instead of SELECT ... WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ... use SELECT ... WHERE (( "," & [Forms]![frmSearch]![tboCriteria] & "," ) LIKE ( "*," & station_id & ",*" )) AND ... where tboCriteria will be filled with, say: Los Angeles,New York,Dallas (note that there is no space after the comas used as sub-string delimiter, in this case). Vanderghast, Access MVP "javablood" wrote in message ... Here is the SQL I am using : PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ), [Forms]![frmSearch]![lbodatbegin] DateTime, [Forms]![frmSearch]![lbodatend] DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ), [Forms]![frmSearch]![cboSamptype] Text ( 255 ), [Forms]![frmSearch]![cboDet] Bit, [Forms]![frmSearch]![tboCriteria] Text ( 255 ); SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE, tblHardage.PARAMETER, tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS, tblHardagePAR.F1_GROUP, tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain, tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN tblHardageSiteIdentification ON tblHardage.STATION_ID = tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY = tblHardage.QC WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin] And [Forms]![frmSearch]![lbodatend]) AND ((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND ((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND ((tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is Null,"*",IIf([Forms]![frmSearch]![cboDet]=Yes,True,False)))) ORDER BY tblHardagePAR.F1_GRPORDR; The tboCriteria is for the STATION_ID field from which I want to see the records of all the locations that are in the tboCriteria string, e.g., "A" OR "B" OR "C", etc. Does this help you help me? I have not used the IN before and am not sure how. I am not an experienced Access person. Is there anything else I can provide to clear up what I am trying to do? thanks, -- javablood "vanderghast" wrote: The syntax "Los Angeles" OR "Baltimore" OR "Dallas" is only valid in very few circumstances. It is far better to have: IN( "Los Angeles", "Baltimore", "Dallas" ) and even then, you have to specify which field has to satisfy this IN-ness constraint in many cases. So, without knowing HOW you use that criteria, I strongly suggest that, at least, you change the OR to a coma, and in the end, use Me.tboCriteria = " IN (" & criteria & ")" It may help (but again, depends on what you really do with that tboCriteria thing). Vanderghast, Access MVP "javablood" wrote in message ... Hi, I have a form in which several choices are selected, one of which is a list box in which the user can make multiple selections. I followed the various suggestions to create a String that has the selected items and I set a textbox in the form as that final String as follows: Dim Criteria As String Dim ctl As Control Dim Itm As Variant ' Build a list of the selections. Set ctl = Me![lboSTATID] For Each Itm In ctl.ItemsSelected If Len(Criteria) = 0 Then Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34) Else Criteria = Criteria & " OR " & Chr(34) & ctl.ItemData(Itm) _ & Chr(34) End If Next Itm Me.tboCriteria = Criteria However, when another form (based on a query with the passed parameters)opens to show me the selection, there are no data. The correct String for the query parameter appears to be correct, i.e., "item1" OR "item2" and if I replace the same String in the query instead of using the [Forms]![frmSearch]![tboCriteria] the query works fine. Where am I going wrong? Any help is appreciated. Thanks, -- javablood |
#5
|
|||
|
|||
passing a parameter based on multiple selections
You learn something new everyday! Thanks to the likes of you! Thank you!
Thank you! The WhereCondition worked, I just had to make sure I had the proper format with the quotes and commas. I am not sure what you meant "use the query in some other way". I did not have the SQL statement in the event procedure but opened the form form the event procedure. All along I thought I could pass the parameter from the form once I had the tboCriteria set. Is that not the case? thanks again! -- javablood "vanderghast" wrote: You can still use the IN( ) syntax, if your statement is used to open a form and if you open the form using DoCmd.OpenForm, which has an optional argument, whereCondition, like: DoCmd.OpenForm "formName", WhereCondition := " Station_id IN('Los Angeles', 'New York', 'Dallas') " but if your intention is to use the query in some other way, and that you don't plan to write the whole SQL statement as a string, to produce what we call an ad hoc query, then even the IN syntax cannot be used. You can use InStr( ) or, more portable, the operator LIKE though, as instead of SELECT ... WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ... use SELECT ... WHERE (( "," & [Forms]![frmSearch]![tboCriteria] & "," ) LIKE ( "*," & station_id & ",*" )) AND ... where tboCriteria will be filled with, say: Los Angeles,New York,Dallas (note that there is no space after the comas used as sub-string delimiter, in this case). Vanderghast, Access MVP "javablood" wrote in message ... Here is the SQL I am using : PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ), [Forms]![frmSearch]![lbodatbegin] DateTime, [Forms]![frmSearch]![lbodatend] DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ), [Forms]![frmSearch]![cboSamptype] Text ( 255 ), [Forms]![frmSearch]![cboDet] Bit, [Forms]![frmSearch]![tboCriteria] Text ( 255 ); SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE, tblHardage.PARAMETER, tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS, tblHardagePAR.F1_GROUP, tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain, tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN tblHardageSiteIdentification ON tblHardage.STATION_ID = tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY = tblHardage.QC WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin] And [Forms]![frmSearch]![lbodatend]) AND ((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND ((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND ((tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is Null,"*",IIf([Forms]![frmSearch]![cboDet]=Yes,True,False)))) ORDER BY tblHardagePAR.F1_GRPORDR; The tboCriteria is for the STATION_ID field from which I want to see the records of all the locations that are in the tboCriteria string, e.g., "A" OR "B" OR "C", etc. Does this help you help me? I have not used the IN before and am not sure how. I am not an experienced Access person. Is there anything else I can provide to clear up what I am trying to do? thanks, -- javablood "vanderghast" wrote: The syntax "Los Angeles" OR "Baltimore" OR "Dallas" is only valid in very few circumstances. It is far better to have: IN( "Los Angeles", "Baltimore", "Dallas" ) and even then, you have to specify which field has to satisfy this IN-ness constraint in many cases. So, without knowing HOW you use that criteria, I strongly suggest that, at least, you change the OR to a coma, and in the end, use Me.tboCriteria = " IN (" & criteria & ")" It may help (but again, depends on what you really do with that tboCriteria thing). Vanderghast, Access MVP "javablood" wrote in message ... Hi, I have a form in which several choices are selected, one of which is a list box in which the user can make multiple selections. I followed the various suggestions to create a String that has the selected items and I set a textbox in the form as that final String as follows: Dim Criteria As String Dim ctl As Control Dim Itm As Variant ' Build a list of the selections. Set ctl = Me![lboSTATID] For Each Itm In ctl.ItemsSelected If Len(Criteria) = 0 Then Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34) Else Criteria = Criteria & " OR " & Chr(34) & ctl.ItemData(Itm) _ & Chr(34) End If Next Itm Me.tboCriteria = Criteria However, when another form (based on a query with the passed parameters)opens to show me the selection, there are no data. The correct String for the query parameter appears to be correct, i.e., "item1" OR "item2" and if I replace the same String in the query instead of using the [Forms]![frmSearch]![tboCriteria] the query works fine. Where am I going wrong? Any help is appreciated. Thanks, -- javablood |
#6
|
|||
|
|||
passing a parameter based on multiple selections
As example, you can write a query statement as a string and use that string
as record SOURCE for a form, or for a ROW SOURCE ( list of a list box or combo box). The string will be evaluated as if it is an original SQL statement. Vanderghast, Access MVP "javablood" wrote in message ... You learn something new everyday! Thanks to the likes of you! Thank you! Thank you! The WhereCondition worked, I just had to make sure I had the proper format with the quotes and commas. I am not sure what you meant "use the query in some other way". I did not have the SQL statement in the event procedure but opened the form form the event procedure. All along I thought I could pass the parameter from the form once I had the tboCriteria set. Is that not the case? thanks again! -- javablood "vanderghast" wrote: You can still use the IN( ) syntax, if your statement is used to open a form and if you open the form using DoCmd.OpenForm, which has an optional argument, whereCondition, like: DoCmd.OpenForm "formName", WhereCondition := " Station_id IN('Los Angeles', 'New York', 'Dallas') " but if your intention is to use the query in some other way, and that you don't plan to write the whole SQL statement as a string, to produce what we call an ad hoc query, then even the IN syntax cannot be used. You can use InStr( ) or, more portable, the operator LIKE though, as instead of SELECT ... WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ... use SELECT ... WHERE (( "," & [Forms]![frmSearch]![tboCriteria] & "," ) LIKE ( "*," & station_id & ",*" )) AND ... where tboCriteria will be filled with, say: Los Angeles,New York,Dallas (note that there is no space after the comas used as sub-string delimiter, in this case). Vanderghast, Access MVP "javablood" wrote in message ... Here is the SQL I am using : PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ), [Forms]![frmSearch]![lbodatbegin] DateTime, [Forms]![frmSearch]![lbodatend] DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ), [Forms]![frmSearch]![cboSamptype] Text ( 255 ), [Forms]![frmSearch]![cboDet] Bit, [Forms]![frmSearch]![tboCriteria] Text ( 255 ); SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE, tblHardage.PARAMETER, tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS, tblHardagePAR.F1_GROUP, tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain, tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN tblHardageSiteIdentification ON tblHardage.STATION_ID = tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY = tblHardage.QC WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin] And [Forms]![frmSearch]![lbodatend]) AND ((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND ((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND ((tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is Null,"*",IIf([Forms]![frmSearch]![cboDet]=Yes,True,False)))) ORDER BY tblHardagePAR.F1_GRPORDR; The tboCriteria is for the STATION_ID field from which I want to see the records of all the locations that are in the tboCriteria string, e.g., "A" OR "B" OR "C", etc. Does this help you help me? I have not used the IN before and am not sure how. I am not an experienced Access person. Is there anything else I can provide to clear up what I am trying to do? thanks, -- javablood "vanderghast" wrote: The syntax "Los Angeles" OR "Baltimore" OR "Dallas" is only valid in very few circumstances. It is far better to have: IN( "Los Angeles", "Baltimore", "Dallas" ) and even then, you have to specify which field has to satisfy this IN-ness constraint in many cases. So, without knowing HOW you use that criteria, I strongly suggest that, at least, you change the OR to a coma, and in the end, use Me.tboCriteria = " IN (" & criteria & ")" It may help (but again, depends on what you really do with that tboCriteria thing). Vanderghast, Access MVP "javablood" wrote in message ... Hi, I have a form in which several choices are selected, one of which is a list box in which the user can make multiple selections. I followed the various suggestions to create a String that has the selected items and I set a textbox in the form as that final String as follows: Dim Criteria As String Dim ctl As Control Dim Itm As Variant ' Build a list of the selections. Set ctl = Me![lboSTATID] For Each Itm In ctl.ItemsSelected If Len(Criteria) = 0 Then Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34) Else Criteria = Criteria & " OR " & Chr(34) & ctl.ItemData(Itm) _ & Chr(34) End If Next Itm Me.tboCriteria = Criteria However, when another form (based on a query with the passed parameters)opens to show me the selection, there are no data. The correct String for the query parameter appears to be correct, i.e., "item1" OR "item2" and if I replace the same String in the query instead of using the [Forms]![frmSearch]![tboCriteria] the query works fine. Where am I going wrong? Any help is appreciated. Thanks, -- javablood |
#7
|
|||
|
|||
passing a parameter based on multiple selections
Okay. I am familiar with writing the query statement as a string but how
does one use that string as a record Source for a form? -- javablood "vanderghast" wrote: As example, you can write a query statement as a string and use that string as record SOURCE for a form, or for a ROW SOURCE ( list of a list box or combo box). The string will be evaluated as if it is an original SQL statement. Vanderghast, Access MVP "javablood" wrote in message ... You learn something new everyday! Thanks to the likes of you! Thank you! Thank you! The WhereCondition worked, I just had to make sure I had the proper format with the quotes and commas. I am not sure what you meant "use the query in some other way". I did not have the SQL statement in the event procedure but opened the form form the event procedure. All along I thought I could pass the parameter from the form once I had the tboCriteria set. Is that not the case? thanks again! -- javablood "vanderghast" wrote: You can still use the IN( ) syntax, if your statement is used to open a form and if you open the form using DoCmd.OpenForm, which has an optional argument, whereCondition, like: DoCmd.OpenForm "formName", WhereCondition := " Station_id IN('Los Angeles', 'New York', 'Dallas') " but if your intention is to use the query in some other way, and that you don't plan to write the whole SQL statement as a string, to produce what we call an ad hoc query, then even the IN syntax cannot be used. You can use InStr( ) or, more portable, the operator LIKE though, as instead of SELECT ... WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ... use SELECT ... WHERE (( "," & [Forms]![frmSearch]![tboCriteria] & "," ) LIKE ( "*," & station_id & ",*" )) AND ... where tboCriteria will be filled with, say: Los Angeles,New York,Dallas (note that there is no space after the comas used as sub-string delimiter, in this case). Vanderghast, Access MVP "javablood" wrote in message ... Here is the SQL I am using : PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ), [Forms]![frmSearch]![lbodatbegin] DateTime, [Forms]![frmSearch]![lbodatend] DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ), [Forms]![frmSearch]![cboSamptype] Text ( 255 ), [Forms]![frmSearch]![cboDet] Bit, [Forms]![frmSearch]![tboCriteria] Text ( 255 ); SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE, tblHardage.PARAMETER, tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS, tblHardagePAR.F1_GROUP, tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain, tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN tblHardageSiteIdentification ON tblHardage.STATION_ID = tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY = tblHardage.QC WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin] And [Forms]![frmSearch]![lbodatend]) AND ((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND ((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND ((tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is Null,"*",IIf([Forms]![frmSearch]![cboDet]=Yes,True,False)))) ORDER BY tblHardagePAR.F1_GRPORDR; The tboCriteria is for the STATION_ID field from which I want to see the records of all the locations that are in the tboCriteria string, e.g., "A" OR "B" OR "C", etc. Does this help you help me? I have not used the IN before and am not sure how. I am not an experienced Access person. Is there anything else I can provide to clear up what I am trying to do? thanks, -- javablood "vanderghast" wrote: The syntax "Los Angeles" OR "Baltimore" OR "Dallas" is only valid in very few circumstances. It is far better to have: IN( "Los Angeles", "Baltimore", "Dallas" ) and even then, you have to specify which field has to satisfy this IN-ness constraint in many cases. So, without knowing HOW you use that criteria, I strongly suggest that, at least, you change the OR to a coma, and in the end, use Me.tboCriteria = " IN (" & criteria & ")" It may help (but again, depends on what you really do with that tboCriteria thing). Vanderghast, Access MVP "javablood" wrote in message ... Hi, I have a form in which several choices are selected, one of which is a list box in which the user can make multiple selections. I followed the various suggestions to create a String that has the selected items and I set a textbox in the form as that final String as follows: Dim Criteria As String Dim ctl As Control Dim Itm As Variant ' Build a list of the selections. Set ctl = Me![lboSTATID] For Each Itm In ctl.ItemsSelected If Len(Criteria) = 0 Then Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34) Else Criteria = Criteria & " OR " & Chr(34) & ctl.ItemData(Itm) _ & Chr(34) End If Next Itm Me.tboCriteria = Criteria However, when another form (based on a query with the passed parameters)opens to show me the selection, there are no data. The correct String for the query parameter appears to be correct, i.e., "item1" OR "item2" and if I replace the same String in the query instead of using the [Forms]![frmSearch]![tboCriteria] the query works fine. Where am I going wrong? Any help is appreciated. Thanks, -- javablood |
#8
|
|||
|
|||
passing a parameter based on multiple selections
You specify the form's RecordSource property.
Me.RecordSource = "SELECT ... " Sure, that has to make sense, I mean, unless you want to revisit each control of the form to specify their ControlSource property too. I mean, if a control source property is set to field CompanyName, then the form record source should return a CompanyName: Me.RecordSource = "SELECT CompanyName, ... " else, you would get #Name? error in the control not finding its ControlSource field. While it can make sense to specify the Record Source in the Open event, it would be weird to do it somewhere else (well, unless it is a really 'special' design). Vanderghast, Access MVP "javablood" wrote in message ... Okay. I am familiar with writing the query statement as a string but how does one use that string as a record Source for a form? -- javablood "vanderghast" wrote: As example, you can write a query statement as a string and use that string as record SOURCE for a form, or for a ROW SOURCE ( list of a list box or combo box). The string will be evaluated as if it is an original SQL statement. Vanderghast, Access MVP "javablood" wrote in message ... You learn something new everyday! Thanks to the likes of you! Thank you! Thank you! The WhereCondition worked, I just had to make sure I had the proper format with the quotes and commas. I am not sure what you meant "use the query in some other way". I did not have the SQL statement in the event procedure but opened the form form the event procedure. All along I thought I could pass the parameter from the form once I had the tboCriteria set. Is that not the case? thanks again! -- javablood "vanderghast" wrote: You can still use the IN( ) syntax, if your statement is used to open a form and if you open the form using DoCmd.OpenForm, which has an optional argument, whereCondition, like: DoCmd.OpenForm "formName", WhereCondition := " Station_id IN('Los Angeles', 'New York', 'Dallas') " but if your intention is to use the query in some other way, and that you don't plan to write the whole SQL statement as a string, to produce what we call an ad hoc query, then even the IN syntax cannot be used. You can use InStr( ) or, more portable, the operator LIKE though, as instead of SELECT ... WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ... use SELECT ... WHERE (( "," & [Forms]![frmSearch]![tboCriteria] & "," ) LIKE ( "*," & station_id & ",*" )) AND ... where tboCriteria will be filled with, say: Los Angeles,New York,Dallas (note that there is no space after the comas used as sub-string delimiter, in this case). Vanderghast, Access MVP "javablood" wrote in message ... Here is the SQL I am using : PARAMETERS [Forms]![frmSearch]![cboGroup] Text ( 255 ), [Forms]![frmSearch]![lbodatbegin] DateTime, [Forms]![frmSearch]![lbodatend] DateTime, [Forms]![frmSearch]![cboChem] Text ( 255 ), [Forms]![frmSearch]![cboSamptype] Text ( 255 ), [Forms]![frmSearch]![cboDet] Bit, [Forms]![frmSearch]![tboCriteria] Text ( 255 ); SELECT tblHardage.STATION_ID, tblHardage.SAMPLE_DATE, tblHardage.PARAMETER, tblHardage.VALUE, tblHardage.FLAG, tblHardage.UNITS, tblHardagePAR.F1_GROUP, tblHardage.QC, tblHardageSiteIdentification.ID_Group_Explain, tblHardagePAR.F1_GRPORDR, tblHardage.DETECTED FROM tblHardageFLD INNER JOIN ((tblHardage INNER JOIN tblHardagePAR ON tblHardage.PARAMETER = tblHardagePAR.PARAMETER) INNER JOIN tblHardageSiteIdentification ON tblHardage.STATION_ID = tblHardageSiteIdentification.STATION_ID) ON tblHardageFLD.ENTRY = tblHardage.QC WHERE (((tblHardage.STATION_ID)=[Forms]![frmSearch]![tboCriteria]) AND ((tblHardage.SAMPLE_DATE) Between [Forms]![frmSearch]![lbodatbegin] And [Forms]![frmSearch]![lbodatend]) AND ((tblHardagePAR.F1_GROUP)=[Forms]![frmSearch]![cboChem]) AND ((tblHardage.QC)=[Forms]![frmSearch]![cboSamptype]) AND ((tblHardage.DETECTED) Like IIf([Forms]![frmSearch]![cboDet] Is Null,"*",IIf([Forms]![frmSearch]![cboDet]=Yes,True,False)))) ORDER BY tblHardagePAR.F1_GRPORDR; The tboCriteria is for the STATION_ID field from which I want to see the records of all the locations that are in the tboCriteria string, e.g., "A" OR "B" OR "C", etc. Does this help you help me? I have not used the IN before and am not sure how. I am not an experienced Access person. Is there anything else I can provide to clear up what I am trying to do? thanks, -- javablood "vanderghast" wrote: The syntax "Los Angeles" OR "Baltimore" OR "Dallas" is only valid in very few circumstances. It is far better to have: IN( "Los Angeles", "Baltimore", "Dallas" ) and even then, you have to specify which field has to satisfy this IN-ness constraint in many cases. So, without knowing HOW you use that criteria, I strongly suggest that, at least, you change the OR to a coma, and in the end, use Me.tboCriteria = " IN (" & criteria & ")" It may help (but again, depends on what you really do with that tboCriteria thing). Vanderghast, Access MVP "javablood" wrote in message ... Hi, I have a form in which several choices are selected, one of which is a list box in which the user can make multiple selections. I followed the various suggestions to create a String that has the selected items and I set a textbox in the form as that final String as follows: Dim Criteria As String Dim ctl As Control Dim Itm As Variant ' Build a list of the selections. Set ctl = Me![lboSTATID] For Each Itm In ctl.ItemsSelected If Len(Criteria) = 0 Then Criteria = Chr(34) & ctl.ItemData(Itm) & Chr(34) Else Criteria = Criteria & " OR " & Chr(34) & ctl.ItemData(Itm) _ & Chr(34) End If Next Itm Me.tboCriteria = Criteria However, when another form (based on a query with the passed parameters)opens to show me the selection, there are no data. The correct String for the query parameter appears to be correct, i.e., "item1" OR "item2" and if I replace the same String in the query instead of using the [Forms]![frmSearch]![tboCriteria] the query works fine. Where am I going wrong? Any help is appreciated. Thanks, -- javablood |
Thread Tools | |
Display Modes | |
|
|