Add to a query Expression!

[Bob V]

Overdue: IIf((DateDiff("d",[MyDate],Date()) Between 30 And 60) And
([Payable]>1),"1 Month",Null)

How would I add theses 3 expressions into the expression
above.............Thanks for any Help.........Bob

Between 60 And 90) And ([Payable]>1),"2 Months",Null
Between 90 And 120) And ([Payable]>1),"3 Months",Null
Between 120 And 2000) And ([Payable]>1),"3 Months Plus",Null

 

[NevilleT]

Hi Bob

I find the easiest way to do this is to build the expression one component
at a time. I use Editpad lite which is like notepad. You can use a test
query to test each component before you insert it in the relevant place in
the main query.

Start with
IIf(Datediff("d")=>60,"True", "False")
Then
iif(datediff("d")<=90,"True", "false")
Once you are sure both work combine them by inserting the second in the
first true

IIf(Datediff("d")=>60, iif(datediff("d")<=90,"True", "false") ,
"False")

I left some spaces to show where it was inserted. You need to remove these.

Another approach that is probably less complex is to create fields in the
query so that you have three columns which are 60_90: then the formula above,
90_120: and 120_2000:

You can then use a final column that checks if Payable>1 and 30_60 = true
the answer = 2 months. It will simplify the whole layout and make debugging
easier. Counting brackets is a pain.

 

[John W. Vinson]

Overdue: IIf((DateDiff("d",[MyDate],Date()) Between 30 And 60) And
([Payable]>1),"1 Month",Null)

How would I add theses 3 expressions into the expression
above.............Thanks for any Help.........Bob

Between 60 And 90) And ([Payable]>1),"2 Months",Null
Between 90 And 120) And ([Payable]>1),"3 Months",Null
Between 120 And 2000) And ([Payable]>1),"3 Months Plus",Null

Ideally, don't do it this way. Instead create a little four-row, three field
table with the ranges and the desired message, and join this table to your
query. Having the message embedded in a complicated nested IIF function, or
even a Switch() function (below), makes your database hard to maintain and
obscure.

If you don't want to do this, you can use the Switch() function. It takes
arguments in (I just learned today!) up to 29 pairs; it evaluates the pairs
left to right, and when it first encounters a pair for which the first element
is true, it returns the second element. E.g.

Switch(DateDiff("d", [MyDate], Date()) < 30, Null, DateDiff("d", [MyDate],
Date()) < 60, "1 Month", DateDiff("d", [MyDate], Date()) < 90, "2 Months",
DateDiff("d", [MyDate], Date()) < 120, "3 Months", True, "Uncle Vinnie will be
over soon deadbeat!")

 

[Steve Schapel]

Bob,

You could try it like this:

Overdue:
Switch((Date()-[MyDate])<30,Null,(Date()-[MyDate])<60,"1",(Date()-[MyDate])<90,"2",(Date()-[MyDate])<120,"3",(Date()-[MyDate])>=120,"3+")+"
Months")

I think this approach would be simpler that nestedd IIf() functions.
The other alternative would be to write a user-defined function.

 

[Bob V]

Thanks John That works Great, except for it does not include ([Payable]>1)
so its showing dates for Clients not owing $0.00
Regards Bob

Overdue: IIf((DateDiff("d",[MyDate],Date()) Between 30 And 60) And
([Payable]>1),"1 Month",Null)

How would I add theses 3 expressions into the expression
above.............Thanks for any Help.........Bob

Between 60 And 90) And ([Payable]>1),"2 Months",Null
Between 90 And 120) And ([Payable]>1),"3 Months",Null
Between 120 And 2000) And ([Payable]>1),"3 Months Plus",Null

Ideally, don't do it this way. Instead create a little four-row, three
field
table with the ranges and the desired message, and join this table to your
query. Having the message embedded in a complicated nested IIF function,
or
even a Switch() function (below), makes your database hard to maintain and
obscure.

If you don't want to do this, you can use the Switch() function. It takes
arguments in (I just learned today!) up to 29 pairs; it evaluates the
pairs
left to right, and when it first encounters a pair for which the first
element
is true, it returns the second element. E.g.

Switch(DateDiff("d", [MyDate], Date()) < 30, Null, DateDiff("d", [MyDate],
Date()) < 60, "1 Month", DateDiff("d", [MyDate], Date()) < 90, "2 Months",
DateDiff("d", [MyDate], Date()) < 120, "3 Months", True, "Uncle Vinnie
will be
over soon deadbeat!")

 

[John W. Vinson]

Thanks John That works Great, except for it does not include ([Payable]>1)
so its showing dates for Clients not owing $0.00

So? Add another pair.

 

[Bob V]

Thanks John , worked it out Just one little problem my query shows
#error for anything that does not have a date in [MyDate], But it does not
show that error in my Combo Box........................Thanks Bob
Overdue: Switch(DateDiff("d",[MyDate],Date())<30 And
[Payable]>1,Null,DateDiff("d",[MyDate],Date())<60 And [Payable]>1,"1
Month",DateDiff("d",[MyDate],Date())<90 And [Payable]>1,"2
Months",DateDiff("d",[MyDate],Date())<120 And [Payable]>1,"3
Months",DateDiff("d",[MyDate],Date())>121 And [Payable]>1,"+4 Months")

Thanks John That works Great, except for it does not include ([Payable]>1)
so its showing dates for Clients not owing $0.00

So? Add another pair.

 

[Bob V]

What about [MyDate] ="", "No Date"
Regards Bob

Thanks John , worked it out Just one little problem my query shows
#error for anything that does not have a date in [MyDate], But it does not
show that error in my Combo Box........................Thanks Bob
Overdue: Switch(DateDiff("d",[MyDate],Date())<30 And
[Payable]>1,Null,DateDiff("d",[MyDate],Date())<60 And [Payable]>1,"1
Month",DateDiff("d",[MyDate],Date())<90 And [Payable]>1,"2
Months",DateDiff("d",[MyDate],Date())<120 And [Payable]>1,"3
Months",DateDiff("d",[MyDate],Date())>121 And [Payable]>1,"+4 Months")

Thanks John That works Great, except for it does not include
([Payable]>1)
so its showing dates for Clients not owing $0.00

So? Add another pair.

 

[Bob V]

Oops: [MyDate] =""And [Payable]>1,"No Date"
Regards Bob

What about [MyDate] ="", "No Date"
Regards Bob

Thanks John , worked it out Just one little problem my query shows
#error for anything that does not have a date in [MyDate], But it does
not show that error in my Combo Box........................Thanks Bob
Overdue: Switch(DateDiff("d",[MyDate],Date())<30 And
[Payable]>1,Null,DateDiff("d",[MyDate],Date())<60 And [Payable]>1,"1
Month",DateDiff("d",[MyDate],Date())<90 And [Payable]>1,"2
Months",DateDiff("d",[MyDate],Date())<120 And [Payable]>1,"3
Months",DateDiff("d",[MyDate],Date())>121 And [Payable]>1,"+4 Months")

Thanks John That works Great, except for it does not include
([Payable]>1)
so its showing dates for Clients not owing $0.00

So? Add another pair.

 

[John W. Vinson]

Oops: [MyDate] =""And [Payable]>1,"No Date"

A Date/Time field will never be equal to a zero length string (or any other
kind of string). Try

IsNull([MyDate]) And Payable > 1