Field Validation using date range for current year

[dfrost]

In MS Access 2007, I am having difficulty with the syntax for a field validation using a date range for current year.

For example, >= #1/1/2013# And <#1/1/2014# will prevent users from entering a date before 1/1/2013 and after 1/1/2014.

The problem is I need this to transfer from year to year without updating the year, so I would need something like >="1/1/" & format(date()," yyyy") and <="12/31/" & format(date()," yyyy"). But I cannot seem to get this to work.

I have tried using the validation on the form in a before BeforeUpdate(Ca ncel As Integer) sub, but I get a validation error. Help!

[Seth Schrock]

While I have never done this before, this is what I would try. In the form's OnLoad event put the following (assuming the textbox's name is txtMyDate).

Me.txtMyDate.ValidationRule = ">=#1/1/" & Year(Date()) & _ 
"# AND <#1/1/" & Year(DateAdd("yyyy", 1, Date())) & "#"

This should put in all the parts that you need.

[dfrost]

Okay, I see where I was messing up. I knew I needed the pound sign, but I was not sure where to put it inside the quotes. Thank you I will try this!

[Seth Schrock]

I tested the string and it does come out to the correct values, but I have never tried to set a field's validation rule this way so I can't guaranty that this will work.

[ADezii]

Personally, I prefer to handle this as hard coded in a Table Validation Rule. Assuming the DATE Field in your Table is named [MyDate], then the Table Level Validation Rule would be:

[MyDate]>=DateSerial(Year(Date()),1,1) And [MyDate]<=DateSerial(Year(Date()),12,31)

P.S. - Do not forget to also enter a meaningful Validation Text.

[dfrost]

The following works perfectly for the Validation Rule on the form field. Thanks so much for all the help!

>=DateSerial(Year(Date()),1,1) And <=DateSerial(Year(Date()),12,31)

[Seth Schrock]

Thats nice to know ADezii. I haven't used DateSerial yet and wasn't in my "bag of tricks", but it is now. Nice to know how/when to use it.

[ADezii]

@Seth Schrock:
We all learn something new every day, don't we Seth?

[Seth Schrock]

One of the things I love about this site and group of people!

[nico5038]

When it's always a year you could also use ADezii's solution with:

YEAR([MyDate])=YEAR(Date())

It's however my experience that users often need some extra days in the new year to correct previous year data, so I often switch to storing the Year field in a tblSystem and add a [Start New Year] button to a form to allow the user to decide when to start the new recording. This button than updates the Year field with the current year.

Nic;o)

[JMichaelM]

When it's always a year you could also use ADezii's solution with:

YEAR([MyDate])=YEAR(Date())

It's however my experience that users often need some extra days in the new year to correct previous year data, so I often switch to storing the Year field in a tblSystem and add a [Start New Year] button to a form to allow the user to decide when to start the new recording. This button than updates the Year field with the current year.

Nic;o)

How do you subtract 1 or two years from the year?

[nico5038]

Just like you wrote:
Year(datefield)-1

Nic;o)

[JMichaelM]

Im trying to integrate into the current logic but it is turning to yes for each year. If its 2017, it should be for previous year.


If (Year(Nz([WCCClmDt1])) = 2016) Then
WCCNF1 = "Yes"
ElseIf (Year(Nz([WCCMRDt1])) = 2016) And _
(Year(Nz([wccmrdtwgt])) = 2016) And _
(Year(Nz([wccmrdthgt])) = 2016) Then
WCCNF1 = "Yes"
Else
WCCNF1 = "No"
End If

[nico5038]

Just check the individual datefield to match 2016. A NULL value will return 0 (zero), that won't match 2016. Better to use NZ(year(datefie ld),2016) to force a TRUE value.

Nic;o)