Too many arguments for iif on form (1 Viewer)

Geronimo

New member
Local time
Today, 10:39
Joined
Dec 5, 2021
Messages
11
Hi there, newbie first post...please be gentle! I've tried to give as much information as possible...

I have a form where new customer details are entered. One of these is Postcode (UK format which is Alpha Alpha Number Number space Number Alpha Alpha). I have a list of 17 postcode areas (designated by the first two Letters of the postcode) that have to be checked against a list by users before they make a sale as some areas within the postcode areas on the list cannot be sold to. What I'm trying to do is have a warning message on the form to prompt users to check the list if they enter one of the designated postcodes. I wrote a long iif statement but its too long obviously as there are 17 arguments! sample of part of the iif statement below, having tested a shorter iif statement it does work though, so I'm happy the logic and syntax is correct:

IIf([Postcode] like "BH*", "Check no coverage areas",IIf([Postcode] like "BT*","Check no coverage areas",IIf([Postcode] like "DT*","Check no coverage areas",IIf([Postcode] like "EX*", "Check no coverage areas",IIf([Postcode]..........

............and on and on it goes! I have played around with "Eval in" but can't get that to work with the "Like" element

The list exists in a table in the DB so potentially that could be used in some way. Screen shot of the table included here -
1638715610753.png


Just wondered if anyone had any suggestions as to what might be the best/easiest way to tackle this for a relatively inexperienced bod like me.

Many thanks in advance, any help you can give will be much appreciated.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:39
Joined
Oct 29, 2018
Messages
21,358
Hi. For your current approach, you might be able to reduce the number of IIfs by combining all like codes. For example:
Code:
IIf(Left([PostCode],2) In("BH","BT","DT"), "No coverage",IIf(something else...))
(untested)
However, if you're saying that table only contains postcodes that you want to avoid, then you could potentially use DCount(). For example:
Code:
IIf(DCount("*","TableName","PostCode='" & Left([PostCode],2) & "'")>0,"No coverage","")
Hope that helps...

PS. Or, maybe use DLookup() as well. For instance:
Code:
Nz(DLookup("Comments","TableName","PostCode='" & Left([PostCode],2) & "'"),"")
 

Geronimo

New member
Local time
Today, 10:39
Joined
Dec 5, 2021
Messages
11
Thank you very much, lots of food for thought there, I'll try those suggestions and let you know how I get on.

Cheers.
 

Geronimo

New member
Local time
Today, 10:39
Joined
Dec 5, 2021
Messages
11
Thanks again, the Dlookup solution works perfectly. Brilliant and simple, and probably quite obvious to an expert, I couldn't see the wood for the trees!

Cheers again.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:39
Joined
Oct 29, 2018
Messages
21,358
Thanks again, the Dlookup solution works perfectly. Brilliant and simple, and probably quite obvious to an expert, I couldn't see the wood for the trees!

Cheers again.
Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.
 

Users who are viewing this thread

Top Bottom