Force year of 2013

[kujeremy]

OK - this is probably going to be so simple I am going to slap myself in the face when I get the answer, but for the life of me I can not get this to work. What I need is basically a mask to always make the year in a date field 2013.

What I have done is used an Input Mask on Short Date of 00/00/0013 - it looks like it will work initially but when you leave the field the date changes to 01/01/2020

So next I put an Input Mask on Short Date of 00/00/2013 - that brings up a date field of __/__/2_13 <- why is there no zero? And when I leave the field the year changes so it shows 01/01/2000

I realize I am being really dense (especially since I am positive I have done this before) but how on Earth can in ensure that the users can only enter a 2013 date!

The user needs the ability to enter anytime throughout the year because typically what they will be entering is about 2 weeks prior to todays date so I can't just put a Date() field on the form (although there is one of those for a timestame).

Once again I apologize because I know that this is going to be so damn simple, it's just one of those days apparently where my brain does not work at all because I am completely lost on this right now and don't have the time to work it out myself.

 

[Adam Caramon]

Once again I apologize because I know that this is going to be so damn simple, it's just one of those days apparently where my brain does not work at all because I am completely lost on this right now and don't have the time to work it out myself.

If you want to use an input mask, try this: 99/99/\2\0\1\3

To elaborate:

So next I put an Input Mask on Short Date of 00/00/2013 - that brings up a date field of __/__/2_13 <- why is there no zero? And when I leave the field the year changes so it shows 01/01/2000

0 is a special character when used for an input mask. Much the same as 9 is a special character. As such, you have to use a "\" preceding a 0 in order for Access to understand that you mean the number instead of the input mask function the number represents.

 

[kujeremy]

Thanks for the suggestion Adam - however doing as you suggest is not working. Whenever I apply that mask to the field on the form I get:

The value you entered isn't valid for this field.
For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.

The field is Date/Time format.

It DOES work if I change the data to text, however doing that will mess up the reporting that is done from this database which is based on date spans. After changing the field to text and applying the mask, the date stored as 0105 (this is when I entered 01/05/2013 on the form)

I have no preference on how this is done and appreciate any help!

 

[Adam Caramon]

My fault, I didn't set my text box to the date format when I tested it. This works for me:

Enter 99/99/2\013;0;_

which Access will format to

99/99/"2013";0;_

 

[Pat Hartman]

Swell. And what is your plan for next year? Is there any time during the year when you will need to ender data for two years?

You are probably better off using a date range to prevent them from entering a date older than two weeks or greater than today or whatever makes sense for your situation.

In the BeforeUpdate event of the form:

If Me.SomeDate is > Date() or Me.SomeDate < Date() - 14 Then
    Msgbox "Some Date is out of range.  It must be blah, blah", vbOKOnly
    Cancel = True
    Me.SomeDate.SetFocus
    Exit Sub
End If

 

[kujeremy]

Pat - after the last 2013 record is entered everything will be for the next year, but you make a great point because the db has been in use since I first made it about 4 years ago - it is one I made for myself years ago because when I took over the process a spreadsheet was being used, but this db made things much easier. Since then I've left that job and others have taken over this task and apparently it is to difficult to to enter the correct year which is why I was asked if I could force 2013, but I suppose if I don't want to be bothered about this every year coming up with your suggestion may be the route to go.

Thanks!

 

[kujeremy]

Hi Pat - I'm trying to use your code as you have below but continue to get a compile error : Syntax error on the > sign

Any suggestions on why that error is being received?

Swell. And what is your plan for next year? Is there any time during the year when you will need to ender data for two years?

You are probably better off using a date range to prevent them from entering a date older than two weeks or greater than today or whatever makes sense for your situation.

In the BeforeUpdate event of the form:

If Me.SomeDate is > Date() or Me.SomeDate < Date() - 14 Then
    Msgbox "Some Date is out of range.  It must be blah, blah", vbOKOnly
    Cancel = True
    Me.SomeDate.SetFocus
    Exit Sub
End If

 

[Pat Hartman]

Please copy and post your code. There is no way for us to devine a syntax error.

 

[kujeremy]

Devine a syntax? LOL. Anyhow - it is your exact code copied and pasted in the BeforeUpdate event - the only change I made was the field name to "Index_Date". The "If Me.Index..." line is highlighted red and I receive the aforementioned error.

Private Sub Index_Date_BeforeUpdate(Cancel As Integer)
If Me.Index_Date is > Date() or Me.Index_Date < Date() - 14 Then
MsgBox "Index Date entered is invalid.", vbOKOnly
Cancel = True
Me.Index_Date.SetFocus
Exit Sub
End If
End Sub

Please copy and post your code. There is no way for us to devine a syntax error.

 

[StarGrabber]

Do it this way:

Private Sub Index_Date_BeforeUpdate(Cancel As Integer)

    If IsNull(Index_Date.Value) Then
        Exit Sub
    End If
    
    If DateValue(Me.Index_Date) > Date Or DateValue(Me.Index_Date) < Date - 14 Then
        MsgBox "Index Date entered is invalid.", vbExclamation
        Cancel = True
    End If

End Sub

The two lines

Me.Index_Date.SetFocus
Exit Sub

are useless.

 

[kujeremy]

Thanks! That works exactly as expected! Very much appreciated

 

[StarGrabber]

You are welcome!

As you noticed, the error was caused by the 'is' in front of the > sign.

With this solution you bind the user to that texbox, that is, he can't leave it before he has entered a correct value. Not very user friendly. In respect to that you should do it as suggested by Pat. Put the code in the Form_BeforeUpdate event. Then the "two lines" are not useless. If there is more validation logic in the routine you'll need the Exit Sub command there.

 

[Pat Hartman]

Sorry about the "is". I must have been talking to myself as I typed.