473,748 Members | 10,539 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

IIf statement not working, SELECT case instead?

18 New Member
I have a very long IIf statement. I think maybe I've reached the limit of how many choices you can have in the control source "Build" statement of a text box on a form. Really, the IIF statement is very confusing looking at it, so I suppose there is a better way, but I don't know how. When I try to add additional choices, nothing happens, I save, get no errors, but the new choices don't work! I am trying to calculate a due date [DATEREVIEWDUE]text field on a subform based off of the value chosen in another subform on the same main form using a field called [TASKTYPE]. Whatever value is chosen in TASKTYPE (dropdown box) it is compared with the value in the [DATEREVIEWASSIG NED] text box which calculates the DATEREVIEWDUE. It works for many instances, but when I tried to add additional options beyond 14 choices, access doesn't save the changes. It will only allow me to change the existing entries. I've attached the sample code of my current IIf statement that works. Thanks in advance.

** Edit ** Added text and removed attachment (as the attachment requires downloading which makes it a little hard to see normally).

Expand|Select|Wrap|Line Numbers
  1. =IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Alternatives Analysis",[DATEREVIEWASSIGNED]+60,
  2.  IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Explore",[DATEREVIEWASSIGNED]+7,
  3.  IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="GP-12",[DATEREVIEWASSIGNED]+30,
  4.  IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Informal",[DATEREVIEWASSIGNED]+45,
  5.  IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Major 1",[DATEREVIEWASSIGNED]+60,
  6.  IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="New 1",[DATEREVIEWASSIGNED]+60,
  7.  IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Noise Complaint",[DATEREVIEWASSIGNED]+30,
  8.  IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Other",[DATEREVIEWASSIGNED]+30,
  9.  IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Renewal",[DATEREVIEWASSIGNED]+60,
  10.  IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Revision",[DATEREVIEWASSIGNED]+90,
  11.  IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Special Project",[DATEREVIEWASSIGNED]+60,
  12.  IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Stream Investigation",[DATEREVIEWASSIGNED]+60,
  13.  IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Transfer",[DATEREVIEWASSIGNED]+60,
  14.  IIf(Forms![Input Facility Task Reviewer]!Task_Subform.Form!TASKTYPE="Water Loss",[DATEREVIEWASSIGNED]+45))))))))))))))
Oct 29 '09 #1
40 6239
MikeTheBike
639 Recognized Expert Contributor
@chhines
Hi

I think I would tackle this with the Choose() function, (see VB Help).

You do not say what the Row Source of the Drop List box is, but if it is a table then I would add a field name, say 'Choose' to relate it the its use (?), as in integer type. Assign a unique number to each of the 'Choices', these should be continues numbers 1 to maxchoice. I don't know if you can make this field the bound column of the ComboBox, but if not, by using the Column property you can put the selected 'Chosen' value in a hidden form control say txtChooseIndex using the AfterUpdate event.

Then you would have a calculated like this
Expand|Select|Wrap|Line Numbers
  1. =Choose(Forms![Input Facility Task Reviewer]!Task_Subform.Form!txtChoosIndex,[DATEREVIEWASSIGNED]+60,[DATEREVIEWASSIGNED]+7,[DATEREVIEWASSIGNED]+30, ............. etc)
  2.  
The order of the Choice arguments in the Choose() function would have to be in line with the 'Choose' values in the table or whatever.

This will be significantly shorter than the nested IIF() functions. Not sure if there is a limit to the number of ‘choices’ thought!

Hope that makes some sort of sense.

I am sure there are many ways to do this; perhaps someone else has other suggestions.


MTB
Oct 29 '09 #2
ADezii
8,834 Recognized Expert Expert
@chhines
  1. Set the Control Source of the [DATEREVIEWDUE] Field equal to a Public Function within which the logic is contained.
    Expand|Select|Wrap|Line Numbers
    1. =fCalcDateReviewDue()          'Control Source
  2. Function Definition.
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcDateReviewDue() As Date
    2. Dim strTaskType As String
    3. Dim dteDateReviewWasAssigned As Date
    4.  
    5. strTaskType = Forms![Input Facility Task Reviewer]!Task_Subform.Form![TASKTYPE]
    6. dteDateReviewWasAssigned = Forms![Input Facility Task Reviewer]!Task_Subform.Form![DATEREVIEWASASSIGNED]
    7.  
    8. Select Case strTaskType
    9.   Case "Alternatives Analysis", "Major 1", "New 1", "Renewal", "Special Project", _
    10.        "Stream Investigation", "Transfer"
    11.     fCalcDateReviewDue = dteDateReviewWasAssigned + 60
    12.   Case "Explore"
    13.     fCalcDateReviewDue = dteDateReviewWasAssigned + 7
    14.   Case "GP-12", "Noise Complaint", "Other"
    15.     fCalcDateReviewDue = dteDateReviewWasAssigned + 30
    16.   Case "Informal", "Water Loss"
    17.     fCalcDateReviewDue = dteDateReviewWasAssigned + 45
    18.   Case "Revision"
    19.     fCalcDateReviewDue = dteDateReviewWasAssigned + 90
    20.   Case Else
    21.     fCalcDateReviewDue = #1/1/1800#
    22. End Select
    23. End Function
  3. NOTE: The major assumption is made that the 2 Fields involved in the calculations are Required, and therefore cannot be NULL. If either is NULL, all bets are OFF. It is also assumed that both Fields reside on the same Sub-Form, if not make the necessary adjustment within the Function.
Oct 29 '09 #3
NeoPa
32,571 Recognized Expert Moderator MVP
CHHines,

What you've managed to fit in there can be more succinctly be written as :
Expand|Select|Wrap|Line Numbers
  1. =[DATEREVIEWASSIGNED]+Switch(
  2.  Forms![Input Facility Task Reviewer]!Task_Subform!TASKTYPE='Explore',7,
  3.  Forms![Input Facility Task Reviewer]!Task_Subform!TASKTYPE In('GP-12','Noise Complaint','Other'),30,
  4.  Forms![Input Facility Task Reviewer]!Task_Subform!TASKTYPE In('Informal','Water Loss'),45,
  5.  Forms![Input Facility Task Reviewer]!Task_Subform!TASKTYPE In('Renewal','Major 1','New 1','Alternatives Analysis','Special Project','Stream Investigation','Transfer'),60,
  6.  Forms![Input Facility Task Reviewer]!Task_Subform!TASKTYPE='Revision',90)
I believe the restriction is on length rather than number of items, so I suggest you have a rethink on your object names. They are far too clumsy for use in programming. Generally including spaces anywhere in an object name is unwise. If the name is not really a name but a sentence, you know you're likely to run into problems like this one.
Oct 31 '09 #4
ADezii
8,834 Recognized Expert Expert
@NeoPa
Hello NeoPa. Just for curiosity, do you think that the Switch() approach is more efficient, readable, and faster than the Function/Select Case approach, especially given the tendency for Switch() to evaluate all expressions whether or not the preceding ones evaluate to True? Just curious on your thoughts on this one.
Oct 31 '09 #5
NeoPa
32,571 Recognized Expert Moderator MVP
While the Switch() function evaluates all expressions before choosing when run in the VBA environment, the SQL equivalent is not so flawed (Just like the SQL versions of IIF() & Choose()). I think it would be more efficient (far less to do and no lines of code to interpret as it goes along as is the case in VBA).

I discovered a long while ago (to my chagrin as I was very inexperienced then on the SQL side - I wasn't even aware it was behind all my QueryDefs then) that any VBA procedure in a QueryDef slowed it down inexorably. The factor was quite extreme. I had to search for alternatives then to avoid such unacceptable delays. I'm pretty sure it's related to the fact that VBA is only a semi-compiled language. It doesn't compile to machine code but to some sort of interpretable half-way house. I'm convinced it would execute more quickly (IE also more efficient than) than the code, notwithstanding the efficient nature of the code.

Clearly it's more readable as it's all in one place. Understanding what a query is doing is never helped by the requirement to switch to another window to see what is happening within a procedure.

All in all, though I recognise the logic of your thoughts on this one, I would never recommend such an approach unless absolutely necessary. I know you're a big fan of all that's VBA, so sorry to be so unequivocal, but there really is no getting away from the answer as far as I see it.

PS. I just reread my previous post and realised I didn't make it clear I was addressing myself to the OP. If it came across as a critique of your post ADezii, it was certainly not intended to. I've updated it to read more clearly.
Oct 31 '09 #6
ADezii
8,834 Recognized Expert Expert
@NeoPa
I never interpreted it as a critique of my code, but simply wanted your opinion on the matter. You know how much I respect it.
Oct 31 '09 #7
chhines
18 New Member
Thanks for all the replies.
Unfortunately, I still can't get it to work.
Everything I try, the [DATEREVIEWDUE] field just shows #NAME? in it.
I changed what appeared to be a couple of errors in the function call, as the [DATEREVIEWASSIG NED] text box was named [DATEREVIEWASASS IGNED] in ADezii's reply.
Also, I changed the Task subform in the string variable, because this field is actually on the Review_subform, not the Task_subform.
I think we're close, I've attached my function again to see if maybe someone sees what is wrong. I'm thinking maybe in the two string variables, maybe we aren't referring EXACTLY to the path or way it needs to call data from those two subforms in the function.
How can I be sure that this path below in bold is correct, is there somewhere I can check this?
dteDateReviewWa sAssigned = Forms![Input Facility Task Reviewer]!Review_Subform .Form![DATEREVIEWASSIG NED]
When I look at the form in Visual Basic Project window under the class objects, it has each referenced as "Form_FORMNAMEHERE" which isn't the name we used when referring to it. Thanks again.
Attached Files
File Type: txt SelectCasestatement.txt (1,012 Bytes, 413 views)
Oct 31 '09 #8
ADezii
8,834 Recognized Expert Expert
@chhines
With your Form/Sub-Form Opened:
  • Press CTRL+G to Open the Debug Window
  • Type in the following line, then press the Enter Key:
    Expand|Select|Wrap|Line Numbers
    1. Debug.Print Forms![Input Facility Task Reviewer]!Review_Subform.Form![DATEREVIEWASSIGNED]
  • The proper Value for ![DATEREVIEWASSIG NED] should be displayed
  • You're right, you are very close to a resolution to this problem.
  • Did you fdorget to set the Control Source of the [DATEREVIEWDUE] Field equal to the Function, as in:
    Expand|Select|Wrap|Line Numbers
    1. =CalcDueDate()
  • If the above does not solve the problem, can you Upload a copy of the Database. I'm sure that the answer is very simple.
Oct 31 '09 #9
NeoPa
32,571 Recognized Expert Moderator MVP
@chhines
Have you tried what I suggested in post #4? I see no reference to it.

If you're not interested that's fine, but if I'm to help, I need to know what you've tried, so that I can direct you further. If you prefer to work exclusively with ADezii's solution then that's no problem, but it would be nice to know if so.
Nov 1 '09 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

9
9194
by: Ben | last post by:
I have two 'Do While Not' statements, that are getting information from the same recordset. If I comment out the first one I can get the results for the second one, and vice-versa. Why is this happening? Is it because it's already at the EOF? If so how do I get it back to the BOF for the 2nd 'Do While Not' statement? '---------------------------------------- 'Create an ADO recordset object Set rs_Report =...
5
4075
by: Rachel Weeden | last post by:
I'm working on an ASP Web application, and am having syntax issues in a WHERE statement I'm trying to write that uses the CInt Function on a field. Basically, I want to select records using criteria of Race, Gender and Crime Code. But the Crime Code field in the table is text, and I cannot change it. I want to use a range of crime codes, so need to convert it to an integer on-the-fly. Here's what I have in my code so far:
9
26843
by: | last post by:
Is it possible to construct a CDO.To statement based on the value of an incoming form drop down list which contains any word such as "ChangeStatus:" How would I construct the IF statement to isolate the 'string' in the incoming variable to allow a branching statement: If Request.form("ChangeStatue") containts the string "Sales Alert" Then '// Else
2
2522
by: serge | last post by:
/* This is a long post. You can paste the whole message in the SQL Query Analyzer. I have a scenario where there are records with values pointing to wrong records and I need to fix them using an Update statement. I have a sample code to reproduce my problem. To simplify the scenario I am trying to use Order related tables to explain a little better the tables i have to work with.
15
2742
by: grunar | last post by:
After some thought on what I need in a Python ORM (multiple primary keys, complex joins, case statements etc.), and after having built these libraries for other un-named languages, I decided to start at the bottom. What seems to plague many ORM systems is the syntactic confusion and string-manipulation required to build the SQL Statements. If you want to do a Left Outer Join, support nested functions, and a nested conditional clause, you'd...
2
3004
by: deanclowe | last post by:
Hi I have a query that is trying to use a field that is created "AS" based on a case statement like this: SELECT CASE WHEN IPOVH = 'Y' THEN '01' WHEN IPHSI = 'Y' THEN '02' WHEN IPVIS = 'Y' THEN '03' ELSE ' ' END AS CRIT_CODE,
7
11445
by: mark | last post by:
Access 2000: I creating a report that has a record source built by the user who selects the WHERE values. An example is: SELECT * FROM CHARGELOG WHERE STDATE Between #10/27/2003# And #11/2/2003# And VehicleID='00000000' And BattID='LKO500HF'. I need to use the records returned to populate text boxes, but the data requires further manipulation. I attempting to use expressions in the control source
19
8379
by: Steve | last post by:
ASP error number 13 - Type mismatch with SELECT...FOR UPDATE statement I got ASP error number 13 when I use the SELECT...FOR UPDATE statement as below. However, if I use SELECT statement without FOR UPDATE, it is fine and no error. I also tried Set objRs = objConn.Execute("SELECT * FROM EMP UPDATE OF EMPNO"), but it still couldn't help. any ideas? I tried to search in the web but couldn't find similar
1
1880
by: hotflash | last post by:
Hi Master CroCrew et All, I am working on a CASE statement to allow different type of searches to search for different type of projects. EVERYTHING WORKS FINE EXCEPT, if the "Any Projects" radio button is checked and if the "All, Complete or Incomplete button is checked, it DISPLAYS everything fine. However, if the "Any Projects" radio button is checked and if the "Complete Projects Only" or "Incomplete Projects Only" button is checked IT...
1
2382
by: robin1983 | last post by:
Dear All, I got stuck in simple problem, I have a two php file one for registration form and one for to check and insert into the table. The problem is that when I get any kind error in validiation the code is working properly but when the condition are fulfilled the code inside the if part is not executing. The following is the code for registration form UserAdd.php <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta...
0
8991
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9374
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9249
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8244
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6796
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6076
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4607
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
2787
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2215
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.