Insert Items from Yes/No CheckBox into Query
in [Queries]
|
From: KARL DEWEY on 5 Feb 2010 15:01 I do not follow your criteria -- WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) Isn't cboGroup your check box and therefore will have -1 or 0? What values are in tblStocksGroup.Group that you have set to test for equal? The same for (tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) ? -- Build a little, test a little. "ryguy7272" wrote: > Ok; thanks! That kind of makes sense, but may be a bit beyond me. I think > the SQL should be something like this: > SELECT SharePrices.DateTime, SharePrices.StockSymbol, > SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group, > tblStocksGroup.Class, tblStocksGroup.HDVest50k > FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol = > tblStocksGroup.Ticker > WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND > ((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) AND > ((tblStocksGroup.HDVest50k)=IIf([Forms]![frmMaster]![chkVest50K]=0,True,False))) > ORDER BY SharePrices.DateTime; > > With this being operative: > AND > ((tblStocksGroup.HDVest50k)=IIf([Forms]![frmMaster]![chkVest50K]=-1,True,False)) > > However, I can't seem to get that to work. When I check the box, I get no > values returned, and I would expect to see five records, actually. > > -1 = yes, right. > > > -- > Ryan--- > If this information was helpful, please indicate this by clicking ''Yes''. > > > "KARL DEWEY" wrote: > > > One way would be to create a table (My_Carrier_Flight) with 2 fields - > > Carrier - Text and Flight - Yes/No. > > Create update query to set all Flight to 0 (zero) - Flight_Zero > > Create query to pull up these 2 fields. > > > > Use command button on form that has the check boxes to run a macro with > > following actions and conditions -- > > OpenQuery Flight_Zero > > OpenQuery Carrier_Flight > > GoToControl Carrier > > FindRecord "Delta" > > SetValue Flight = -1 {Condition} chkDelta = -1 > > GoToControl Carrier > > FindRecord "JetBlue" > > SetValue Flight = -1 {Condition} chkJetBlue = -1 > > GoToControl Carrier > > FindRecord "Continental" > > SetValue Flight = -1 {Condition} chkContinental = -1 > > .... etc. > > > > Then you can use My_Carrier_Flight as criteria in query design view -- > > FIELD YourAirLineField Flight > > TABLE YourTableName My_Carrier_Flight > > CRITERIA My_Carrier_Flight.Carrier -1 > > > > -- > > Build a little, test a little. > > > > > > "ryguy7272" wrote: > > > > > Good question! I have 20 CheckBoxes on a Form, bound to a Table. I want to > > > be able to click on a few CheckBoxes, say 'Continental', 'Jet Blue', 'Delta', > > > and 'Southwest', and add those elements to a Query. I have this macro now: > > > Private Sub CallQuery_Click() > > > > > > Dim strClass As String > > > Dim strGroup As String > > > > > > Dim db As DAO.Database > > > Dim qdf As DAO.QueryDef > > > Dim strSQL As String > > > Set db = CurrentDb > > > Set qdf = db.QueryDefs("qrySummary") > > > > > > ' Close the query if it is already open > > > If SysCmd(acSysCmdGetObjectState, acQuery, "qrySummary") = > > > acObjStateOpen Then > > > DoCmd.Close acQuery, "qrySummary" > > > End If > > > > > > ' Get the values from the combo boxes > > > If IsNull(Me.cboClass.Value) Then > > > strClass = " Like '*' " > > > Else > > > strClass = "='" & Me.cboClass.Value & "' " > > > End If > > > > > > If IsNull(Me.cboGroup.Value) Then > > > strGroup = " Like '*' " > > > Else > > > strGroup = "='" & Me.cboGroup.Value & "' " > > > End If > > > > > > ' Build the SQL string > > > strSQL = "SELECT SharePrices.DateTime, SharePrices.StockSymbol, > > > SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group, > > > tblStocksGroup.Class " & vbCrLf & _ > > > "FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol = > > > tblStocksGroup.Ticker " & vbCrLf & _ > > > "WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND > > > ((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass])) " & vbCrLf & _ > > > "Order by SharePrices.DateTime;" > > > > > > > > > ' Open the Query > > > qdf.SQL = strSQL > > > DoCmd.OpenQuery "qrySummary" > > > Set qdf = Nothing > > > Set db = Nothing > > > End Sub > > > > > > This takes strings from two ComboBoxes (cboClass and cboGroup) and passes > > > the string to a Query. this part works fine. I'm trying to add a capability > > > to choose from 20 CheckBoxes, and add these to the Query. Is that hard to > > > do? I'm sure it's possbile in Access, but I don't know how to do it. I've > > > done it in Excel, but not Access. Any thoughts or suggestions? > > > > > > The names of the controls are pretty intuitive: chkJetBlue, chkContinental, > > > etc. > > > > > > Thanks, > > > Ryan-- > > > > > > -- > > > Ryan--- > > > If this information was helpful, please indicate this by clicking ''Yes''. > > > > > > > > > "KARL DEWEY" wrote: > > > > > > > >>dynamically insert results from 20 yes/no checkboxes into a Query. > > > > I do not follow you question. Do you want to put different checkbox results > > > > in the query on the fly so that one time the results displays 1 through 5 and > > > > next time all or some comination? > > > > > > > > -- > > > > Build a little, test a little. > > > > > > > > > > > > "ryguy7272" wrote: > > > > > > > > > I am trying to figure out how to dynamically insert results from 20 yes/no > > > > > checkboxes into a Query. I'm pretty sure it will require VBA and some kind > > > > > of loop will be required, right; > > > > > For Each. . .Next > > > > > > > > > > Should I add some kind of unbound object frame? Is there a good demo of how > > > > > to do this online somewhere? I googled around for an example this AM, but > > > > > didn't come up with anything useful. > > > > > > > > > > Thanks! > > > > > Ryan-- > > > > > -- > > > > > Ryan--- > > > > > If this information was helpful, please indicate this by clicking ''Yes''.
From: ryguy7272 on 5 Feb 2010 15:27 The SQL takes strings from two ComboBoxes (cboClass and cboGroup) and passes the string to a Query. This part works. What I'm trying to do now is add some additional additional functionality to allow a user to choose from 20 CheckBoxes, and pass these parameters these to the Query. Some of the names of the CheckBoxes are 'chkVest50K', 'chkContinental', 'chkJet Blue', 'chkDelta', etc. I thought the logic would be something like this: IIf([Forms]![frmMaster]![chkVest50K]=-1,True,False Or... IIf([Forms]![frmMaster]![chkVest50K]=-1,"chkVest50K","" I tried several combinations of things; nothing is working so far. > and 'Southwest', -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "KARL DEWEY" wrote: > I do not follow your criteria -- > WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) > Isn't cboGroup your check box and therefore will have -1 or 0? What values > are in tblStocksGroup.Group that you have set to test for equal? > > The same for (tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) ? > > > -- > Build a little, test a little. > > > "ryguy7272" wrote: > > > Ok; thanks! That kind of makes sense, but may be a bit beyond me. I think > > the SQL should be something like this: > > SELECT SharePrices.DateTime, SharePrices.StockSymbol, > > SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group, > > tblStocksGroup.Class, tblStocksGroup.HDVest50k > > FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol = > > tblStocksGroup.Ticker > > WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND > > ((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) AND > > ((tblStocksGroup.HDVest50k)=IIf([Forms]![frmMaster]![chkVest50K]=0,True,False))) > > ORDER BY SharePrices.DateTime; > > > > With this being operative: > > AND > > ((tblStocksGroup.HDVest50k)=IIf([Forms]![frmMaster]![chkVest50K]=-1,True,False)) > > > > However, I can't seem to get that to work. When I check the box, I get no > > values returned, and I would expect to see five records, actually. > > > > -1 = yes, right. > > > > > > -- > > Ryan--- > > If this information was helpful, please indicate this by clicking ''Yes''. > > > > > > "KARL DEWEY" wrote: > > > > > One way would be to create a table (My_Carrier_Flight) with 2 fields - > > > Carrier - Text and Flight - Yes/No. > > > Create update query to set all Flight to 0 (zero) - Flight_Zero > > > Create query to pull up these 2 fields. > > > > > > Use command button on form that has the check boxes to run a macro with > > > following actions and conditions -- > > > OpenQuery Flight_Zero > > > OpenQuery Carrier_Flight > > > GoToControl Carrier > > > FindRecord "Delta" > > > SetValue Flight = -1 {Condition} chkDelta = -1 > > > GoToControl Carrier > > > FindRecord "JetBlue" > > > SetValue Flight = -1 {Condition} chkJetBlue = -1 > > > GoToControl Carrier > > > FindRecord "Continental" > > > SetValue Flight = -1 {Condition} chkContinental = -1 > > > .... etc. > > > > > > Then you can use My_Carrier_Flight as criteria in query design view -- > > > FIELD YourAirLineField Flight > > > TABLE YourTableName My_Carrier_Flight > > > CRITERIA My_Carrier_Flight.Carrier -1 > > > > > > -- > > > Build a little, test a little. > > > > > > > > > "ryguy7272" wrote: > > > > > > > Good question! I have 20 CheckBoxes on a Form, bound to a Table. I want to > > > > be able to click on a few CheckBoxes, say 'Continental', 'Jet Blue', 'Delta', > > > > and 'Southwest', and add those elements to a Query. I have this macro now: > > > > Private Sub CallQuery_Click() > > > > > > > > Dim strClass As String > > > > Dim strGroup As String > > > > > > > > Dim db As DAO.Database > > > > Dim qdf As DAO.QueryDef > > > > Dim strSQL As String > > > > Set db = CurrentDb > > > > Set qdf = db.QueryDefs("qrySummary") > > > > > > > > ' Close the query if it is already open > > > > If SysCmd(acSysCmdGetObjectState, acQuery, "qrySummary") = > > > > acObjStateOpen Then > > > > DoCmd.Close acQuery, "qrySummary" > > > > End If > > > > > > > > ' Get the values from the combo boxes > > > > If IsNull(Me.cboClass.Value) Then > > > > strClass = " Like '*' " > > > > Else > > > > strClass = "='" & Me.cboClass.Value & "' " > > > > End If > > > > > > > > If IsNull(Me.cboGroup.Value) Then > > > > strGroup = " Like '*' " > > > > Else > > > > strGroup = "='" & Me.cboGroup.Value & "' " > > > > End If > > > > > > > > ' Build the SQL string > > > > strSQL = "SELECT SharePrices.DateTime, SharePrices.StockSymbol, > > > > SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group, > > > > tblStocksGroup.Class " & vbCrLf & _ > > > > "FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol = > > > > tblStocksGroup.Ticker " & vbCrLf & _ > > > > "WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND > > > > ((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass])) " & vbCrLf & _ > > > > "Order by SharePrices.DateTime;" > > > > > > > > > > > > ' Open the Query > > > > qdf.SQL = strSQL > > > > DoCmd.OpenQuery "qrySummary" > > > > Set qdf = Nothing > > > > Set db = Nothing > > > > End Sub > > > > > > > > This takes strings from two ComboBoxes (cboClass and cboGroup) and passes > > > > the string to a Query. this part works fine. I'm trying to add a capability > > > > to choose from 20 CheckBoxes, and add these to the Query. Is that hard to > > > > do? I'm sure it's possbile in Access, but I don't know how to do it. I've > > > > done it in Excel, but not Access. Any thoughts or suggestions? > > > > > > > > The names of the controls are pretty intuitive: chkJetBlue, chkContinental, > > > > etc. > > > > > > > > Thanks, > > > > Ryan-- > > > > > > > > -- > > > > Ryan--- > > > > If this information was helpful, please indicate this by clicking ''Yes''. > > > > > > > > > > > > "KARL DEWEY" wrote: > > > > > > > > > >>dynamically insert results from 20 yes/no checkboxes into a Query. > > > > > I do not follow you question. Do you want to put different checkbox results > > > > > in the query on the fly so that one time the results displays 1 through 5 and > > > > > next time all or some comination? > > > > > > > > > > -- > > > > > Build a little, test a little. > > > > > > > > > > > > > > > "ryguy7272" wrote: > > > > > > > > > > > I am trying to figure out how to dynamically insert results from 20 yes/no > > > > > > checkboxes into a Query. I'm pretty sure it will require VBA and some kind > > > > > > of loop will be required, right; > > > > > > For Each. . .Next > > > > > > > > > > > > Should I add some kind of unbound object frame? Is there a good demo of how > > > > > > to do this online somewhere? I googled around for an example this AM, but > > > > > > didn't come up with anything useful. > > > > > > > > > > > > Thanks! > > > > > > Ryan-- > > > > > > -- > > > > > > Ryan--- > > > > > > If this information was helpful, please indicate this by clicking ''Yes''.
From: KARL DEWEY on 5 Feb 2010 15:42 I think you will need this -- Like IIf([Forms]![frmMaster]![chkVest50K]=-1,"chkVest50K","*") -- Build a little, test a little. "ryguy7272" wrote: > The SQL takes strings from two ComboBoxes (cboClass and cboGroup) and passes > the string to a Query. This part works. What I'm trying to do now is add > some additional additional functionality to allow a user to choose from 20 > CheckBoxes, and pass these parameters these to the Query. > > Some of the names of the CheckBoxes are 'chkVest50K', 'chkContinental', > 'chkJet Blue', 'chkDelta', etc. > > I thought the logic would be something like this: > IIf([Forms]![frmMaster]![chkVest50K]=-1,True,False > > Or... > IIf([Forms]![frmMaster]![chkVest50K]=-1,"chkVest50K","" > > I tried several combinations of things; nothing is working so far. > > > > and 'Southwest', > > -- > Ryan--- > If this information was helpful, please indicate this by clicking ''Yes''. > > > "KARL DEWEY" wrote: > > > I do not follow your criteria -- > > WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) > > Isn't cboGroup your check box and therefore will have -1 or 0? What values > > are in tblStocksGroup.Group that you have set to test for equal? > > > > The same for (tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) ? > > > > > > -- > > Build a little, test a little. > > > > > > "ryguy7272" wrote: > > > > > Ok; thanks! That kind of makes sense, but may be a bit beyond me. I think > > > the SQL should be something like this: > > > SELECT SharePrices.DateTime, SharePrices.StockSymbol, > > > SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group, > > > tblStocksGroup.Class, tblStocksGroup.HDVest50k > > > FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol = > > > tblStocksGroup.Ticker > > > WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND > > > ((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) AND > > > ((tblStocksGroup.HDVest50k)=IIf([Forms]![frmMaster]![chkVest50K]=0,True,False))) > > > ORDER BY SharePrices.DateTime; > > > > > > With this being operative: > > > AND > > > ((tblStocksGroup.HDVest50k)=IIf([Forms]![frmMaster]![chkVest50K]=-1,True,False)) > > > > > > However, I can't seem to get that to work. When I check the box, I get no > > > values returned, and I would expect to see five records, actually. > > > > > > -1 = yes, right. > > > > > > > > > -- > > > Ryan--- > > > If this information was helpful, please indicate this by clicking ''Yes''. > > > > > > > > > "KARL DEWEY" wrote: > > > > > > > One way would be to create a table (My_Carrier_Flight) with 2 fields - > > > > Carrier - Text and Flight - Yes/No. > > > > Create update query to set all Flight to 0 (zero) - Flight_Zero > > > > Create query to pull up these 2 fields. > > > > > > > > Use command button on form that has the check boxes to run a macro with > > > > following actions and conditions -- > > > > OpenQuery Flight_Zero > > > > OpenQuery Carrier_Flight > > > > GoToControl Carrier > > > > FindRecord "Delta" > > > > SetValue Flight = -1 {Condition} chkDelta = -1 > > > > GoToControl Carrier > > > > FindRecord "JetBlue" > > > > SetValue Flight = -1 {Condition} chkJetBlue = -1 > > > > GoToControl Carrier > > > > FindRecord "Continental" > > > > SetValue Flight = -1 {Condition} chkContinental = -1 > > > > .... etc. > > > > > > > > Then you can use My_Carrier_Flight as criteria in query design view -- > > > > FIELD YourAirLineField Flight > > > > TABLE YourTableName My_Carrier_Flight > > > > CRITERIA My_Carrier_Flight.Carrier -1 > > > > > > > > -- > > > > Build a little, test a little. > > > > > > > > > > > > "ryguy7272" wrote: > > > > > > > > > Good question! I have 20 CheckBoxes on a Form, bound to a Table. I want to > > > > > be able to click on a few CheckBoxes, say 'Continental', 'Jet Blue', 'Delta', > > > > > and 'Southwest', and add those elements to a Query. I have this macro now: > > > > > Private Sub CallQuery_Click() > > > > > > > > > > Dim strClass As String > > > > > Dim strGroup As String > > > > > > > > > > Dim db As DAO.Database > > > > > Dim qdf As DAO.QueryDef > > > > > Dim strSQL As String > > > > > Set db = CurrentDb > > > > > Set qdf = db.QueryDefs("qrySummary") > > > > > > > > > > ' Close the query if it is already open > > > > > If SysCmd(acSysCmdGetObjectState, acQuery, "qrySummary") = > > > > > acObjStateOpen Then > > > > > DoCmd.Close acQuery, "qrySummary" > > > > > End If > > > > > > > > > > ' Get the values from the combo boxes > > > > > If IsNull(Me.cboClass.Value) Then > > > > > strClass = " Like '*' " > > > > > Else > > > > > strClass = "='" & Me.cboClass.Value & "' " > > > > > End If > > > > > > > > > > If IsNull(Me.cboGroup.Value) Then > > > > > strGroup = " Like '*' " > > > > > Else > > > > > strGroup = "='" & Me.cboGroup.Value & "' " > > > > > End If > > > > > > > > > > ' Build the SQL string > > > > > strSQL = "SELECT SharePrices.DateTime, SharePrices.StockSymbol, > > > > > SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group, > > > > > tblStocksGroup.Class " & vbCrLf & _ > > > > > "FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol = > > > > > tblStocksGroup.Ticker " & vbCrLf & _ > > > > > "WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND > > > > > ((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass])) " & vbCrLf & _ > > > > > "Order by SharePrices.DateTime;" > > > > > > > > > > > > > > > ' Open the Query > > > > > qdf.SQL = strSQL > > > > > DoCmd.OpenQuery "qrySummary" > > > > > Set qdf = Nothing > > > > > Set db = Nothing > > > > > End Sub > > > > > > > > > > This takes strings from two ComboBoxes (cboClass and cboGroup) and passes > > > > > the string to a Query. this part works fine. I'm trying to add a capability > > > > > to choose from 20 CheckBoxes, and add these to the Query. Is that hard to > > > > > do? I'm sure it's possbile in Access, but I don't know how to do it. I've > > > > > done it in Excel, but not Access. Any thoughts or suggestions? > > > > > > > > > > The names of the controls are pretty intuitive: chkJetBlue, chkContinental, > > > > > etc. > > > > > > > > > > Thanks, > > > > > Ryan-- > > > > > > > > > > -- > > > > > Ryan--- > > > > > If this information was helpful, please indicate this by clicking ''Yes''. > > > > > > > > > > > > > > > "KARL DEWEY" wrote: > > > > > > > > > > > >>dynamically insert results from 20 yes/no checkboxes into a Query. > > > > > > I do not follow you question. Do you want to put different checkbox results > > > > > > in the query on the fly so that one time the results displays 1 through 5 and > > > > > > next time all or some comination? > > > > > > > > > > > > -- > > > > > > Build a little, test a little. > > > > > > > > > > > > > > > > > > "ryguy7272" wrote: > > > > > > > > > > > > > I am trying to figure out how to dynamically insert results from 20 yes/no > > > > > > > checkboxes into a Query. I'm pretty sure it will require VBA and some kind > > > > > > > of loop will be required, right; > > > > > > > For Each. . .Next > > > > > > > > > > > > > > Should I add some kind of unbound object frame? Is there a good demo of how > > > > > > > to do this online somewhere? I googled around for an example this AM, but > > > > > > > didn't come up with anything useful. > > > > > > > > > > > > > > Thanks! > > > > > > > Ryan-- > > > > > > > -- > > > > > > > Ryan--- > > > > > > > If this information was helpful, please indicate this by clicking ''Yes''.
From: ryguy7272 on 5 Feb 2010 16:21 Perfect!! When I saw it, I knew it would work, Karl! I just couldn't seem to get out of the rut I was in. Thanks for showing me the way. Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "KARL DEWEY" wrote: > I think you will need this -- > Like IIf([Forms]![frmMaster]![chkVest50K]=-1,"chkVest50K","*") > > -- > Build a little, test a little. > > > "ryguy7272" wrote: > > > The SQL takes strings from two ComboBoxes (cboClass and cboGroup) and passes > > the string to a Query. This part works. What I'm trying to do now is add > > some additional additional functionality to allow a user to choose from 20 > > CheckBoxes, and pass these parameters these to the Query. > > > > Some of the names of the CheckBoxes are 'chkVest50K', 'chkContinental', > > 'chkJet Blue', 'chkDelta', etc. > > > > I thought the logic would be something like this: > > IIf([Forms]![frmMaster]![chkVest50K]=-1,True,False > > > > Or... > > IIf([Forms]![frmMaster]![chkVest50K]=-1,"chkVest50K","" > > > > I tried several combinations of things; nothing is working so far. > > > > > > > and 'Southwest', > > > > -- > > Ryan--- > > If this information was helpful, please indicate this by clicking ''Yes''. > > > > > > "KARL DEWEY" wrote: > > > > > I do not follow your criteria -- > > > WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) > > > Isn't cboGroup your check box and therefore will have -1 or 0? What values > > > are in tblStocksGroup.Group that you have set to test for equal? > > > > > > The same for (tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) ? > > > > > > > > > -- > > > Build a little, test a little. > > > > > > > > > "ryguy7272" wrote: > > > > > > > Ok; thanks! That kind of makes sense, but may be a bit beyond me. I think > > > > the SQL should be something like this: > > > > SELECT SharePrices.DateTime, SharePrices.StockSymbol, > > > > SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group, > > > > tblStocksGroup.Class, tblStocksGroup.HDVest50k > > > > FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol = > > > > tblStocksGroup.Ticker > > > > WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND > > > > ((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass]) AND > > > > ((tblStocksGroup.HDVest50k)=IIf([Forms]![frmMaster]![chkVest50K]=0,True,False))) > > > > ORDER BY SharePrices.DateTime; > > > > > > > > With this being operative: > > > > AND > > > > ((tblStocksGroup.HDVest50k)=IIf([Forms]![frmMaster]![chkVest50K]=-1,True,False)) > > > > > > > > However, I can't seem to get that to work. When I check the box, I get no > > > > values returned, and I would expect to see five records, actually. > > > > > > > > -1 = yes, right. > > > > > > > > > > > > -- > > > > Ryan--- > > > > If this information was helpful, please indicate this by clicking ''Yes''. > > > > > > > > > > > > "KARL DEWEY" wrote: > > > > > > > > > One way would be to create a table (My_Carrier_Flight) with 2 fields - > > > > > Carrier - Text and Flight - Yes/No. > > > > > Create update query to set all Flight to 0 (zero) - Flight_Zero > > > > > Create query to pull up these 2 fields. > > > > > > > > > > Use command button on form that has the check boxes to run a macro with > > > > > following actions and conditions -- > > > > > OpenQuery Flight_Zero > > > > > OpenQuery Carrier_Flight > > > > > GoToControl Carrier > > > > > FindRecord "Delta" > > > > > SetValue Flight = -1 {Condition} chkDelta = -1 > > > > > GoToControl Carrier > > > > > FindRecord "JetBlue" > > > > > SetValue Flight = -1 {Condition} chkJetBlue = -1 > > > > > GoToControl Carrier > > > > > FindRecord "Continental" > > > > > SetValue Flight = -1 {Condition} chkContinental = -1 > > > > > .... etc. > > > > > > > > > > Then you can use My_Carrier_Flight as criteria in query design view -- > > > > > FIELD YourAirLineField Flight > > > > > TABLE YourTableName My_Carrier_Flight > > > > > CRITERIA My_Carrier_Flight.Carrier -1 > > > > > > > > > > -- > > > > > Build a little, test a little. > > > > > > > > > > > > > > > "ryguy7272" wrote: > > > > > > > > > > > Good question! I have 20 CheckBoxes on a Form, bound to a Table. I want to > > > > > > be able to click on a few CheckBoxes, say 'Continental', 'Jet Blue', 'Delta', > > > > > > and 'Southwest', and add those elements to a Query. I have this macro now: > > > > > > Private Sub CallQuery_Click() > > > > > > > > > > > > Dim strClass As String > > > > > > Dim strGroup As String > > > > > > > > > > > > Dim db As DAO.Database > > > > > > Dim qdf As DAO.QueryDef > > > > > > Dim strSQL As String > > > > > > Set db = CurrentDb > > > > > > Set qdf = db.QueryDefs("qrySummary") > > > > > > > > > > > > ' Close the query if it is already open > > > > > > If SysCmd(acSysCmdGetObjectState, acQuery, "qrySummary") = > > > > > > acObjStateOpen Then > > > > > > DoCmd.Close acQuery, "qrySummary" > > > > > > End If > > > > > > > > > > > > ' Get the values from the combo boxes > > > > > > If IsNull(Me.cboClass.Value) Then > > > > > > strClass = " Like '*' " > > > > > > Else > > > > > > strClass = "='" & Me.cboClass.Value & "' " > > > > > > End If > > > > > > > > > > > > If IsNull(Me.cboGroup.Value) Then > > > > > > strGroup = " Like '*' " > > > > > > Else > > > > > > strGroup = "='" & Me.cboGroup.Value & "' " > > > > > > End If > > > > > > > > > > > > ' Build the SQL string > > > > > > strSQL = "SELECT SharePrices.DateTime, SharePrices.StockSymbol, > > > > > > SharePrices.StockPrice, tblStocksGroup.Company, tblStocksGroup.Group, > > > > > > tblStocksGroup.Class " & vbCrLf & _ > > > > > > "FROM SharePrices INNER JOIN tblStocksGroup ON SharePrices.StockSymbol = > > > > > > tblStocksGroup.Ticker " & vbCrLf & _ > > > > > > "WHERE (((tblStocksGroup.Group)=[Forms]![frmMaster]![cboGroup]) AND > > > > > > ((tblStocksGroup.Class)=[Forms]![frmMaster]![cboClass])) " & vbCrLf & _ > > > > > > "Order by SharePrices.DateTime;" > > > > > > > > > > > > > > > > > > ' Open the Query > > > > > > qdf.SQL = strSQL > > > > > > DoCmd.OpenQuery "qrySummary" > > > > > > Set qdf = Nothing > > > > > > Set db = Nothing > > > > > > End Sub > > > > > > > > > > > > This takes strings from two ComboBoxes (cboClass and cboGroup) and passes > > > > > > the string to a Query. this part works fine. I'm trying to add a capability > > > > > > to choose from 20 CheckBoxes, and add these to the Query. Is that hard to > > > > > > do? I'm sure it's possbile in Access, but I don't know how to do it. I've > > > > > > done it in Excel, but not Access. Any thoughts or suggestions? > > > > > > > > > > > > The names of the controls are pretty intuitive: chkJetBlue, chkContinental, > > > > > > etc. > > > > > > > > > > > > Thanks, > > > > > > Ryan-- > > > > > > > > > > > > -- > > > > > > Ryan--- > > > > > > If this information was helpful, please indicate this by clicking ''Yes''. > > > > > > > > > > > > > > > > > > "KARL DEWEY" wrote: > > > > > > > > > > > > > >>dynamically insert results from 20 yes/no checkboxes into a Query. > > > > > > > I do not follow you question. Do you want to put different checkbox results > > > > > > > in the query on the fly so that one time the results displays 1 through 5 and > > > > > > > next time all or some comination? > > > > > > > > > > > > > > -- > > > > > > > Build a little, test a little. > > > > > > > > > > > > > > > > > > > > > "ryguy7272" wrote: > > > > > > > > > > > > > > > I am trying to figure out how to dynamically insert results from 20 yes/no > > > > > > > > checkboxes into a Query. I'm pretty sure it will require VBA and some kind > > > > > > > > of loop will be required, right; > > > > > > > > For Each. . .Next > > > > > > > > > > > > > > > > Should I add some kind of unbound object frame? Is there a good demo of how > > > > > > > > to do this online somewhere? I googled around for an example this AM, but > > > > > > > > didn't come up with anything useful. > > > > > > > > > > > > > > > > Thanks! > > > > > > > > Ryan-- > > > > > > > > -- > > > > > > > > Ryan--- > > > > > > > > If this information was helpful, please indicate this by clicking ''Yes''.
First
|
Prev
|
Pages: 1 2 Prev: Delete Query Next: I need to add data to a table from another table how do I acheive |