limit on iif statement

[cneill]

Hi currenly use the following IIF Statement in my query

Delivery Ref No: IIf([tblElectricalDeliveryCompany]![DeliveryCompanyID]=1,[CompanyPrefix] & "" & [CFLGTINo],IIf([tblElectricalDeliveryCompany]![DeliveryCompanyID]=3,[CompanyPrefix] & "" & [CFLPrologNo],IIf([tblElectricalDeliveryCompany]![DeliveryCompanyID]=4,[CompanyPrefix] & "" & [PDPrologNo],IIf([tblElectricalDeliveryCompany]![DeliveryCompanyID]=2,[CompanyPrefix] & "" & [PDGTINo],IIf([tblElectricalDeliveryCompany]![DeliveryCompanyID]=8,[CompanyPrefix] & "" & [TVPDPrologNo],IIf([tblElectricalDeliveryCompany]![DeliveryCompanyID]=7,[CompanyPrefix] & "" & [TVPDGTINo],IIf([tblElectricalDeliveryCompany]![DeliveryCompanyID]=9,[CompanyPrefix] & "" & [ICFLGTINo],IIf([tblElectricalDeliveryCompany]![DeliveryCompanyID]=10,[CompanyPrefix] & "" & [ICFLPrologNo],IIf([tblElectricalDeliveryCompany]![DeliveryCompanyID]=13,[CompanyPrefix] & "" & [IPDGTINo],IIf([tblElectricalDeliveryCompany]![DeliveryCompanyID]=14,[CompanyPrefix] & "" & [IPDPrologNo],""))))))))))

So that I can combine the CompanyPrefix with various ref numbers, each of the fields i.e. IPDPrologNo is a numertic number.

I need to add some more fields but I have reached my limit on the IIF Statement.
how do I change this into a Case Statement or something else, so that I can increase the number of fields.
I would like to call it from the query so that I use the field.
I hope I have explained myself correctly, can anyone help?
Thanks
cneill

 

[RoyVidar]

Had the numbers been consecutive (1, 2, 3,...), you could have used the Choose() function. Since it seems there are gaps in your numbers, then the Switch() function should be applicable.

You'll find more information, syntax and samples in the help file.

Roy-Vidar

 

[cneill]

Hi,

I have tried to construct a Function as follows

Public Function DeliveryRefNo()

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblElectricalDeliveryCompany", , dbPessimistic) '([tblElectricalDeliveryCompany], dbOpenDynaset)

Dim db1 As Database
Dim rs1 As Recordset
Set db1 = CurrentDb
Set rs1 = db1.OpenRecordset("tblAccountSchemesActivity", , dbPessimistic)

Dim Value As String, DeliveryCompanyID As String, CompanyPrefix As String, CFLGTINo As String, ICFLGTINo As String, CFLPrologNo As String, ICFLPrologNo As String, PDGTINo As String, IPDGTINo As String, PDPrologNo As String, IPDPrologNo As String, TVPDGTINo As String, ITVPDGTINo As String, TVPDPrologNo As String, ITVPDPrologNo As String
DeliveryCompanyID = rs![DeliveryCompanyID]
CompanyPrefix = rs![CompanyPrefix]
CFLGTINo = rs1![CFLGTINo]
ICFLGTINo = rs1![ICFLGTINo]
CFLPrologNo = rs1![CFLPrologNo]
ICFLPrologNo = rs1![ICFLPrologNo]
PDGTINo = rs1![PDGTINo]
IPDGTINo = rs1![IPDGTINo]
PDPrologNo = rs1![PDPrologNo]
IPDPrologNo = rs1![IPDPrologNo]
TVPDGTINo = rs1![TVPDGTINo]
ITVPDGTINo = rs1![ITVPDGTINo]
TVPDPrologNo = rs1![TVPDPrologNo]
ITVPDPrologNo = rs1![ITVPDPrologNo]

Value = Switch(DeliveryCompanyID = 1, [CompanyPrefix] & "" & [CFLGTINo], DeliveryCompanyID = 3, [CompanyPrefix] & "" & [CFLPrologNo], DeliveryCompanyID = 4, [CompanyPrefix] & "" & [PDPrologNo], DeliveryCompanyID = 2, [CompanyPrefix] & "" & [PDGTINo], DeliveryCompanyID = 8, [CompanyPrefix] & "" & [TVPDPrologNo], DeliveryCompanyID = 7, [CompanyPrefix] & "" & [TVPDGTINo], DeliveryCompanyID = 9, [CompanyPrefix] & "" & [ICFLGTINo], DeliveryCompanyID = 10, [CompanyPrefix] & "" & [ICFLPrologNo], DeliveryCompanyID = 13, [CompanyPrefix] & "" & [IPDGTINo], DeliveryCompanyID = 14, [CompanyPrefix] & "" & [IPDPrologNo])

End Function

I am calling the Function from the Query but getting a Null Value message
Have I constructed the Function correctly? can anyone help?

Thanks
cneill

 

[RoyVidar]

Use the Switch function directly in the query

[tt]Delivery Ref No: Switch([DeliveryCompanyID]=1,[CompanyPrefix] & "" & [CFLGTINo],[DeliveryCompanyID]=3,[CompanyPrefix] & "" & [CFLPrologNo],...[/tt]

Roy-Vidar

 

[cneill]

Hi Roy,

Yes I did that and it worked, but again there is a limit as I need to add addtional items.

Any thoughts?

Thanks

cneill

 

[RoyVidar]

I don't think the function in it self has any limits.

If you wish to use a UDF (user defined function), you need to pass a value to it, and have it return a value - see below "sketch"

[tt]Public Function DeliveryRefNo(TheNumber as Long) As String

dim s as string
...

Select Case TheNumber
Case 1
s = rs1![CFLGTINo]
Case 2
s = rs1![PDGTINo]
Case 3
s = rs1![CFLPrologNo]
...
End Select
DeliveryRefNo = s

End Function[/tt]



Then the call in the query might look like this

[tt]Delivery Ref No: CompanyPrefix & DeliveryRefNo([DeliveryCompanyID])[/tt]

Roy-Vidar

 

[MajP]

Using a function for this is a bad idea. Using nested iif is a horrible idea. I would do it in Sql. Make a simple query for each of your ID types and a concatenated field for the refno.

example
qryID1
Select deliverCompanyID, [CompanyPrefix] & "" & [CFLGTINo] as refNow from tblElectricalDeliveryCompany where DeliveryCompanyID=1

All other queries are exactly the same with a different concatenated field and where value.

Now do this for each ID type.
Then do a union of
Select * from qryID1 Union Select * from qryID2... Select * from qryID14

Now you can keep adding.

If it was me once I built the final union query I would make a table to store the "refNo" in the table.

 

[MajP]

Also if it was not obvious once you create the final query/table then just do a simple inner join. You can add to this very easily with more queries.

 

[missinglinq]

Using nested iif is a horrible idea.

Amen to that, MajP! I've always thought IIFs were something that Rube Goldberg would have dreamed up if he'd lived into the Computer Age!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[cneill]

Hi MajP,

Followed your advise, now works a treat.

Many Thanks

cneill

 

[MajP]

Here is an interesting discussion on iif, choose, and switch but deals mainly with issues of using these in code. It is towards the bottom.

http://msdn.microsoft.com/en-us/library/aa188211(office.10).aspx

This does not even address the performance hits of nested iif. But the biggest issue as the Missingling points out, they are difficult to read, difficult to edit, difficult to trouble shoot. You can write a user defined function using a select statement that would be faster, clearer, and very easy to edit. Then you can call that function from anywhere.

 

[RoyVidar]

Having a look at it agains, seems to me the underlying problem is data structure. Doesn't the tblElectricalDeliveryCompany table contain repeating information (extra columns per each "DeliveryCompanyID")? Perhaps the whole enchilada could be achieved with a restructuring of that table and a join in stead of functions, umpteen queries or storing calculated values?

Roy-Vidar