Date Problems (1 Viewer)

[Groundrush]

Hi all,

I'm trying to prevent records being entered outside a time period & I'm struggling to work out the best method of doing so

I've been trying

If Me.txtStartDate <= #3/13/2010# Or Me.txtStartDate >= #3/12/2011# Then
MsgBox "This date may be incorrect please check your records & try again"
Me.txtStartDate = ""
Me.txtStartedHidden = ""
End if

This does seem to work but I need to be more flixable & allow 2 weeks either side of the two dates but not sure on the correct way of coding it.

I need something like

 If me.txtStartDate <= now() -14 or Me.txtStartDate >=Now() +14 then
MsgBox "This date may be incorrect please check your records & try again"
Me.txtStartDate = ""
Me.txtStartedHidden = ""
End if

Can someone please show me the correct way?

Thanks

 

[Brianwarnock]

Doesn't your "something like" work?
Personally I would use Datadd instead of the + - and also Date() instead of Now() but that's personal preference as for as the concept.

Brian

I wonder if you need ( )

 

[vbaInet]

I would be inclined to Format() the value of your textbox too.

 

[Groundrush]

Hi,

The following code now works after formating the date field to short date

Private Sub txtStartDate_AfterUpdate()
If Me.txtStartDate <= Date - 14 Or Me.txtStartDate >= Date + 14 Then
MsgBox "This date may be incorrect please check your records & try again"
Me.txtStartDate = ""
Me.txtStartedHidden = ""
End If
End Sub

But I think that using the date add method you suggested would be better, if I can figure out what I'm doing wrong when i try this.

If Me.txtStartDate Between DateAdd("ww", -2, Date()) And DateAdd("ww", 2, Date())

I get a complile Error: "Expected then or goto" & can't work out where the problem is.

Anyone know where I'm going wrong?


Thanks

 

[Brianwarnock]

Well the bit of code posted as an If but no Then

Brian

 

[Groundrush]

Well the bit of code posted as an If but no Then

Brian

Yes, I tried to add "then" to the end of the line but it errors out

Must be something wrong with "Between" as that is highlighted in red

If Me.txtStartDate Between DateAdd("ww", -2, Date()) And DateAdd("ww", 2, Date())

 

[Brianwarnock]

Perhaps you cant use Between... And in VBA. Never have but thought you could,

Brian

 

[Groundrush]

Perhaps you cant use Between... And in VBA. Never have but thought you could,

Brian

Mmm, I found that solution from another post somewhere & perhaps was based on a query.

Oh, well.

My other method seems to work & happy to use it if noone else says otherwise


Thanks Brian

 

[DCrake]

You cannot use the following logic

If x Between y And z Then

You need

If X <= y Or x  >= z Then

Other than that

Select Case ActualDate
     Case < DateAdd("d",-14,Date())
         Too Early
     Case > DateAdd("d",14,Date())
         Too Late
     Case Else
         Within Range
End Select

 

[Groundrush]

Select Case ActualDate
     Case < DateAdd("d",-14,Date())
         Too Early
     Case > DateAdd("d",14,Date())
         Too Late
     Case Else
         Within Range
End Select

Hi DCrake,

Where do you put your code so I can try it out?

 

[Groundrush]

If X <= y Or x  >= z Then

That's the method I can only seem to get to work

If Me.txtStartDate <= Date - 7 Or Me.txtStartDate >= Date + 7 Then
MsgBox "This date may be incorrect please check your records & try again"
Me.txtStartDate = ""
Me.txtStartedHidden = ""

End If

 

[DCrake]

Right I create a little function

Function TestDate(ActualDate As Date) As Integer

Select Case ActualDate
     Case Is < DateAdd("d", -14, Date)
        TestDate = 1
     Case Is > DateAdd("d", 14, Date)
         TestDate = 2
     Case Else
         TestDate = 0
End Select



End Function

Then on the AfterUpdate of the textbox that holds the date

If TestDate(Me.txtStartDate) > 0 Then
         MsgBox "This date may be incorrect please check your records & try again"
         Me.txtStartDate = ""
         Me.txtStartedHidden = ""
End If

 

[Groundrush]

Right I create a little function

Function TestDate(ActualDate As Date) As Integer

Select Case ActualDate
     Case Is < DateAdd("d", -14, Date)
        TestDate = 1
     Case Is > DateAdd("d", 14, Date)
         TestDate = 2
     Case Else
         TestDate = 0
End Select



End Function

Then on the AfterUpdate of the textbox that holds the date

If TestDate(Me.txtStartDate) > 0 Then
         MsgBox "This date may be incorrect please check your records & try again"
         Me.txtStartDate = ""
         Me.txtStartedHidden = ""
End If

Thank You, I understand now

 

[Brianwarnock]

Surely if you are going to the trouble of the Select Case then the message output should be as in the earlier example pointing the user as to too early or too late.
Can't it all be done in the afterupdate event?

Brian