Date Function in Query builder

[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.

 

[John Spencer]

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.

 

[steinmetzw42 via AccessMonster.com]

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.

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:

[quoted text clipped - 8 lines]

IIf? If so, could someone please point me in the right direction?
Thanks.

 

[John Spencer]

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,

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.

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:

[quoted text clipped - 8 lines]

IIf? If so, could someone please point me in the right direction?
Thanks.

 

[steinmetzw42 via AccessMonster.com]

There is a unique Identifyer called Inv# in both tables which the queries are
connected by showing which one it should be pulling.

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,

[quoted text clipped - 40 lines]