Date calculation - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Date calculation

Robert Freeman

3 years ago

I'm working my way through designing a Membership database and one of the things that I need to do is default a date field to 1 January (Field 2) when any date entered in another field (Field 1) is BEFORE 1 January. This will apply for any given membership year.
For example: If Field 1 date is 30 November 2022 then the date in Field 2 must default to 1 January 2023. This date will then be used in a query.
Any date entered in Field 1 that is 1 January 2023 or thereafter would be entered in Field 2 as the actual date entered in Field 1.
Here is the Excel expression used in the current Excel Database that I'm rebuilding in Access where "Z5" is the cell number:
=IF(Z5>=DATE(2023,1,1),Z5,"01/01/2023")
Can anyone help me out please with the calculation to achieve this in Access.
Many thanks. Bob Freeman.

	Kevin Robertson @Reply 3 years ago
Here are a couple of options for your Calculated Field. Both will give you the same result. `EndDate: IIf([StartDate]>=#2023-01-01#,[StartDate],#2023-01-01#)` `EndDate: IIf([StartDate]>=DateSerial(2023,1,1),[StartDate],#2023-01-01#)` Here are a couple of videos on using Dates in Access: Date Criteria DateSerial

	Robert Freeman OP @Reply 3 years ago
Hi and thanks for the response. I've created a table with just StartDate and EndDate with the start date formatted at Date/Time and the end date a calculated field. I am getting an error message when I try either of the expressions: "The expression could not be saved because the result type, such as binary or NULL, is not supported by the server." What am I doing wrong please??

	Kevin Robertson @Reply 3 years ago
Don't use Calculated Fields in your tables. See Evil Access Stuff EndDate should be calculated in a query.

	Kevin Robertson @Reply 3 years ago

	Robert Freeman OP @Reply 3 years ago
Kevin. Thanks for helping me out but whereas I understand the making of the query I do not understand (too inexperienced yet) how to run and then relate the result of the query automatically for an individual member's record each time one is created in a table (or form). Is that even possible?

	Robert Freeman OP @Reply 3 years ago
Hi. I have now figured out how this works but I am not getting the results shown in your screenshot above. The dates for 2022 are not updating to 1-1-2023, they remain the same as the 2022 date entered in 'StartDate'. Any suggestions please. I have tried both your options for the query calculation.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View