Insert Items from Yes/No CheckBox into Query

[ryguy7272]

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--

 

[KARL DEWEY]

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?

 

[ryguy7272]

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--

 

[KARL DEWEY]

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.

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''.

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?

 

[ryguy7272]

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''.

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.

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''.

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.


:

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--

 

[KARL DEWEY]

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.

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''.

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.

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''.


:

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.


:

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--

 

[ryguy7272]

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''.

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.

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''.

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.


:

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''.


:

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.


:

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--

 

[KARL DEWEY]

I think you will need this --
Like IIf([Forms]![frmMaster]![chkVest50K]=-1,"chkVest50K","*")

--
Build a little, test a little.

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''.

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.

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''.


:

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.


:

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''.


:

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.


:

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--

 

[ryguy7272]

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''.

I think you will need this --
Like IIf([Forms]![frmMaster]![chkVest50K]=-1,"chkVest50K","*")

--
Build a little, test a little.

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''.

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.


:

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''.


:

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.


:

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''.


:

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.


:

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--