Date Function in Query builder

  • Thread starter Thread starter The Joker via AccessMonster.com
  • Start date Start date
T

The Joker via AccessMonster.com

Hello All,
I have the following code in a query expression:
IIf ( [tbl_RemitDays]![Frequency] = "Daily", Date(), IIf ( [tbl_RemitDays]!
[Frequency] = "CD" , DateSerial(Day= [tbl_RemitDays]![Days] ), WeekDay(Day+
[tbl_RemitDays]![Days] ) )

I am recieving a "The expression you entered has a function containing the
wrong number of arguments" error. Is it possible to have an IIf within an
IIf? If so, could someone please point me in the right direction? Thanks.
 
IIf ( [tbl_RemitDays]![Frequency] = "Daily", Date(),
IIf ( [tbl_RemitDays]![Frequency] = "CD" ,
DateSerial(Day= [tbl_RemitDays]![Days] ),
WeekDay(Day+[tbl_RemitDays]![Days] ) )

DateSerial requires three numbers - a year, a month, and a day Your
expression supplies it with one value which is either true or false.
Perhaps you want to use DateAdd or some other function.

IIF can be nested up to 7 levels deep - something I would never attempt.
Your structure for the IIF is correct, however the expressions you are using
make letter to no sense to me. Could you describe what you are attempting
to do?

You might look at the Switch function as an alternative.
 
John,

I have a table as follows (example):
Frequency | Days
------------------------------------
Daily | Null
CD | 5
CD | 6
BD | 4
BD | 6

CD = Calendar Days
BD = Business Days

What I need is if Frequency's value is Daily then just put the current date.
If the value is CD then it's an exact date (going forward) such as 6 would be
1/6/06. If the value is BD then it would be that many business days (week
days) added to current day (eg.. 3 would be 12/21/06). Let me know if you
need further clarification on something. Thank you so much for your help.

John said:
IIf ( [tbl_RemitDays]![Frequency] = "Daily", Date(),
IIf ( [tbl_RemitDays]![Frequency] = "CD" ,
DateSerial(Day= [tbl_RemitDays]![Days] ),
WeekDay(Day+[tbl_RemitDays]![Days] ) )

DateSerial requires three numbers - a year, a month, and a day Your
expression supplies it with one value which is either true or false.
Perhaps you want to use DateAdd or some other function.

IIF can be nested up to 7 levels deep - something I would never attempt.
Your structure for the IIF is correct, however the expressions you are using
make letter to no sense to me. Could you describe what you are attempting
to do?

You might look at the Switch function as an alternative.
Hello All,
I have the following code in a query expression:
Click to expand...
[quoted text clipped - 8 lines]
IIf? If so, could someone please point me in the right direction?
Thanks.
Click to expand...
Click to expand...
 
Interesting and if the frequency is CD how does one know whether to use 5 or
6 and the Same for BD?

IIF([Frequency] = "Daily", Date(),
IIF([Frequency] = "CD", DateAdd("d", 5, Date()),
IIF([Frequency]="BD",DateAdd("d",4,Date()))))


steinmetzw42 via AccessMonster.com said:
John,

I have a table as follows (example):
Frequency | Days
------------------------------------
Daily | Null
CD | 5
CD | 6
BD | 4
BD | 6

CD = Calendar Days
BD = Business Days

What I need is if Frequency's value is Daily then just put the current
date.
If the value is CD then it's an exact date (going forward) such as 6 would
be
1/6/06. If the value is BD then it would be that many business days (week
days) added to current day (eg.. 3 would be 12/21/06). Let me know if you
need further clarification on something. Thank you so much for your help.

John said:
IIf ( [tbl_RemitDays]![Frequency] = "Daily", Date(),
IIf ( [tbl_RemitDays]![Frequency] = "CD" ,
DateSerial(Day= [tbl_RemitDays]![Days] ),
WeekDay(Day+[tbl_RemitDays]![Days] ) )

DateSerial requires three numbers - a year, a month, and a day Your
expression supplies it with one value which is either true or false.
Perhaps you want to use DateAdd or some other function.

IIF can be nested up to 7 levels deep - something I would never attempt.
Your structure for the IIF is correct, however the expressions you are
using
make letter to no sense to me. Could you describe what you are attempting
to do?

You might look at the Switch function as an alternative.
Hello All,
I have the following code in a query expression:
Click to expand...
[quoted text clipped - 8 lines]
IIf? If so, could someone please point me in the right direction?
Thanks.
Click to expand...
Click to expand...
Click to expand...
 
There is a unique Identifyer called Inv# in both tables which the queries are
connected by showing which one it should be pulling.


John said:
Interesting and if the frequency is CD how does one know whether to use 5 or
6 and the Same for BD?

IIF([Frequency] = "Daily", Date(),
IIF([Frequency] = "CD", DateAdd("d", 5, Date()),
IIF([Frequency]="BD",DateAdd("d",4,Date()))))
John,
Click to expand...
[quoted text clipped - 40 lines]
Click to expand...
 
Back
Top