I have a form that consists of 4 conditions. The 4th condition is to chose a particular kind of value else chose all. However right now I want to add another option in this condition. If the value is not true, then have an option to chose all or chose all except that value. So eg:
Currently the condition is “Market Share” or “Blank” in the form.
I want to include another option which is “Non Market Share” so that it choses all values that are anything but Market Share.

Currently I use a VB code “getbasis() to select the value in drop down and below is the query I’m using. Please advise what change I’ll have to make in the query to get the third option.

SELECT StatusTable.Status, StatusTable.Datedue, ContractTable.Area, StatusTable.Statuskey, StatusTable.ContractIDnum, StatusTable.Custnum, [Master Customer list].Name, [Master Customer list].City, [Master Customer list].ST, ContractTable.BusinessUnit, ContractTable.RebateBasis AS basis, ContractTable.Terms, ContractTable.Paytype, ContractTable.RebateType, StatusTable.Comments, ContractTable.Group, Forms!RebatesDue!BeginningDate AS Startdate, Forms!RebatesDue!EndingDate AS enddate, ContractTable.Priority, ContractTable.System, IIf(getbasis()="Market Share","Market Share","All") AS reportbasis, RebateDueprequery.[total dollar], IIf(IsNull([Order Number]),"","Bulk Order") AS Bulk
FROM (([Master Customer list] INNER JOIN (ContractTable INNER JOIN StatusTable ON ContractTable.ContractIDnum = StatusTable.ContractIDnum) ON [Master Customer list].[Cust#] = ContractTable.Custnum) LEFT JOIN RebateDueprequery ON StatusTable.ContractIDnum = RebateDueprequery.ContractIDnum) LEFT JOIN [Bulk Sales Report] ON (ContractTable.Custnum = [Bulk Sales Report].[Customer Number]) AND (ContractTable.BusinessUnit = [Bulk Sales Report].[Business Unit])
GROUP BY StatusTable.Status, StatusTable.Datedue, ContractTable.Area, StatusTable.Statuskey, StatusTable.ContractIDnum, StatusTable.Custnum, [Master Customer list].Name, [Master Customer list].City, [Master Customer list].ST, ContractTable.BusinessUnit, ContractTable.RebateBasis, ContractTable.Terms, ContractTable.Paytype, ContractTable.RebateType, StatusTable.Comments, ContractTable.Group, Forms!RebatesDue!BeginningDate, Forms!RebatesDue!EndingDate, ContractTable.Priority, ContractTable.System, RebateDueprequery.[total dollar], IIf(IsNull([Order Number]),"","Bulk Order")
HAVING (((StatusTable.Status) Like "due" Or (StatusTable.Status)="ready") AND ((StatusTable.Datedue)<[Forms]![RebatesDue]![EndingDate]) AND ((ContractTable.Area) Like getarea()) AND ((ContractTable.RebateBasis) Is Null Or (ContractTable.RebateBasis) Like Getbasis()) AND ((ContractTable.System) Is Null)) OR (((StatusTable.Status) Like "due" Or (StatusTable.Status)="ready") AND ((StatusTable.Datedue)<[Forms]![RebatesDue]![EndingDate]) AND ((ContractTable.Area) Like getarea()) AND ((ContractTable.RebateBasis) Is Null Or (ContractTable.RebateBasis) Like Getbasis()) AND ((ContractTable.System)<>"GPO" And (ContractTable.System)<>"P3u"))
ORDER BY ContractTable.Area;

PLEASE ADVISE.

IIf(getbasis()="Market Share","Market Share",
iif(getbasis()=[WhateverQualifiesNonMarketShare],
"Non-Market Share","All")) AS reportbasis

-or-

IIf(getbasis()="Market Share","Market Share",
iif(trim(getbasis())="","All",
"Non-Market Share")) AS reportbasis

-or-

iif(trim(getbasis())="","All",getbasis()) as reportbasis

Based on your description, it looks like GetBasis() will return either "Market Share", "Non Market Share" or [spaces]. If that's the case, the third option is the simplest to code, but that double-call to GetBasis() might not be so efficient.

Hey Thanks for that reply but I have some doubts. The Getbasis() function basically outputs the value "marketshare" or "All". It doesn't output anything about the Non-MarketShare values and thats what I'm looking for.
   Another issue is that there are about 8-9 values that qualify as "Non-Marketshare" so I don't how to include a statement which asks me to include all values <> marketshare as "Non-Marketshare".
  Can you please advise me? Will I have to make a change in the Getbasis() function n then make change in the query or just the query. I'm also including the getbasis() function for ur reference as below:
Public Function Getbasis()

On Error GoTo Getbasis_Error

v = [Forms]![RebatesDue]![basis]

If IsNull(v) Or v = "" Then
    Getbasis = "*"
Else
    Getbasis = v
End If

Exit Function

Getbasis_Error:

Getbasis = "*"

End Function