Nested IIf statements

[Chyld]

I have a table like so called 'repinfo' (slightly different but relational.)

postcode Rep1 Rep2 Rep3 Rep4
======== ==== ==== ==== ====
AB10 Craig Jack Paul Jemma
HX1 Phil Jezza Gavin Jake
ZS3 Laa Laa Po Tinky W Dipsy

I have an unbound form [Mainform] with a combobox in it [rep_type], with the following types being available:

Builders Merchant
Contractor
Domestic End User
Specifier / Project Consultant

and an unbound text box called [postcode]

I have a subform [mainform_sub] based on a query called 'repinfoextract' that is based on the table [repinfo] above, but has a an overly large nested query in it..

i.e

Representative: IIf(left([forms]![mainform]![rep_type],3)="Bui", [Rep1], IIf(left([forms]![mainform]![rep_type],3)="Con", [Rep2], IIf(left([forms]![mainform]![rep_type],3)="Dom", [Rep3], [Rep4])))

Postcode
criteria = like[forms]![mainform]![postcode]

so in the afterupdate event on the textbox [postcode] that appears on [mainform], it requeries the [mainform]![mainform_sub] and displays who the appropriate rep is based on the customer type choice..

Yes this all works, but I really would like to reduce the amount of iif statements that appear in my queries...

Any takers?

Chyld

It wasn't the fall that was embarrassing, it was realising that I hadn't tied my laces in the first place...

 

[MichaelRed]

Copy the below into a public module.

Public Function basMyRep(RepType As String, Rep1 as String, Rep2, as String, Rep3 as String, Rep4 as String) As String

Select Case RepType
Case Is = "Bui"
basMyRep = Rep1

Case Is = "Con"
basMyRep = Rep2

Case Is = "Dom"
basMyRep = Rep3

Case Else
basMyRep = Rep4

End Select

End Function


Replace the 'overly nessted' iif's with:

Representative: basMyRep(left([forms]![mainform]![rep_type],3), [Rep1], [Rep2], [Rep3], [Rep4])
MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Chyld]

Thank you for that, I will give it a go, is there any way of doing this without coding in VBA though?

 

[MichaelRed]

Yes, but -to me- they generally have the same/similar difficulty as the nested IIF. It is more difficult to construct and 'see' the various parts / syntax issues. Further, some of the processes are unique to MS Access while generally the procedures translate fairly easily to stored procedures for the 'grown up' dbs (SQL Server, Oracke. ... )

Look at the 'switch' function in Ms. Access, it is as lose to "Select Case" as you can embed directly into a query.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[Chyld]

You think the switch function then yeah?! Then that is what I will try....

I will probably thread some other post about something else shortly... hahaha

Chyld

"If there is ever a time to fall, it is most likely when you are winning."

Me, 10/8/01..

 

[Chyld]

I think the switch function is cack...

I will try your module and see what happens.

 

[Guest_imported]

switch function is kind of evil...

try this to emulate the Oracle Decode function:

public function decode(v as variant, optional paramarray argv() as variant) as variant
dim result as variant

'Oracle decode function:
'decode(arg, [v1, r1, [v2, r2, [...vn, rn]] [,relse]])
'return r1 if arg = v1, etc., otherwise return relse
'if it exists, or null if it doesn't.

dim argc as integer, i as integer

argc = ubound(argv) + 1 'argv will always be 0-based.
select case argc
case 0:
result = null
case 1:
result = argv(0)
case else:

for i = 0 to (argc - 1) step 2
if i < ubound(argv) then
if v = argv(i+1) then
result = argv(i+1)
exit for
end if
else
if v = argv(i) then
result = argv(i)
end if
end if
next i
end select
decode = result
end function