Unbound FORM with radio or toggle button to pass a parameter to qu

[AFSSkier]

I have a select query that uses the following WHERE parameter. I want to set
up an unbound FORM with a radio or toggle button that the user can check to
replace a parameter prompt. The FORM needs to pass a "E" for the EDLP group
only and a default "*" for all items.

WHERE ([qryITEMS].[EDLP]) = [Enter E for EDLP items only and * for All
items])

 

[Steve Schapel]

Kevin,

WHERE [Forms]![YourForm]![YourRadioButton]=0 OR [qryITEMS].[EDLP]="E"

 

[AFSSkier]

Steve,

Thanks for your quick response. However, your suggested WHERE only returns
the value of the “E†group, regardless if the RadioButton is checked or not.

I need something like this:
IIf([Forms]![MyForm]![MyRadioButton] = 1,"E",([qryITEMS].[EDLP]) Is Null Or
([qryITEMS].[EDLP]) = "E")

In my Select Query, without the form (returns ALL records)
WHERE (([qryITEMS].[EDLP]) Is Null Or ([qryITEMS].[EDLP]) ="E"))

Without the form (returns only EDLP “E†records)
WHERE ([qryITEMS].[EDLP]) ="E")

The field is Null or a value of E to flag the EDLP items. “*†as mentioned
before does not work.

I’m trying to provide the end-user with an option to get a Excel report of
ALL items or check the RadioButton for only the EDLP “E†items.

Currently, I have to provide the user with all items. After Excel export,
if they want only EDLP items, they have to delete all the ones that aren't
flagged with the "E".

--
Thanks, Kevin

Kevin,

WHERE [Forms]![YourForm]![YourRadioButton]=0 OR [qryITEMS].[EDLP]="E"

--
Steve Schapel, Microsoft Access MVP

I have a select query that uses the following WHERE parameter. I want to set
up an unbound FORM with a radio or toggle button that the user can check to
replace a parameter prompt. The FORM needs to pass a "E" for the EDLP group
only and a default "*" for all items.

WHERE ([qryITEMS].[EDLP]) = [Enter E for EDLP items only and * for All
items])

 

[Douglas J. Steele]

Steve's suggestion should work.

What's the actual SQL for your query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Steve,

Thanks for your quick response. However, your suggested WHERE only
returns
the value of the "E" group, regardless if the RadioButton is checked or
not.

I need something like this:
IIf([Forms]![MyForm]![MyRadioButton] = 1,"E",([qryITEMS].[EDLP]) Is Null
Or
([qryITEMS].[EDLP]) = "E")

In my Select Query, without the form (returns ALL records)
WHERE (([qryITEMS].[EDLP]) Is Null Or ([qryITEMS].[EDLP]) ="E"))

Without the form (returns only EDLP "E" records)
WHERE ([qryITEMS].[EDLP]) ="E")

The field is Null or a value of E to flag the EDLP items. "*" as mentioned
before does not work.

I'm trying to provide the end-user with an option to get a Excel report of
ALL items or check the RadioButton for only the EDLP "E" items.

Currently, I have to provide the user with all items. After Excel export,
if they want only EDLP items, they have to delete all the ones that aren't
flagged with the "E".

--
Thanks, Kevin

Kevin,

WHERE [Forms]![YourForm]![YourRadioButton]=0 OR [qryITEMS].[EDLP]="E"

--
Steve Schapel, Microsoft Access MVP

I have a select query that uses the following WHERE parameter. I want
to set
up an unbound FORM with a radio or toggle button that the user can
check to
replace a parameter prompt. The FORM needs to pass a "E" for the EDLP
group
only and a default "*" for all items.

WHERE ([qryITEMS].[EDLP]) = [Enter E for EDLP items only and * for All
items])

 

[AFSSkier]

Doug/Steve,

Here's my SQL with Steve's suggested Where:
SELECT DIV.ITEMNO, DIV.PACK AS PK, DIV.SIZE, DIV.DESC, DIV.RETLUPC,
DIV.COST, DIV.SELL, DIV.RETLAMT, qryITEMS.EDLP, DIV.VENDOR, DIV.DISCCD

FROM [DIV 1] LEFT JOIN qryITEMS ON DIV.ITEMNO = qryITEMS.ITEMNO

WHERE (((DIV.VENDOR) Like [Forms]![PRICING FORM]![Vendor]) AND ((DIV.CM)
Like [Forms]![PRICING FORM]![CM]) AND (([Forms]![PRICING FORM]![EDLPOnly]=0
Or [qryITEMS]![EDLP]=“Eâ€)<>False));

Access is adding <>False to the where, could this be the problem?

--
Thanks, Kevin

Steve's suggestion should work.

What's the actual SQL for your query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Steve,

Thanks for your quick response. However, your suggested WHERE only
returns
the value of the "E" group, regardless if the RadioButton is checked or
not.

I need something like this:
IIf([Forms]![MyForm]![MyRadioButton] = 1,"E",([qryITEMS].[EDLP]) Is Null
Or
([qryITEMS].[EDLP]) = "E")

In my Select Query, without the form (returns ALL records)
WHERE (([qryITEMS].[EDLP]) Is Null Or ([qryITEMS].[EDLP]) ="E"))

Without the form (returns only EDLP "E" records)
WHERE ([qryITEMS].[EDLP]) ="E")

The field is Null or a value of E to flag the EDLP items. "*" as mentioned
before does not work.

I'm trying to provide the end-user with an option to get a Excel report of
ALL items or check the RadioButton for only the EDLP "E" items.

Currently, I have to provide the user with all items. After Excel export,
if they want only EDLP items, they have to delete all the ones that aren't
flagged with the "E".

--
Thanks, Kevin

Kevin,

WHERE [Forms]![YourForm]![YourRadioButton]=0 OR [qryITEMS].[EDLP]="E"

--
Steve Schapel, Microsoft Access MVP

AFSSkier wrote:
I have a select query that uses the following WHERE parameter. I want
to set
up an unbound FORM with a radio or toggle button that the user can
check to
replace a parameter prompt. The FORM needs to pass a "E" for the EDLP
group
only and a default "*" for all items.

WHERE ([qryITEMS].[EDLP]) = [Enter E for EDLP items only and * for All
items])

 

[Douglas J. Steele]

How are you entering the criteria that Access is adding the <> False?

The following Where clause should work:

WHERE ((DIV.VENDOR Like [Forms]![PRICING FORM]![Vendor]) AND (DIV.CM
Like [Forms]![PRICING FORM]![CM]) AND ([Forms]![PRICING FORM]![EDLPOnly]=0
Or [qryITEMS]![EDLP]="E"))

Are you putting wild cards into the Vendor and CM text boxes? If not,
replace the Like with =.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug/Steve,

Here's my SQL with Steve's suggested Where:
SELECT DIV.ITEMNO, DIV.PACK AS PK, DIV.SIZE, DIV.DESC, DIV.RETLUPC,
DIV.COST, DIV.SELL, DIV.RETLAMT, qryITEMS.EDLP, DIV.VENDOR, DIV.DISCCD

FROM [DIV 1] LEFT JOIN qryITEMS ON DIV.ITEMNO = qryITEMS.ITEMNO

WHERE (((DIV.VENDOR) Like [Forms]![PRICING FORM]![Vendor]) AND ((DIV.CM)
Like [Forms]![PRICING FORM]![CM]) AND (([Forms]![PRICING
FORM]![EDLPOnly]=0
Or [qryITEMS]![EDLP]="E")<>False));

Access is adding <>False to the where, could this be the problem?

--
Thanks, Kevin

Steve's suggestion should work.

What's the actual SQL for your query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Steve,

Thanks for your quick response. However, your suggested WHERE only
returns
the value of the "E" group, regardless if the RadioButton is checked or
not.

I need something like this:
IIf([Forms]![MyForm]![MyRadioButton] = 1,"E",([qryITEMS].[EDLP]) Is
Null
Or
([qryITEMS].[EDLP]) = "E")

In my Select Query, without the form (returns ALL records)
WHERE (([qryITEMS].[EDLP]) Is Null Or ([qryITEMS].[EDLP]) ="E"))

Without the form (returns only EDLP "E" records)
WHERE ([qryITEMS].[EDLP]) ="E")

The field is Null or a value of E to flag the EDLP items. "*" as
mentioned
before does not work.

I'm trying to provide the end-user with an option to get a Excel report
of
ALL items or check the RadioButton for only the EDLP "E" items.

Currently, I have to provide the user with all items. After Excel
export,
if they want only EDLP items, they have to delete all the ones that
aren't
flagged with the "E".

--
Thanks, Kevin


:

Kevin,

WHERE [Forms]![YourForm]![YourRadioButton]=0 OR [qryITEMS].[EDLP]="E"

--
Steve Schapel, Microsoft Access MVP

AFSSkier wrote:
I have a select query that uses the following WHERE parameter. I
want
to set
up an unbound FORM with a radio or toggle button that the user can
check to
replace a parameter prompt. The FORM needs to pass a "E" for the
EDLP
group
only and a default "*" for all items.

WHERE ([qryITEMS].[EDLP]) = [Enter E for EDLP items only and * for
All
items])

 

[AFSSkier]

Steve,
Thank you, you’re correct with your suggestion of:
WHERE [Forms]![YourForm]![YourRadioButton]=0 OR [qryITEMS].[EDLP]="E"
This does work, I had something else that was causing my error. It also
helped to set the Option Button Default Value as 0 (zero, same as the query).

Doug: Thank you, too. I’m still not sure how I was getting the “<> Falseâ€.
It may have been when I entered it through Expression Builder.

--
Thanks, Kevin

Kevin,

WHERE [Forms]![YourForm]![YourRadioButton]=0 OR [qryITEMS].[EDLP]="E"

--
Steve Schapel, Microsoft Access MVP

I have a select query that uses the following WHERE parameter. I want to set
up an unbound FORM with a radio or toggle button that the user can check to
replace a parameter prompt. The FORM needs to pass a "E" for the EDLP group
only and a default "*" for all items.

WHERE ([qryITEMS].[EDLP]) = [Enter E for EDLP items only and * for All
items])

 

[Steve Schapel]

Thanks for letting us know, Kevin, and glad to know it's sorted now.