Expression with mixed value result

[chelseasikoebs]

I want to have a field have an expression like this: IIf(Month([Field1])=10,"10/31/"+(year([Field1])+1),"0"). Basically, Field2 needs to look at Field1's date and whatever month has been entered, it needs to return that month, the last day of that month, and one year from the year entered. Examples:

Entered in Field1: Returned in Field2:
10/22/2013 10/31/2014
5/13/2013 5/31/2014
4/2/2011 4/30/2012



I run into a couple problems. One, February. Will it be the 28th or the 29th? Two, I get a Type error when the expression returns true, probably because I have mixed value types in the expression. Is there a way around this? Is there a better expression for what I'm trying to accomplish??

I'm new to Access. Can you do lots of nested If statements in Access like you can in Excel? or would it maybe be easier to set the main table up in Excel and work off of that, but import it into Access as a linked table to create queries and reports from? I've just come across problems with dates not showing up in a date format in Access if they're a calculated field in Excel. Any opinions/suggestions would be GREATLY appreciated! This will end up being a very large file so I want to do it the best way possible!

[akhmadahdiyat]

assalamualaikum..

the main problem is : day of month
22 october, retrieve the day : 22
+1 year + 1 month & subtract date with 22 day : (22 oct + 1 month) - 22 day = (22 nov) - 22 day = 31 oct

alhamdulillah.. it's solved: (in query..)
Expr1: DateSerial((DatePart("yyyy";[datetest]![Field1])+1);(DatePart("m";(DatePart("yyyy";[datetest]![Field1])+1))+1);DatePart("d";(DatePart("yyyy";[datetest]![Field1])+1)))-(DatePart("d";(DatePart("yyyy";[datetest]![Field1])+1)))

I split into 2 expression:

Expr1: DateSerial(DatePart("yyyy";[datetest]![Field1])+1;(DatePart("m";[datetest]![Field1])+1);DatePart("d";[datetest]![Field1]))

Expr2: [Expr1] -(DatePart("d";(DatePart("yyyy";[datetest]![Field1])+1)))

Result

datetest_query
Field1	Expr1	Expr2
23/10/2013	23/11/2014	31/10/2014
03/10/2013	03/11/2014	31/10/2014
13/02/2013	13/03/2014	28/02/2014
27/02/2013	27/03/2014	28/02/2014
28/02/2013	28/03/2014	28/02/2014
28/02/2014	28/03/2015	28/02/2015
27/02/2015	27/03/2016	29/02/2016
28/02/2015	28/03/2016	29/02/2016
28/02/2016	28/03/2017	28/02/2017
29/02/2016	29/03/2017	28/02/2017

[chelseasikoebs]

Wow! Much more complicated than I ever thought it would be! Thank you so much!!