473,320 Members | 1,953 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Using IIf in a query

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:
Expand|Select|Wrap|Line Numbers
  1. Event Date: IIf([PROSPECT_FLAG]="Prosp" And
  2.   IIf([ACCT_OPN_DT]>[Client Ref Create Date],[ACCT_OPN_DT],
  3.   IIf([TRANSFER_DATE] Is Not Null And 
  4.   IIf([ACCT_OPN_DT]>[TRANSFER_DATE],[ACCT_OPN_DT],
  5.   IIf([TRANSFER_DATE] Is Null And 
  6.   IIf([ACCT_OPN_DT]>[Client Ref Create Date],[ACCT_OPN_DT],
  7.   IIf([ACCT_OPN_DT] Is Null,[CUST_OPEN_DATE],
  8.   IIf([CUST_OPEN_DATE] Is Null,[ACCT_OPN_DT],
  9.   IIf([ACCT_OPN_DT] Is Not Null And [CUST_OPEN_DATE] Is Not Null,
  10.   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.
Nov 3 '09 #1
8 2831
NeoPa
32,556 Expert Mod 16PB
I went through your explanation and it :
  1. Leaves various gaping holes that are not explained.
  2. Doesn't match the code posted.
  3. 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 :
  1. We will be in a position to help you.
  2. 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.
Nov 3 '09 #2
ADezii
8,834 Expert 8TB
@RonnieG
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.
Nov 3 '09 #3
NeoPa
32,556 Expert Mod 16PB
@ADezii
What are you referring to that intrinsically won't work? The current state of the SQL or SQL in general?
Nov 3 '09 #4
ADezii
8,834 Expert 8TB
@NeoPa
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)!
Nov 3 '09 #5
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."
Nov 3 '09 #6
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.
Nov 3 '09 #7
NeoPa
32,556 Expert Mod 16PB
@RonnieG
Yes. But a query is an execution of SQL. Structured Query Language is what queries are written in.
Nov 4 '09 #8
NeoPa
32,556 Expert Mod 16PB
@RonnieG
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.
Nov 4 '09 #9

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

Similar topics

6
by: PG | last post by:
When deleting a row from the database, that id is now missing. So what I'm trying to do is update in a loop (maybe an sql loop if there is one) of all of the id numbers - sort of like renaming...
3
by: GL | last post by:
Hi, Is there a way to add a field to an existing table using a query of some sort (without needing to manually add a field to the table). I know how to do it with a make table query, but I have...
3
by: StBond | last post by:
Hi everyone, I am new to Access and Visual Basic so things my be getting across a bit cloudy. I only started using VB for one week. I am having a little problem with the database that I am...
3
by: eagleofjade | last post by:
I am trying to help a friend who is learning VB.net in school. I have done VB programming for a number of years using VB 6. He needs to open a query in an Access database that has parameters so he...
10
by: Robert | last post by:
How do you get an accurate count of the number of records returned from a query when using linked tables. I have an access 2003 database as a front end to another access 2003 database that...
11
by: funky | last post by:
hello, I've got a big problem ad i'm not able to resolve it. We have a server running oracle 10g version 10.1.0. We usually use access as front end and connect database tables for data extraction....
21
KevinADC
by: KevinADC | last post by:
Note: You may skip to the end of the article if all you want is the perl code. Introduction Uploading files from a local computer to a remote web server has many useful purposes, the most...
0
debasisdas
by: debasisdas | last post by:
Using Subqueries ================== The sub query is often referred to as a nested SELECT, Sub - SELECT, or inner SELECT statement. The sub query executes once before the main query. The...
221
Atli
by: Atli | last post by:
You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t. In situations where your PHP application...
6
by: BOMEz | last post by:
So i've recently been starting to program PHP in an object oriented way, but I'm running into some difficulties in from a design stand point and from an object oriented stand point: Issue 1: In my...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.