Using IIf in a query | Newbie | | Join Date: Nov 2009
Posts: 3
| |
I'm trying to create a function in a query but not sure how or if it's the most effecient way. Here is what I'm wanting to do:
If the [prospect flag] = Yes and the [acct opn dt]>[client ref create dt] use the [acct opn dt], if the [transfer dt] is not null and the [transfer dt]>[acct opn dt] use the [transfer dt], if the [transfer dt] is null and the [acct opn dt]>[client ref create dt] use the [acct opn dt], if the [acct opn dt] is null use the [cust opn dt], if the [cust opn dt] is null use the [acct opn dt], if the [acct opn dt] and the [cust opn dt] is null use the greater of the two.
Here is what I've tried but it's not pulling in the correct info: - Event Date: IIf([PROSPECT_FLAG]="Prosp" And
-
IIf([ACCT_OPN_DT]>[Client Ref Create Date],[ACCT_OPN_DT],
-
IIf([TRANSFER_DATE] Is Not Null And
-
IIf([ACCT_OPN_DT]>[TRANSFER_DATE],[ACCT_OPN_DT],
-
IIf([TRANSFER_DATE] Is Null And
-
IIf([ACCT_OPN_DT]>[Client Ref Create Date],[ACCT_OPN_DT],
-
IIf([ACCT_OPN_DT] Is Null,[CUST_OPEN_DATE],
-
IIf([CUST_OPEN_DATE] Is Null,[ACCT_OPN_DT],
-
IIf([ACCT_OPN_DT] Is Not Null And [CUST_OPEN_DATE] Is Not Null,
-
IIf([ACCT_OPN_DT]>[CUST_OPEN_DATE],[ACCT_OPN_DT],[CONVERTED_CUST_OPEN_DATE])))))))
I get this error message: The expression you entered has a function containing the wrong number of arguments. I've searched this error message and tried several things, but it doesn't seem to be working... Any direction would be greatly appreciated.
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,672
| | | re: Using IIf in a query
I went through your explanation and it : - Leaves various gaping holes that are not explained.
- Doesn't match the code posted.
- Has nonsensical instructions in it like "if both values are Null then select the greater of the two".
I know logic as complicated as this can be hard to get your head around sometimes, but without a sensible question we cannot do magic.
If you want to give this some more thought and explain it clearly and succinctly I suspect : - We will be in a position to help you.
- You may not even require the help. Often when someone puts in the effort to make the question make sense they find the answer is much less of a problem.
Either way you will get a workable solution.
PS. CODE tags are mandatory on these forums. A string as long as that will need to be formatted manually first before posting though - otherwise it is unreadable.
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,213
| | | re: Using IIf in a query
Quote:
Originally Posted by RonnieG  I'm trying to create a function in a query but not sure how or if it's the most effecient way. Here is what I'm wanting to do:
If the [prospect flag] = Yes and the [acct opn dt]>[client ref create dt] use the [acct opn dt], if the [transfer dt] is not null and the [transfer dt]>[acct opn dt] use the [transfer dt], if the [transfer dt] is null and the [acct opn dt]>[client ref create dt] use the [acct opn dt], if the [acct opn dt] is null use the [cust opn dt], if the [cust opn dt] is null use the [acct opn dt], if the [acct opn dt] and the [cust opn dt] is null use the greater of the two.
Here is what I've tried but it's not pulling in the correct info:
- Event Date: IIf([PROSPECT_FLAG]="Prosp" And
-
IIf([ACCT_OPN_DT]>[Client Ref Create Date],[ACCT_OPN_DT],
-
IIf([TRANSFER_DATE] Is Not Null And
-
IIf([ACCT_OPN_DT]>[TRANSFER_DATE],[ACCT_OPN_DT],
-
IIf([TRANSFER_DATE] Is Null And
-
IIf([ACCT_OPN_DT]>[Client Ref Create Date],[ACCT_OPN_DT],
-
IIf([ACCT_OPN_DT] Is Null,[CUST_OPEN_DATE],
-
IIf([CUST_OPEN_DATE] Is Null,[ACCT_OPN_DT],
-
IIf([ACCT_OPN_DT] Is Not Null And [CUST_OPEN_DATE] Is Not Null,
-
IIf([ACCT_OPN_DT]>[CUST_OPEN_DATE],[ACCT_OPN_DT],[CONVERTED_CUST_OPEN_DATE])))))))
I get this error message: The expression you entered has a function containing the wrong number of arguments. I've searched this error message and tried several things, but it doesn't seem to be working... Any direction would be greatly appreciated. Unless I am counting incorrectly, you have 10 Left Parenthesis (')'), and 7 Right Parenthesis (')'). Besides all the points mentioned by NeoPa, this in and of itself will not work. Once you have your logic correctly worked out, you can encapsulate it within a Function that returns the Proper Value.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,672
| | | re: Using IIf in a query
Quote:
Originally Posted by ADezii Besides all the points mentioned by NeoPa, this in and of itself will not work. Once you have your logic correctly worked out, you can encapsulate it within a Function that returns the Proper Value. What are you referring to that intrinsically won't work? The current state of the SQL or SQL in general?
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,213
| | | re: Using IIf in a query
Quote:
Originally Posted by NeoPa What are you referring to that intrinsically won't work? The current state of the SQL or SQL in general? I can be wrong, but I thought that the number of opening and closing Parenthesis were offset (current State of SQL). Could be the olde eyes (LOL)!
| | Newbie | | Join Date: Nov 2009
Posts: 3
| | | re: Using IIf in a query
Thanks for responding, sorry for code tags and not being clear.
When I run the code that is associated with (Event Date:), I'm using it in a query not SQL. It doesn't run it tells me that "The expression you entered has a function containing the wrong number of arguments."
| | Newbie | | Join Date: Nov 2009
Posts: 3
| | | re: Using IIf in a query
NeoPa and ADezii,
Let me go back and explain what I have and what I'm trying to do.
I have Table that has a prosp field (Yes or blank), acct open date field (date or blank), client ref create date field (date or blank), transfer date field (date or blank) and a cust open date field (date or blank). I have a field in the table that is called event date that is blank. I'm trying to create an update query with the IIf function to populate that field. I could be going about it wrong, I just started with the IIf function because I thought I knew how to code it.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,672
| | | re: Using IIf in a query
Quote:
Originally Posted by RonnieG When I run the code that is associated with (Event Date:), I'm using it in a query not SQL. Yes. But a query is an execution of SQL. Structured Query Language is what queries are written in.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,672
| | | re: Using IIf in a query
Quote:
Originally Posted by RonnieG NeoPa and ADezii,
Let me go back and explain what I have and what I'm trying to do. What a good idea.
Why don't you go back and post exactly what you have (A field name is a field name. It is not an approximation that looks somewhat similar) in the way of fields, and exactly (no logic holes this time please) how the result you need should be determined.
Logic isn't cozy or comfortable and it's certainly not woolly. It is demanding. It needs to be exactly correct or it will certainly fail to provide what you need. Until you specify exactly what you want we cannot help you to translate that into something Access will understand.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,272 network members.
|
