Generating a report based on 'yes' or 'no' selection on a query

[Guest]

Hi,

I would like to produce a form that allows the user to select (from drop
down combo box) either a 'yes' or a 'no' from a particular field in a query
(in this case, 'GrantStatus' field ) I have produced. After the user has
selected 'yes' or 'no' on a form combo box, I would like the results of the
query to appear in a report. Does anyone know how I can implement this?

If you need further information, please do not hesitate to ask.

Best Wishes

Ammo

 

[Douglas J Steele]

Is the GrantStatus field a text field that will contain the words Yes or No
in it, or is it a boolean field?

If text, you can create a query that has Forms!NameOfForm!NameOfCombobox as
a criteria for the GrantStatus field, and use that field as the recordsource
for the reports.

If it's actually boolean, you'd be better off using a checkbox rather than a
combobox.

 

[Guest]

Hi Douglas,

Thank you for your suggested solution, but I still could not get what I
wanted to achieve to work. Let me provide more detail and hopefully the
problem will make more sense:

I have created a query made up of 2 tables (tbl_GrantPot &
tbl_GrantApplicant) called qry_TotalGrantPotAllocated, the query consists of
the following fields:

GrantPotNumber, GrantPotName, AvailableGrant, GrantApplicantNumber,
DateGrantAllocated, GrantRecipient, ReasonGrantAllocated,
AmountGrantAllocated, GrantSpendProgress and GrantStatus

The 'GrantStatus' is the field I would like the user to be able to select
between a 'Yes' or 'No' criteria, preferably through a form. Once the user
has selected this, I would like a report to appear (with
qry_TotalGrantPotAllocated as the datasoruce for the report) with the records.

The data type of the 'GrantStatus' field is 'Yes/No'. There is no indication
of whether the field is 'Boolean' or 'text' in table design view.

Hope you can help. Cheers.

Ammo

 

[Douglas J Steele]

Microsoft tends to confuse things by using non-standard terminology. The
so-called Yes/No field is actually a Binary field. That means you need to
match the field to -1 for True (Yes) or 0 for False (No).

As I suggested earlier, try using a checkbox rather than a combobox, or else
have two columns in your combobox so that you can display Yes or No, but
have the combobox return -1 or 0. Assuming you're using a value list for
your combobox, you'd changed the Column Count from 1 to 2, the Row Source
from "Yes";"No" to -1; "Yes";0;"No" and the column width from whatever it is
to 0". Leave the Bound Column as 1.

 

[Guest]

Cheers Doug

Microsoft tends to confuse things by using non-standard terminology. The
so-called Yes/No field is actually a Binary field. That means you need to
match the field to -1 for True (Yes) or 0 for False (No).

As I suggested earlier, try using a checkbox rather than a combobox, or else
have two columns in your combobox so that you can display Yes or No, but
have the combobox return -1 or 0. Assuming you're using a value list for
your combobox, you'd changed the Column Count from 1 to 2, the Row Source
from "Yes";"No" to -1; "Yes";0;"No" and the column width from whatever it is
to 0". Leave the Bound Column as 1.