IIf statement for Date and Number Fields 2

[wvmbark]

I have a [NumberField] and [DateField]. I need an IIf statement that says:

If [NumberField]=1 AND If the last day of the month 12 months after [DateField] is > the last day the current month, return 1, else return 0.

I can write the expression for either the NumberField or the DateField, but I don't know how to include an "and" in an IIf statement.

If this is possible, please provide as an expression.

Thanks for any assistance!!!

 

[THWatson]

wvmbark
See if this does it for you...

IIf(([NumberField]=1) And DateSerial(Year(Date()),Month(DateField())+12,0),1,0)

Tom

 

[THWatson]

wvmbark
I have looked at it again, and think I didn't read everything properly. See if this does it...

IIf([NumberField]=1 And DateSerial(Year([DateField]),Month([DateField])+12,0)>DateSerial(Year(Date()),Month(Date())+1,0),1,0)



Tom

 

[PHV]

General syntax:
IIf(Condition1 AND Condition2, True part, False part)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wvmbark]

Thanks Tom and PH!

It almost works... Tom's DateSerial expression is returning the last day of the month 11 months from DateField. I realize I can change the +12 to +13, but I'm curious why the +12 doesn't work.

Also, is Day zero the last day of the month?

Thanks again!

 

[PHV]

Day zero is the last day of the previous month.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[THWatson]

Hmmm, I think that to get the last day of the current month, the expression is...
DateSerial(Year(Date()),Month(Date())+1,0)

Tom

 

[wvmbark]

Tom - So, your original expression is correct if +12 is changed to +13 (adding 12 to the +1)!

Thank again to both of you!!!

 

[THWatson]

Yep, that looks correct! Sometimes this stuff takes tweaking.

Good catch.
Tom

 

[wvmbark]

I am also using the DateSerial function to provide an Expiration Date [ExpDate] based on the [DateField].

ExpDate: DateSerial(Year([DateField]),Month([DateField])+13,0)

My problem is: There's not always a date in [DateField] and for those records I get "#Error". Should I use an Nz function or an IIf function to prevent that??

Also, how come my thread is the only one not in bold font?

Thanks!!!

 

[THWatson]

wvmbark
I'm not clear why some records don't have a date. Should you set things up so that a date is always entered.

But if it's okay for a date field to be left blank, then you could probably check for Nulls, using something such as...
ExpDate: If Not IsNull([DateField]),DateSerial(Year([DateField]),Month([DateField])+13,0),"whatever you want the False part of the statement to be, put it here")

As to why your thread isn't in bold, I don't know. That's a question for the site administrators.

Tom

 

[THWatson]

Sorry, I didn't put that quite correctly...

ExpDate: IIf(Not IsNull([DateField]),DateSerial(Year([DateField]),Month([DateField])+13,0),"whatever you want the False part of the statement to be, put it here"))

Tom