Parameter Query using Y/N?

[jazminecat]

Hi, I have the following Query:

[red]PARAMETERS [Do you want to see subrecipients?] Bit;[/red]
SELECT tbl_main.wccontract, tbl_main.title, tbl_main.desc, tbl_main.origgrantawd, tbl_main.amendgrantawd, tbl_main.begdate, tbl_main.orig_enddate, tbl_main.amend_enddate, tbl_main.gcontact, tbl_gcontacts.firstname, tbl_gcontacts.lastname, qry_recip.manager, qry_recip.wcfirstname, qry_recip.wclastname, tbl_main.fvendorid, tbl_main.fcontract, tbl_main.svendorid, tbl_main.scontract, tbl_main.lvendorid, tbl_main.lcontract, tbl_main.subrecipient
FROM (tbl_main LEFT JOIN tbl_gcontacts ON tbl_main.gcontact = tbl_gcontacts.gcontact) INNER JOIN qry_recip ON tbl_main.wccontract = qry_recip.wccontract
WHERE (((tbl_main.begdate) Between #12/31/2004# And #12/31/2005#) AND ((tbl_main.subrecipient)=[Do you want to see subrecipients?]));

Here's my issue. the field tbl_main.subrecipient is a Yes/No field. The user just checks a box on a main form to populate this field.

Since the value for Y/N is actually stored as a number, they would have to enter 0 in the above query's parameter box to answer no to the question, and 1 to answer yes. obviously this isn't intuitive for most users. I phrased the question in red just to try to arrive at a phrasing that would work, but then realized it didn't matter, since they have to enter 1 or 0. *sigh*

Is there a way to make the parameter request box pop up with a different format - i.e. to say "select the type of contract you want" and then show a check box or option group wherein the user can tick either subrecipient, or contract, and then use that to run the query based on the user selection?

thanks in advance, and have a good weekend.

 

[dhookom]

Kick you development up a notch and abandon all parameter prompts in queries in favor of using controls on forms. You get to use defaults, check for integrity, use combo boxes/check boxes/list boxes, users can see several "prompts" at once, you can store the parameter values in a table,......

A parameter prompt is a parameter prompt is a parameter prompt. You just don't offer much functionality to your users.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jazminecat]

Well, that was my original approach, but I couldn't figure out how to get a form, with two option buttons, to run a query based on the option button selection, and generate the report based on that query.

All I need to do here is ask my user if they want to see the subcontracts or the contracts, and then show their report based on that. The info is stored in a yes/no field in the table, so that was why I was using a parameter query.

I'm not picky about how it gets done, but it seems like a simple thing and I can't for the life of me figure out how to do it. The sequence of events would be this:

user selects report from menu
small box pops up and asks what type of report they want
they check contracts or subcontracts
query runs based on their selection
report runs showing either contracts or subcontracts.

I got so stuck into the idea of a parameter query, that I'm not sure where to go next. Do I create a form with an option group, and then run a sql statement on the onclick event of the form based on the selection in the option group? so if contracts is checked then select x from table y where subrecipient = 1, etc? Can I use an if statement in a sql statement?

thanks for your direction - I seriously appreciate it.

 

[Golom]

I agree with Michael that you should use form controls rather than parameter queries but here's an adaptation of your parameter-based approach

PARAMETERS [Do you want to see subrecipients? (Y/N)] [COLOR=red]Text[/color];

SELECT M.wccontract, M.title, M.desc, M.origgrantawd, M.amendgrantawd, M.begdate, M.orig_enddate, M.amend_enddate, M.gcontact, C.firstname, C.lastname, R.manager, R.wcfirstname, R.wclastname, M.fvendorid, M.fcontract, M.svendorid, M.scontract, M.lvendorid, M.lcontract, M.subrecipient

FROM (tbl_main M LEFT JOIN tbl_gcontacts C ON M.gcontact = C.gcontact) 
     INNER JOIN qry_recip R ON M.wccontract = R.wccontract

WHERE M.begdate Between #12/31/2004# And #12/31/2005# 
  AND M.subrecipient=
  [COLOR=red]IIF(Left([Do you want to see subrecipients? (Y/N)],1)='Y',True,False)[/color]

The query will accept any string starting with "Y" to mean that sub-recipients are to be displayed and any other character ("N" for example) to mean that they are not displayed.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[jazminecat]

I am still stuck on which direction to go here. Can someone help point me in the right direction? I have built my pop up form with two option buttons, one for subcontract, and one for contracts. The user will select one and then click ok.

I have built the report.

I have built the queries - I built two, one with each option (contracts, and subcontracts).

So, what do I do now? I want the user to make a choice from this form, I want to then set the recordsource for the report to that query, and run the report.

I am not sure how to do that. Or if I should be changing the sql in the query in the onclick, and then just open the report - which I'm also not sure how to do.

or, do I not need queries at all?

totally stuck and spinning in circles - hope someone can help me here.