Date validation problem

[Malcy]

Hi
I popped a bit of code in the AfterUpdate event of the textbox to try to ensure that the date entered was sort of valid i.e. between two weeks ago and four weeks hence.
It seems to say everything is out of range so far as I can see
The code is

' Test to see that date is within reasonable parameters
    If Me.txtDate < Date - 14 Or Me.txtDate > Date + 28 Then
        MsgBox "The date is outwith the set limits", vbOKOnly, "MicaPharm Data Validation"
        Me.txtDate.SetFocus
        Exit Sub
    End If

I fully accept it may be my bleary post New Year eyes are missing something very obvious or it may be that I have to use # around date construct.
Can anyone please spot the error?
Many thanks in anticipation
Hope you all had a good New Year

 

[llkhoutx]

Try the following:

If cdate(Me.txtDate) < Date - 14 Or cdate(Me.txtDate) > Date + 28 Then

 

[Ian Mac]

Hi,

You need AND instead of OR.

You may also want to use >= And <=
and you might want to use ! instead of '.'

If Me!txtDate <= Date - 14 And Me!txtDate => Date + 28 Then

 

[Rich]

You should also use the BeforeUpdate event and not the AfterUpdate, if you use Cancel = True you don't need to use the SetFocus method

 

[Malcy]

Thanks guys
I tried the suggestion from Ilkhoutx first and it worked fine so don't think I need to develop along the route Ian suggested.
Just spotted the suggestion from Rich and can see where he is coming from - why don't I only ever drink water!
Thanks again

 

[llkhoutx]

Ian Mac -

Look at the code more closely. AND is not appropriate. OR is what's needed.

 

[Ian Mac]

Ian Mac -

Look at the code more closely. AND is not appropriate. OR is what's needed.

You are absolutely wrong!

You answer you own question in the first post:

i.e. between two weeks ago and four weeks hence.

If I put in the date February 7th 2005 your code will think it's valid.

i.e. it is > date - 14

You need to say >date-14 AND <date-28 otherwise ALL dates entered will fine because any date will satisfy one of your Or arguments.