Check Box Use As Criteria

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query where one of the fields is a check box, [Select]. The criteria
comes from a subform in datasheet view
[Forms]![frmSystem]![frmSystemSub]![Select]
I the created another field in the query
Expr1:[Forms]![frmSystem]![frmSystemSub]![Select] and the criteria is False.
This works sometimes.
So what I’m trying to achieve is if [Select] check box is blank all records
are returned but if some are checked only those are displayed after up-date.

Any help appreciated.

Nick
 
If the check box is unchecked (False), you want all records returned.
If it is checked (True), you want to return only the records where MyField
is True.

Switch your query to SQL View (View menu, in query design).
Change the WHERE clause so it looks like this:

WHERE ([Forms]![frmSystem]![frmSystemSub].[Form]![Select] = False) OR
(MyField = True)

If the check box is unchecked, the first part returns True, so the WHERE
clause evaluates to True, and if the WHERE clause evaluates to True for all
records then all records are returned. If the check box is checked, then
the WHERE clause returns True only for those records where MyField is True,
so only those records are returned.

The .Form bit helps Access understand the refererence (especially Access
2003.)
 
Hi Allen,
Thanks for the reply.
I changed the WHERE line as you suggested but no luck. When I change to
Datasheet view I get a pop-up asking for MyFied value. I have posted the SQL
as I have probably typed something wrong?

Hope you can assist
Nick

SELECT [qSamples-1].SampleID, [qSamples-1].CHECKED,
[qSamples-1].GREASE_SAMPLE, [qSamples-1].OIL_SAMPLE, [qSamples-1].VA,
[qSamples-1].FLOC_ID, [qSamples-1].BHP_EQ, [qSamples-1].VA_CODE,
[qSamples-1].Transfered, [qSamples-1].FLOC_DESCRIPTION,
[qSamples-1].MAINTPLANT, [qSamples-1].[COST CENTER], [qSamples-1].[CATALOG
PROFILE], [qSamples-1].OILTYPE, [qSamples-1].FillQuantity, [qSamples-1].s,
[qSamples-1].[Select]
FROM [qSamples-1]
WHERE ([Forms]![frmSystem]![frmSystemSub].[Form]![Select]=False) OR
(MyField=True)
ORDER BY [qSamples-1].FLOC_ID;


Allen Browne said:
If the check box is unchecked (False), you want all records returned.
If it is checked (True), you want to return only the records where MyField
is True.

Switch your query to SQL View (View menu, in query design).
Change the WHERE clause so it looks like this:

WHERE ([Forms]![frmSystem]![frmSystemSub].[Form]![Select] = False) OR
(MyField = True)

If the check box is unchecked, the first part returns True, so the WHERE
clause evaluates to True, and if the WHERE clause evaluates to True for all
records then all records are returned. If the check box is checked, then
the WHERE clause returns True only for those records where MyField is True,
so only those records are returned.

The .Form bit helps Access understand the refererence (especially Access
2003.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Nick hfrupn said:
I have a query where one of the fields is a check box, [Select]. The
criteria
comes from a subform in datasheet view
[Forms]![frmSystem]![frmSystemSub]![Select]
I the created another field in the query
Expr1:[Forms]![frmSystem]![frmSystemSub]![Select] and the criteria is
False.
This works sometimes.
So what I'm trying to achieve is if [Select] check box is blank all
records
are returned but if some are checked only those are displayed after
up-date.

Any help appreciated.

Nick
Click to expand...
Click to expand...
 
Hi Nick
In Allen example the MyField represent the name of the True/Flase field name
in your table.
So, you need to change the name of MyField to the name of the field in the
table




Nick hfrupn said:
Hi Allen,
Thanks for the reply.
I changed the WHERE line as you suggested but no luck. When I change to
Datasheet view I get a pop-up asking for MyFied value. I have posted the SQL
as I have probably typed something wrong?

Hope you can assist
Nick

SELECT [qSamples-1].SampleID, [qSamples-1].CHECKED,
[qSamples-1].GREASE_SAMPLE, [qSamples-1].OIL_SAMPLE, [qSamples-1].VA,
[qSamples-1].FLOC_ID, [qSamples-1].BHP_EQ, [qSamples-1].VA_CODE,
[qSamples-1].Transfered, [qSamples-1].FLOC_DESCRIPTION,
[qSamples-1].MAINTPLANT, [qSamples-1].[COST CENTER], [qSamples-1].[CATALOG
PROFILE], [qSamples-1].OILTYPE, [qSamples-1].FillQuantity, [qSamples-1].s,
[qSamples-1].[Select]
FROM [qSamples-1]
WHERE ([Forms]![frmSystem]![frmSystemSub].[Form]![Select]=False) OR
(MyField=True)
ORDER BY [qSamples-1].FLOC_ID;


Allen Browne said:
If the check box is unchecked (False), you want all records returned.
If it is checked (True), you want to return only the records where MyField
is True.

Switch your query to SQL View (View menu, in query design).
Change the WHERE clause so it looks like this:

WHERE ([Forms]![frmSystem]![frmSystemSub].[Form]![Select] = False) OR
(MyField = True)

If the check box is unchecked, the first part returns True, so the WHERE
clause evaluates to True, and if the WHERE clause evaluates to True for all
records then all records are returned. If the check box is checked, then
the WHERE clause returns True only for those records where MyField is True,
so only those records are returned.

The .Form bit helps Access understand the refererence (especially Access
2003.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Nick hfrupn said:
I have a query where one of the fields is a check box, [Select]. The
criteria
comes from a subform in datasheet view
[Forms]![frmSystem]![frmSystemSub]![Select]
I the created another field in the query
Expr1:[Forms]![frmSystem]![frmSystemSub]![Select] and the criteria is
False.
This works sometimes.
So what I'm trying to achieve is if [Select] check box is blank all
records
are returned but if some are checked only those are displayed after
up-date.

Any help appreciated.

Nick
Click to expand...
Click to expand...
Click to expand...
 
Hi Ofer,
I tried this, WHERE
([Forms]![frmSystem]![frmSystemSub].[Form]![Select]=False) OR ([Select]=True)
but still didn't work

Nick

Ofer said:
Hi Nick
In Allen example the MyField represent the name of the True/Flase field name
in your table.
So, you need to change the name of MyField to the name of the field in the
table




Nick hfrupn said:
Hi Allen,
Thanks for the reply.
I changed the WHERE line as you suggested but no luck. When I change to
Datasheet view I get a pop-up asking for MyFied value. I have posted the SQL
as I have probably typed something wrong?

Hope you can assist
Nick

SELECT [qSamples-1].SampleID, [qSamples-1].CHECKED,
[qSamples-1].GREASE_SAMPLE, [qSamples-1].OIL_SAMPLE, [qSamples-1].VA,
[qSamples-1].FLOC_ID, [qSamples-1].BHP_EQ, [qSamples-1].VA_CODE,
[qSamples-1].Transfered, [qSamples-1].FLOC_DESCRIPTION,
[qSamples-1].MAINTPLANT, [qSamples-1].[COST CENTER], [qSamples-1].[CATALOG
PROFILE], [qSamples-1].OILTYPE, [qSamples-1].FillQuantity, [qSamples-1].s,
[qSamples-1].[Select]
FROM [qSamples-1]
WHERE ([Forms]![frmSystem]![frmSystemSub].[Form]![Select]=False) OR
(MyField=True)
ORDER BY [qSamples-1].FLOC_ID;


Allen Browne said:
If the check box is unchecked (False), you want all records returned.
If it is checked (True), you want to return only the records where MyField
is True.

Switch your query to SQL View (View menu, in query design).
Change the WHERE clause so it looks like this:

WHERE ([Forms]![frmSystem]![frmSystemSub].[Form]![Select] = False) OR
(MyField = True)

If the check box is unchecked, the first part returns True, so the WHERE
clause evaluates to True, and if the WHERE clause evaluates to True for all
records then all records are returned. If the check box is checked, then
the WHERE clause returns True only for those records where MyField is True,
so only those records are returned.

The .Form bit helps Access understand the refererence (especially Access
2003.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a query where one of the fields is a check box, [Select]. The
criteria
comes from a subform in datasheet view
[Forms]![frmSystem]![frmSystemSub]![Select]
I the created another field in the query
Expr1:[Forms]![frmSystem]![frmSystemSub]![Select] and the criteria is
False.
This works sometimes.
So what I'm trying to achieve is if [Select] check box is blank all
records
are returned but if some are checked only those are displayed after
up-date.

Any help appreciated.

Nick
Click to expand...
Click to expand...
Click to expand...
 
What result did you get?

In your original post you said:
what I'm trying to achieve is if [Select] check box is blank
all records are returned but if some are checked only those
are displayed after up-date.

Problems with that logic:
1. The query will only look at the current record in the subform (whichever
that one is.)

2. If this query feeds the subform, you are going around in circles.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Nick hfrupn said:
Hi Ofer,
I tried this, WHERE
([Forms]![frmSystem]![frmSystemSub].[Form]![Select]=False) OR
([Select]=True)
but still didn't work

Nick

Ofer said:
Hi Nick
In Allen example the MyField represent the name of the True/Flase field
name
in your table.
So, you need to change the name of MyField to the name of the field in
the
table




Nick hfrupn said:
Hi Allen,
Thanks for the reply.
I changed the WHERE line as you suggested but no luck. When I change to
Datasheet view I get a pop-up asking for MyFied value. I have posted
the SQL
as I have probably typed something wrong?

Hope you can assist
Nick

SELECT [qSamples-1].SampleID, [qSamples-1].CHECKED,
[qSamples-1].GREASE_SAMPLE, [qSamples-1].OIL_SAMPLE, [qSamples-1].VA,
[qSamples-1].FLOC_ID, [qSamples-1].BHP_EQ, [qSamples-1].VA_CODE,
[qSamples-1].Transfered, [qSamples-1].FLOC_DESCRIPTION,
[qSamples-1].MAINTPLANT, [qSamples-1].[COST CENTER],
[qSamples-1].[CATALOG
PROFILE], [qSamples-1].OILTYPE, [qSamples-1].FillQuantity,
[qSamples-1].s,
[qSamples-1].[Select]
FROM [qSamples-1]
WHERE ([Forms]![frmSystem]![frmSystemSub].[Form]![Select]=False) OR
(MyField=True)
ORDER BY [qSamples-1].FLOC_ID;


:

If the check box is unchecked (False), you want all records returned.
If it is checked (True), you want to return only the records where
MyField
is True.

Switch your query to SQL View (View menu, in query design).
Change the WHERE clause so it looks like this:

WHERE ([Forms]![frmSystem]![frmSystemSub].[Form]![Select] = False) OR
(MyField = True)

If the check box is unchecked, the first part returns True, so the
WHERE
clause evaluates to True, and if the WHERE clause evaluates to True
for all
records then all records are returned. If the check box is checked,
then
the WHERE clause returns True only for those records where MyField is
True,
so only those records are returned.

The .Form bit helps Access understand the refererence (especially
Access
2003.)

I have a query where one of the fields is a check box, [Select]. The
criteria
comes from a subform in datasheet view
[Forms]![frmSystem]![frmSystemSub]![Select]
I the created another field in the query
Expr1:[Forms]![frmSystem]![frmSystemSub]![Select] and the criteria
is
False.
This works sometimes.
So what I'm trying to achieve is if [Select] check box is blank all
records
are returned but if some are checked only those are displayed after
up-date.
Click to expand...
Click to expand...
Click to expand...
 
Allen,
Thanks for your reply. It makes sense what you’re saying as that would
explain why sometimes it works and why sometimes it doesn’t.
I’ll give it some thought to see if I can find a different approach

Thanks again

Nick


Allen Browne said:
What result did you get?

In your original post you said:
what I'm trying to achieve is if [Select] check box is blank
all records are returned but if some are checked only those
are displayed after up-date.

Problems with that logic:
1. The query will only look at the current record in the subform (whichever
that one is.)

2. If this query feeds the subform, you are going around in circles.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Nick hfrupn said:
Hi Ofer,
I tried this, WHERE
([Forms]![frmSystem]![frmSystemSub].[Form]![Select]=False) OR
([Select]=True)
but still didn't work

Nick

Ofer said:
Hi Nick
In Allen example the MyField represent the name of the True/Flase field
name
in your table.
So, you need to change the name of MyField to the name of the field in
the
table




:

Hi Allen,
Thanks for the reply.
I changed the WHERE line as you suggested but no luck. When I change to
Datasheet view I get a pop-up asking for MyFied value. I have posted
the SQL
as I have probably typed something wrong?

Hope you can assist
Nick

SELECT [qSamples-1].SampleID, [qSamples-1].CHECKED,
[qSamples-1].GREASE_SAMPLE, [qSamples-1].OIL_SAMPLE, [qSamples-1].VA,
[qSamples-1].FLOC_ID, [qSamples-1].BHP_EQ, [qSamples-1].VA_CODE,
[qSamples-1].Transfered, [qSamples-1].FLOC_DESCRIPTION,
[qSamples-1].MAINTPLANT, [qSamples-1].[COST CENTER],
[qSamples-1].[CATALOG
PROFILE], [qSamples-1].OILTYPE, [qSamples-1].FillQuantity,
[qSamples-1].s,
[qSamples-1].[Select]
FROM [qSamples-1]
WHERE ([Forms]![frmSystem]![frmSystemSub].[Form]![Select]=False) OR
(MyField=True)
ORDER BY [qSamples-1].FLOC_ID;


:

If the check box is unchecked (False), you want all records returned.
If it is checked (True), you want to return only the records where
MyField
is True.

Switch your query to SQL View (View menu, in query design).
Change the WHERE clause so it looks like this:

WHERE ([Forms]![frmSystem]![frmSystemSub].[Form]![Select] = False) OR
(MyField = True)

If the check box is unchecked, the first part returns True, so the
WHERE
clause evaluates to True, and if the WHERE clause evaluates to True
for all
records then all records are returned. If the check box is checked,
then
the WHERE clause returns True only for those records where MyField is
True,
so only those records are returned.

The .Form bit helps Access understand the refererence (especially
Access
2003.)

I have a query where one of the fields is a check box, [Select]. The
criteria
comes from a subform in datasheet view
[Forms]![frmSystem]![frmSystemSub]![Select]
I the created another field in the query
Expr1:[Forms]![frmSystem]![frmSystemSub]![Select] and the criteria
is
False.
This works sometimes.
So what I'm trying to achieve is if [Select] check box is blank all
records
are returned but if some are checked only those are displayed after
up-date.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access 2003 query criteria issue.... 3
Expression for Criteria for Form with Combo Boxes 1
Need VBA Code for Check Box 8
list box criteria 4
criteria failure 7
Criteria & <> 4
Can a query be ran by check boxes in a form? 1
textbox value to be inserted in the combobox 3

Back
Top