Date Validation

[Neilbees]

Hello

Another problem that I hope someone can suggest a solution for!

Basically, there are a number of dates entered by the user on a form that need to be within certain parameters based on another date in the form. In the first example the date DateRequestedFromExrTarget needs to be between 8 and 11 months before DateOfExam. I had a look through the forum and came up with this for the beforeupdate event

If Not (DateRequestedFromExrTarget >= DateAdd("m", -11, DateOfExam) And DateRequestedFromExrTarget <= DateAdd("m", -8, DateOfExam)) Then
Msgbox "Date Requested From Examiner Target out of range", vbOKOnly
Cancel = True
End If

This seems to work fine. However, the next validation is for FinalCopyReceivedTarget to be between 6 and 1 months before DateOfExam. I tried this

If Not (FinalCopyReceivedTarget >= DateAdd("m", -6, DateOfExam) And FinalCopyReceivedTarget <= DateAdd("m", -1, DateOfExam)) Then
Msgbox "Final Copy Received Target out of range", vbOKOnly
Cancel = True
End If

But this gives me a syntax error when it compiles the code.

Any ideas?

 

[Zigzag]

Hi Neilbees

Have a look at the datediff function, I think you will find this to be any easier way of dealing with the date issue you have.

If you need any help with this just let me know.

Garry

 

[Neilbees]

Hi Garry

Thanks for your reply!

Doesn't the DateDiff function calculate the difference between 2 dates?

What I want to do is to allow users to only enter dates that fit within parameters that are based upon another date in the form. The parameters are as follows:

Date Requested From Examiner Target - between 8 and 11 months prior to Exam Date
Final Copy Received Target - between 1 and 6 months prior to Exam Date

So if users try to enter dates outside these ranges, they get a message box telling them the date they've entered is not valid.

 

[Zigzag]

Yes the datediff does calculte the difference between two dates.

' dimension monthdif
Dim monthdif as integer

'Calculate the difference in months between the two dates
monthdif = datediff("m", [Exam Date],[Date Requested From Examiner Target] ,0,0)
If monthdif >8 and monthdif <11 then
'Valid
else
'Invalid
End if

This is straight off the top of my head and not tested.

Garry

 

[Dennisk]

Dateadd is the best way forward as it is easier to debug and is less verbose.
I would use Between and And instead of >= / <=

 

[Zigzag]

As this thread has just demonstrated there is more than one way to skin a cat.

Garry