UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Alternative To Nested Iifs In Access Qbe, Access 2013    
 
   
MikeWaring1
post Jun 10 2019, 11:47 AM
Post#1



Posts: 99
Joined: 7-November 12



Hi, I need to create a query that needs to return various results based on a whole heap of criteria.

I've started off using nested IIF statements (I'm up to 8 at the moment....) but it's going to get even more complicated as I need to add at least 4 more IIfs.

In addition, I need to add 2 more fields to the query to return other results, but based loosely on the same criteria, in fields called "Contract No" and "Service Code".

As can be seen from the code, the query is basically looking at fields[NewShipMethod] and [Buyer Country] and returning results based on the contents of those fields. [NewShipMethod] is in itself a calculated filed in the query using (guess what) more nested IIfs. [Buyer Country] is in a table.

I suppose I could copy and paste the criteria into those fields but its just going to make the query awfully complicated. Added to that, if any of the criteria changes, its going to be a nightmare to update the query. The nested iifs do actually work, but I suspect there'll be a much better method.

Is there an alternative, such as Switch() or other function? I've heard of the Switch function but don't have any experience with it so would need help on how to write the syntax.

I prefer to stay with QBE, but I'm open to using SQL or VBA but since I have no idea where to start with these two I'd need someone to write the code in order that I could just copy it. Of course, I would also need to then call the VBA but again, I'd need walking through this step by step.

Here's a copy of the query so far...
CODE
Carrier: IIf([NewShipMethod]="H48" And [Buyer Country]<>"United Kingdom","NON-UK",IIf([NewShipMethod]="H24" And [Buyer Country]<>"United Kingdom","NONUK2",IIf([NewShipMethod]="BOX" And [Buyer Country]<>"United Kingdom","DHL WWIDE",IIf([NewShipMethod]="H48" And [Buyer Country]="United Kingdom","Hermes",IIf([NewShipMethod]="H24" And [Buyer Country]="United Kingdom","Hermes",IIf([NewShipMethod]="LET","",IIf([NewShipMethod]="RM2","",IIf([NewShipMethod]="LL","","DHL"))))))))


Can somebody advise and take this one on?

I'm aware that I may need to supply more details about the database, which I'm happy to do. However its a split DB and very complicated so it not possible to attach a copy

Living in hope....

Kindest regards

Mike Waring

Go to the top of the page
 
theDBguy
post Jun 10 2019, 12:06 PM
Post#2


Access Wiki and Forums Moderator
Posts: 75,505
Joined: 19-June 07
From: SunnySandyEggo


Hi Mike. An even better method might be to create a separate table for the criteria and the results you want returned. You can then join this to your table and not worry about using IIf() or Switch().

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
MadPiet
post Jun 10 2019, 12:13 PM
Post#3



Posts: 3,120
Joined: 27-February 09



Mike,
can you post a database with just the one or two tables relevant to the question? Just create a blank database and import the relevant tables/objects.
I was about to suggest SWITCH(), but DBGuy is right - if you can use a JOIN between the two tables, that would be much better. If you want to cater for the chance of no matches, you can use an OUTER join.

Pieter
Go to the top of the page
 
ADezii
post Jun 10 2019, 01:41 PM
Post#4



Posts: 2,368
Joined: 4-February 07
From: USA, Florida, Delray Beach


Whenever I get into a situation where there are multiple IIfs() I also prefer to use a Calculated Field that calls a Function with a If...ElseIf...Else...End If Construct to achieve the same result.
  1. Sample Data:
    IDNewShipMethodBuyer Country
    1H48Egypt
    2H24Not United Kingdom
    3BOXNepal
    4LETNova Scotia
    5RM2Pakistan
    6LLSaudi Arabia
    7H48United Kingdom
    8H24United Kingdom
    9H99England
    10BOXUnited Kingdom
  2. Query Definition:
    SQL
    SELECT tblData.NewShipMethod, tblData.[Buyer Country], fReturnCarrier([NewShipMethod],[Buyer Country]) AS Carrier
    FROM tblData;
  3. Function Definition:
    CODE
    Public Function fReturnCarrier(strShipMethod, strCountry)
    If strShipMethod = "H48" And strCountry <> "United Kingdom" Then
      fReturnCarrier = "NON-UK"
    ElseIf strShipMethod = "H24" And strCountry <> "United Kingdom" Then
      fReturnCarrier = "NON-UK2"
    ElseIf strShipMethod = "BOX" And strCountry <> "United Kingdom" Then
      fReturnCarrier = "DHL WWIDE"
    ElseIf strShipMethod = "H24" And strCountry = "United Kingdom" Then
      fReturnCarrier = "Hermes"
    ElseIf strShipMethod = "H48" And strCountry = "United Kingdom" Then
      fReturnCarrier = "Hermes"
    ElseIf strShipMethod = "LET" Then
      fReturnCarrier = ""
    ElseIf strShipMethod = "RM2" Then
      fReturnCarrier = ""
    ElseIf strShipMethod = "LL" Then
      fReturnCarrier = ""
    Else
      fReturnCarrier = "DHL"
    End If
    End Function
  4. Query OUTPUT:
    NewShipMethodBuyer CountryCarrier
    H48EgyptNON-UK
    H24Not United KingdomNON-UK2
    BOXNepalDHL WWIDE
    LETNova Scotia
    RM2Pakistan
    LLSaudi Arabia
    H48United KingdomHermes
    H24United KingdomHermes
    H99EnglandDHL
    BOXUnited KingdomDHL
  5. Realizing that this is all so very confusing, simply download the Attachment.

P.S. - The first Query that generates the [NewShipMethod] Field would be contained within the second Query. Obviously, I have simplified the Response.
This post has been edited by ADezii: Jun 10 2019, 01:50 PM
Attached File(s)
Attached File  No_Nested_IIfs.zip ( 23.96K )Number of downloads: 2
 
Go to the top of the page
 
MikeWaring1
post Jun 11 2019, 06:20 AM
Post#5



Posts: 99
Joined: 7-November 12



Hi All, thanks for your rapid and constructive responses. MadPiet, the db is a split one with tables in the Back End (stored on a server) and all the "operative" objects on several Front End dbs on the office staff PCs. The FE's are complicated with many macros, forms, queries of all sorts so it would take me a good while to try to create a single db that would work. However, I agree with you and DbGuy that his suggestion to use a separate table is the way to go and ADezii has very kindly put me on the right track.

Adezii - I'll post another reply to you as I have a few more questions on how to incorporate and expand on your suggestions.

Once again, thanks everybody for assisting - I think I can see the light at the end of my tunnel...

Kindest regards
Mike
Go to the top of the page
 
MikeWaring1
post Jun 11 2019, 06:58 AM
Post#6



Posts: 99
Joined: 7-November 12



Hi ADezii, thanks for your detailed suggestions. As you may have gathered, I have absolutely no knowledge on how to create VBA or SQL coding, so I think I'm going to need your help some more on how to deploy your suggestions so I hope you can stick with me and give further assistance.

The table is certainly the way to go, but I'm not sure how I can join it to other tables in the query (of which there are already 4 tables doing this task and others as well), as the values in field [NewShipMethod] are calculated within the query itself and not drawn from a table.
This means that your tbl.Data doesn't have any values that correspond to values in any other table with which to join it to. Therefore I don't think the query would return any results.

Here's the nested IIF statement that does the calculations for [NewShipMethod]:
CODE
NewShipMethod:
IIf([Each Weight KG]*[Reorder Qty]<0.75,"LL",
IIf([Product Code] Like "TT*","BOX",
IIf([Each Weight KG]*[Reorder Qty]<0.75 And [Cube cm3]*[Reorder Qty]<[Max Cube],"LL",
IIf([Each Weight KG]*[Reorder Qty]<=2 And [Lgth mm]<1200,"H48",
IIf([Each Weight KG]*[Reorder Qty] Between 2.001 And 15 And [Lgth mm]<1200,"H24",
"BOX")))))
I've put each IIf on a separate line to make it easier to read.

As I've already touched on, the query does a lot more than just return values for [Carrier] - I'll copy the SQL of the query so you can see the rest. The query is actually an Append Query to populate a table from which the data is then exported to a CSV file; I've converted it to a Select query whilst I'm figuring it all out.

I also need to have it return values for 2 more similar fields [ContractNo] and [ServiceCode], using loosely the same criteria as required for[Carrier]. I'm thinking I could expand the table by creating two more fields and then entering all the possible combinations of all the fields.

SQL Of the query as it stands with the original [Carrier] field:
CODE
SELECT [Products Main Import Table].[Primary Location], [Import data From Ebay - MIDS TOOLS].[Buyer Full name], [Import data From Ebay - MIDS TOOLS].[Buyer Address 1], [Import data From Ebay - MIDS TOOLS].[Buyer Address 2], [Import data From Ebay - MIDS TOOLS].[Buyer Town/City], [Import data From Ebay - MIDS TOOLS].[Buyer Postcode], IIf([Buyer Phone Number] Is Not Null,[Code] & [Buyer Phone Number],"") AS [Cust Tel], [Products Main Import Table].[UK Ship Method], IIf([Each Weight KG]*[Reorder Qty]<0.75,"LL",IIf([Product Code] Like "TT*","BOX",IIf([Each Weight KG]*[Reorder Qty]<0.75 And [Cube cm3]*[Reorder Qty]<[Max Cube],"LL",IIf([Each Weight KG]*[Reorder Qty]<=2 And [Lgth mm]<1200,"H48",IIf([Each Weight KG]*[Reorder Qty] Between 2.001 And 15 And [Lgth mm]<1200,"H24","BOX"))))) AS NewShipMethod, [Import data From Ebay - MIDS TOOLS].[Sales Record Number], 2 AS [GP Order], IIf([NewShipMethod]="LET","C&D",IIf([NewShipMethod]="LL","C&D","PCL")) AS [Invoice Gp], IIf([NewShipMethod]="H48" And [Buyer Country]<>"United Kingdom","NON-UK",IIf([NewShipMethod]="H24" And [Buyer Country]<>"United Kingdom","NONUK2",IIf([NewShipMethod]="BOX" And [Buyer Country]<>"United Kingdom","DHL WWIDE",IIf([NewShipMethod]="H48" And [Buyer Country]="United Kingdom","Hermes",IIf([NewShipMethod]="H24" And [Buyer Country]="United Kingdom","Hermes",IIf([NewShipMethod]="LET","",IIf([NewShipMethod]="LET","",IIf([NewShipMethod]="LL","","DHL")))))))) AS Carrier
FROM (([Import data From Ebay - MIDS TOOLS] INNER JOIN [Products Main Import Table] ON [Import data From Ebay - MIDS TOOLS].[Custom Label] = [Products Main Import Table].[Product Code]) INNER JOIN [tbl_International Dialling Codes] ON [Import data From Ebay - MIDS TOOLS].[Buyer Country] = [tbl_International Dialling Codes].Country) INNER JOIN [Shipping Rates] ON [Products Main Import Table].[UK Ship Method] = [Shipping Rates].ShipMethod
WHERE ((([Import data From Ebay - MIDS TOOLS].[Buyer Full name]) In (SELECT [Buyer Full name] FROM [Import data From Ebay - MIDS TOOLS] As Tmp GROUP BY [Buyer Full name] HAVING Count(*)=1 )) AND (([Products Main Import Table].[UK Ship Method]) In ("LET","LL","RM2","SF2","BAG","BOX")) AND (([Import data From Ebay - MIDS TOOLS].[Custom Label]) Not Like "MUK*") AND (([Import data From Ebay - MIDS TOOLS].[Postage and Packaging])<>7.77) AND (([Import data From Ebay - MIDS TOOLS].Quantity)=1))
ORDER BY [Products Main Import Table].[Primary Location], [Import data From Ebay - MIDS TOOLS].[Custom Label], [Import data From Ebay - MIDS TOOLS].[Sales Record Number];


Sorry, I can't separate the various lines as I don't have the first clue on what it all means - it has all been created in QBE.

I think I've posted enough here for a start, so I won't complicate this post with other questions I have - I'll need to take it bit by bit otherwise I'll fry my brain....

Hoping you can still help...
Kindest regards
Mike
Go to the top of the page
 
ADezii
post Jun 11 2019, 09:07 AM
Post#7



Posts: 2,368
Joined: 4-February 07
From: USA, Florida, Delray Beach


Mike:
At this point, I am thinking that the best way that we can help to resolve your problem is if you can Upload an Empty Copy of your Database. By empty, I mean only Tables with no Data in them but with Relationships among them pre-defined. No other DB Objects are necessary. Don't take my word only, wait and see what the rest of the gang has to say on this idea.
Go to the top of the page
 
MikeWaring1
post Jun 11 2019, 09:17 AM
Post#8



Posts: 99
Joined: 7-November 12



Hi Adezii, thanks for your reply.

I'll see if I can create a sample db by just copying the tables that are part of this query, then the query itself.

In theory it should work OK, but yes I'm all for taking help from any other experts who are happy to contribute.
Kind regards
Mike
Go to the top of the page
 
MikeWaring1
post Jun 12 2019, 09:52 AM
Post#9



Posts: 99
Joined: 7-November 12



Hi Adezii, I think I have a workaround for the table join problem - I'm going to append the query to a new temp table which will then have the values for the field [NewShipMethod] contained within; I can then create an extra query where I can then create the join and thus call the Module from that query

I've copied your VBA into my db and then created 2 more modules for the values for "Contract no" and "Service Code", which I can also call from the query.

I'll let you know how I get on,

BUT... I'm now getting an error that I've traced to one field in the query. The error is "Type Data Mismatch in Criteria".

This is referring to a filed that is a calculated field
CODE
ShipMethod: IIf([Lgth mm]>1400,"HVY",(UKShipMethod([Parcel Wt])))
.

The module it is calling is

CODE
Option Compare Database
Option Explicit

Public Function UKShipMethod(ByVal dblParcelWt As Double) As String

Select Case dblParcelWt
    Case 0.001 To 0.748
        UKShipMethod = "LL"
    Case 0.75 To 2
        UKShipMethod = "RM2"
    Case 2.001 To 5
        UKShipMethod = "BAG"
    Case 5.001 To 30
        UKShipMethod = "BOX"
    Case Else
        UKShipMethod = "HVY"
    End Select

End Function


I've checked to see if the field [Parcel Wt] is correct and it is definitely a Number Field, type Double, 3 decimal places, which is the same as declared in the module, but there's obviously an error somewhere, as when I test changed the statement to
CODE
ShipMethod: IIf([Lgth mm]>1400,"HVY","OK")
, it didn't throw up the error.

Can you see anything obvious in the VBa that might be causing this error?

Kind regards
Mike
Go to the top of the page
 
ADezii
post Jun 12 2019, 12:00 PM
Post#10



Posts: 2,368
Joined: 4-February 07
From: USA, Florida, Delray Beach


Is [Lgth mm] defined as Text?
Go to the top of the page
 
MikeWaring1
post Jun 13 2019, 04:30 AM
Post#11



Posts: 99
Joined: 7-November 12



No, Its a number - single, auto decimal places
Go to the top of the page
 
ADezii
post Jun 13 2019, 07:55 AM
Post#12



Posts: 2,368
Joined: 4-February 07
From: USA, Florida, Delray Beach


Mike:
I think we are back to square one as previously indicated in Post# 7 (quoting myself):
QUOTE
At this point, I am thinking that the best way that we can help to resolve your problem is if you can Upload an Empty Copy of your Database. By empty, I mean only Tables with no Data in them but with Relationships among them pre-defined. No other DB Objects are necessary.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th June 2019 - 05:40 AM