expression value in table to return FY

[pastorkc]

I have this expression in my Default Value area of my table for field FY. It just errors out. I would like it to look at the Date field and if the month is less than 7 then put in the year, if it is greater than 7 then add 1 to the year.

=IIf(Month("«date»")<7,Year("«date»"),Year("«date»")+1)

 

[Bosquedeguate]

Your formula should be "=IF" not "IIF"

see test sheet below; it has 2 ways of implementing your formula:

GetLastRow.xlsm
	A	B
39	7/19/2023	2024
40		2024
Sheet3

Cell Formulas
Range	Formula
B39	B39	=IF(MONTH(A39)<7,YEAR(A39),YEAR(A39)+1)
B40	B40	=YEAR(A39)+IF(MONTH(A39)<7,0,1)

 

[Bosquedeguate]

2nd formula calculates the YEAR then adds 0 or 1 depending on the month value.

 

[Fluff]

Not sure if you realised. but this is in the Access part of the site, not Excel.

 

[Joe4]

I don't think you can put formulas in the Default Value of Table Fields in Microsoft Access.
You really shouldn't anyway, as that would violate the Rules of Normalization (any value that can be calculated form other values in the table should NOT be stored as a hard-coded value).
Since it can be calculated, simply add that calculated field to a query on that table.

 

[pastorkc]

Not sure if you realised. but this is in the Access part of the site, not Excel.

Yes its an access question. I an trying to put an expression as a default value in a field.

 

[Joe4]

As a follow-up to my previous reply, note that newer versions of Access allow you to do calculated fields in Tables, but it is highly advised NOT to do this, and to do it in queries instead.
Here is an article which discusses that: Why One Should Use Queries instead of Calculated Fields for Access Tables.

Another reason not to is that it violates the Rules of Normalization, and other database programs like SQL do not allow this, so if you ever need to convert the database or interact with the database programs, you will probably have issues.

Simple rule of thumb to follow: Never store in a table that which can easily be calculated in a query.
There is seldom ever a need to. You can use a Query for almost anything that you use a Table for.

 

[pastorkc]

As a follow-up to my previous reply, note that newer versions of Access allow you to do calculated fields in Tables, but it is highly advised NOT to do this, and to do it in queries instead.
Here is an article which discusses that: Why One Should Use Queries instead of Calculated Fields for Access Tables.

Another reason not to is that it violates the Rules of Normalization, and other database programs like SQL do not allow this, so if you ever need to convert the database or interact with the database programs, you will probably have issues.

Simple rule of thumb to follow: Never store in a table that which can easily be calculated in a query.
There is seldom ever a need to. You can use a Query for almost anything that you use a Table for.

Awesome, good information. Thank you

 

[Joe4]

You are welcome.

In developing Access databases, I highly advise you follow the Rules of Normalization in table design. If you do so, it will save you a lot of headaches down the road.
See: Database normalization description - Office