Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 5th, 2007, 04:41 PM
Authorized User
 
Join Date: Mar 2007
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default IIf Function - Multiple conditions

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.
 
Old June 8th, 2007, 12:29 PM
Registered User
 
Join Date: May 2007
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

 
Old June 13th, 2007, 10:27 AM
Authorized User
 
Join Date: Mar 2007
Posts: 15
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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


Quote:
quote:Originally posted by GhostWolf
 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.






Similar Threads
Thread Thread Starter Forum Replies Last Post
using multiple conditions in sql loader elygp Oracle 0 October 9th, 2008 10:21 PM
SQL LIKE Multiple Conditions takwirira ASP.NET 2.0 Basics 1 April 16th, 2008 06:24 AM
Multiple conditions austinf XSLT 1 May 5th, 2006 02:55 AM
Multiple conditions austinf XSLT 1 May 5th, 2006 02:38 AM
Multiple db fields each with different conditions? sfinc Crystal Reports 1 February 27th, 2004 09:46 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.