Conditional Querying

[Guy Hocking]

Hi there,

I have a form with multiple text boxes and check boxes.

The check boxes (3 of them) all are used to select ALL of the data and the
relevant text box is disabled when selected.

What i need is, When the check box is ticked (for that column in the query),
select all the data, else look at the text box entries.

Basically, when the check box is ticked, it needs to ignore the values
(whatever they are) in the text box/es, and display all the data in that
column.
this needs to be done on 3 seperate columns in one query, but i have no idea
where to input the code, into the criteria in the query? or in the button on
the form that runs the query?

I am new to Access so setup neccessities will need to be explained,

Thanks for your patience

--
GH

www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

 

[Steve Schapel]

Guy,

Possibly the most straightforward way of handling this:
In the criteria of the query for each of the three fields, put the
equivalent of...
[Forms]![FormName]![TextboxName] Or [Forms]![FormName]![CheckboxName]=-1
Note that after you save the query, Access will re-arrange this to suit
its own purposes... don't worry that it looks different next time you
look at the query design.

 

[John Vinson]

I have a form with multiple text boxes and check boxes.

The check boxes (3 of them) all are used to select ALL of the data and the
relevant text box is disabled when selected.

What i need is, When the check box is ticked (for that column in the query),
select all the data, else look at the text box entries.

Basically, when the check box is ticked, it needs to ignore the values
(whatever they are) in the text box/es, and display all the data in that
column.

A criterion of

WHERE (Forms!yourform!checkbox1 = True OR ([fieldname1] =
[Forms]![yourform]![textfield1])
AND (Forms!yourform!checkbox2 = True OR ([fieldname2] =
[Forms]![yourform]![textfield2])
AND (Forms!yourform!checkbox3 = True OR ([fieldname3] =
[Forms]![yourform]![textfield3])

or analogous should do the trick...

 

[Guy Hocking]

A bit of jiggery pokery and what you proposed its fine, John

Thanks alot for that guys, a great help!



--
GH
www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

I have a form with multiple text boxes and check boxes.

The check boxes (3 of them) all are used to select ALL of the data and the
relevant text box is disabled when selected.

What i need is, When the check box is ticked (for that column in the query),
select all the data, else look at the text box entries.

Basically, when the check box is ticked, it needs to ignore the values
(whatever they are) in the text box/es, and display all the data in that
column.

A criterion of

WHERE (Forms!yourform!checkbox1 = True OR ([fieldname1] =
[Forms]![yourform]![textfield1])
AND (Forms!yourform!checkbox2 = True OR ([fieldname2] =
[Forms]![yourform]![textfield2])
AND (Forms!yourform!checkbox3 = True OR ([fieldname3] =
[Forms]![yourform]![textfield3])

or analogous should do the trick...

 

[Guy Hocking]

Aha, a problem.

At first it appeared to work. but now, if i have a mixture of checkboxes and
textbox values, it doesnt combine the two. It just comes up with nothing.

Basically, i have 6 text boxes - 3 pairs.=

txt_StartDate and txt_EndDate
txt_StartTime and txt_EndTime
txt_StartPortNumber and txt_EndPortNumber

and 3 check boxes

chk_AllDates
chk_AllTimes
chk_AllPortNos

I need to be able to search in any combination of these values, and when a
check box is selected it overrides the relevant pair of text boxes (and also
disables them, which i have done) and selects all data in that column.

so i could have - All Dates, between 17.00 and 22.00, All Port Numbers
Or any mixture of them.

At the moment, all that works is if i select All check boxes, which displays
all data (which is right) and if i enter all criteria, with no checkboxes
selected.
Also if there is data in the text boxes, and the check box is also selected
(disabling the text boxes) it still reads the data in the text boxes.
How can i get the check boxes to "override" the text boxes, no matter what.

Thanks, and sorry...

GH



--
GH
www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

I have a form with multiple text boxes and check boxes.

The check boxes (3 of them) all are used to select ALL of the data and the
relevant text box is disabled when selected.

What i need is, When the check box is ticked (for that column in the query),
select all the data, else look at the text box entries.

Basically, when the check box is ticked, it needs to ignore the values
(whatever they are) in the text box/es, and display all the data in that
column.

A criterion of

WHERE (Forms!yourform!checkbox1 = True OR ([fieldname1] =
[Forms]![yourform]![textfield1])
AND (Forms!yourform!checkbox2 = True OR ([fieldname2] =
[Forms]![yourform]![textfield2])
AND (Forms!yourform!checkbox3 = True OR ([fieldname3] =
[Forms]![yourform]![textfield3])

or analogous should do the trick...

 

[Guy Hocking]

My Current Criteria in 1 of the columns-

[Forms]![frm_Reports]![chk_All_Dates]=Yes OR
Between [Forms]![frm_Reports].[txt_StartDate] And
[Forms]![frm_Reports].[txt_EndDate]

--
GH

www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

I have a form with multiple text boxes and check boxes.

The check boxes (3 of them) all are used to select ALL of the data and the
relevant text box is disabled when selected.

What i need is, When the check box is ticked (for that column in the query),
select all the data, else look at the text box entries.

Basically, when the check box is ticked, it needs to ignore the values
(whatever they are) in the text box/es, and display all the data in that
column.

A criterion of

WHERE (Forms!yourform!checkbox1 = True OR ([fieldname1] =
[Forms]![yourform]![textfield1])
AND (Forms!yourform!checkbox2 = True OR ([fieldname2] =
[Forms]![yourform]![textfield2])
AND (Forms!yourform!checkbox3 = True OR ([fieldname3] =
[Forms]![yourform]![textfield3])

or analogous should do the trick...

 

[Steve Schapel]

Guy,

If you followed the method I suggested, or use the type of SQL WHERE
clause that John suggested (although his example had some parentheses in
the wrong place, which it sounds like you have worked out by now), I see
no reason why you wouldn't get the result you are after. It shouldn't
matter that there are entries in the criteria boxes on the form in the
case of the checkboxes ticked, but if you want to make sure it is neat,
you could put code like this on the AfterUpdate event of the checkbox...
If Me.chk_All_Dates = -1 Then
Me.txt_StartDate = Null
Me.txt_EndDate = Null
End If

If you are still not getting it right, please post back with the fulll
SQL view of the query you are using.

--
Steve Schapel, Microsoft Access MVP

My Current Criteria in 1 of the columns-

[Forms]![frm_Reports]![chk_All_Dates]=Yes OR
Between [Forms]![frm_Reports].[txt_StartDate] And
[Forms]![frm_Reports].[txt_EndDate]

--
GH

www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

I have a form with multiple text boxes and check boxes.

The check boxes (3 of them) all are used to select ALL of the data and

the

relevant text box is disabled when selected.

What i need is, When the check box is ticked (for that column in the

query),

select all the data, else look at the text box entries.

Basically, when the check box is ticked, it needs to ignore the values
(whatever they are) in the text box/es, and display all the data in that
column.

A criterion of

WHERE (Forms!yourform!checkbox1 = True OR ([fieldname1] =
[Forms]![yourform]![textfield1])
AND (Forms!yourform!checkbox2 = True OR ([fieldname2] =
[Forms]![yourform]![textfield2])
AND (Forms!yourform!checkbox3 = True OR ([fieldname3] =
[Forms]![yourform]![textfield3])

or analogous should do the trick...

 

[John Vinson]

My Current Criteria in 1 of the columns-

[Forms]![frm_Reports]![chk_All_Dates]=Yes OR
Between [Forms]![frm_Reports].[txt_StartDate] And
[Forms]![frm_Reports].[txt_EndDate]

Sorry! I did have extra parentheses in there. You *do* need
parentheses, and you do need the date field in the criterion. Try a
WHERE clause of

WHERE (Forms!yourform!checkbox1 = True OR [YourDateField] BETWEEN
[Forms]![frm_Reports].[txt_StartDate] And
[Forms]![frm_Reports].[txt_EndDate])

If you have several such sets of controls for different fields, just
include a parenthsis set like this for each of them, with AND as the
logical operator between them.

 

[Guy Hocking]

Hi there,

Thanks again for that,
It looks really strange in datasheet view, criterium everywhere, but i
edited it as you said in SQL view and it appears to working fine.

Thanks for the help once again.

Regards

--
GH

www.bradflack.com

Please remove ANTI and SPAM from my
email address before sending me an email.

My Current Criteria in 1 of the columns-

[Forms]![frm_Reports]![chk_All_Dates]=Yes OR
Between [Forms]![frm_Reports].[txt_StartDate] And
[Forms]![frm_Reports].[txt_EndDate]

Sorry! I did have extra parentheses in there. You *do* need
parentheses, and you do need the date field in the criterion. Try a
WHERE clause of

WHERE (Forms!yourform!checkbox1 = True OR [YourDateField] BETWEEN
[Forms]![frm_Reports].[txt_StartDate] And
[Forms]![frm_Reports].[txt_EndDate])

If you have several such sets of controls for different fields, just
include a parenthsis set like this for each of them, with AND as the
logical operator between them.