Date code

[Dean]

I have a form i need to add code to but am not real
familer with VB. What I have is a db for that lets
employees sign up for vacation. The form allows them to
sign up for days and also delete days. We recently
decided that we do not want them to delete vacation days 2
weeks or less before the first day of vac. What I have so
far is
If strLOCK_IN_FLAG = "Y" Then
Call check_LockIn
EndIf

then:



Private Sub check_LockIn()
Dim NoChange As String
Dim dtmREQUEST_DATE As Date
NoChange = Date - 14
If dtmREQUEST_DATE <= NoChange Then
MsgBox _
"You cannot delete vacation days that are
locked in this close to the first day of vaction." &
vbCrLf & vbCrLf & _
"Please contact your supervisor for help with
this."
Else
End If
End Sub

i think my date formula is wrong, it gives the above
message for all deletion trys.

Any suggestions/ideas/code would be appreciated


Thanks
Dean

 

[Brad McCulloch]

Hi Dean,

You'd be better using something like:

If strLOCK_IN_FLAG = "Y" Then
If dtmREQUEST_DATE -date() <= 14 Then
call MsgBox ("You cannot delete vacation days that
are locked in this close to the first day of vaction." &
vbCrLf & vbCrLf & " Please contact your supervisor for
help with this.")
Else
'action the change.
endif
else
'do what ever you do if lock_in flag is not Y
endif

 

[Gary Miller]

Dean,

I see a couple of things here. One is that you are setting
the NoChange date to be a string instead of a date. This
will fail now when you try to compare it to your RequestDate
date field. Kind of like adding 'one + 2'. Secondly, you
haven't given dtmREQUESTDATE a value. I think that your
intention is to have it be the date Start Vac Date on your
form, but it isn't assigned that anywhere. As you have your
'check' code in seperate sub, you will need to pass it the
date that you want to check from the form code calling the
routine. This will assume that you form date control is
called StartDate. Change that to whatever it is on your
form. Also, don't you mean today + 14 days?? Here is a new
version of yours, but I will give you even another
alternative below...

If strLOCK_IN_FLAG = "Y" Then
Call check_LockIn(Me!StartDate) '** New
Variation
EndIf

then:

Private Sub check_LockIn(dtmStartDate as Date)
If dtmStartDate <= Date + 14 Then
MsgBox _
"You cannot delete vacation days that are
locked in this close to the first day of vaction." &
vbCrLf & vbCrLf & _
"Please contact your supervisor for help with
this."
Else
' Do Whatever You Need Here
End If
End Sub

The basic thing that you are trying to do is find out if it
is true or false whether it is 14 days out, another way is
to create a Function that tells you that. Here is another
alternative that would keep your date checking more generic
so you can use it for more than one thing. You could have it
Private in the Form or Public in a Global Module where you
can get at it from other places. I will make it Public...


Public Function LessThan14(dtmStartDate as Date) As Boolean
If dtmStartDate <= Date + 14 Then
LessThan14 = True
Else
LessThan14 = False
End If
End Function

Now call it like this from wherever you trigger event is...

If strLOCK_IN_FLAG = "Y" Then
If LessThan14(Me!StartDate) = True Then
MsgBox _
"You cannot delete vacation days that are
locked in this close to the first day of
vaction." &
vbCrLf & vbCrLf & _
"Please contact your supervisor for help
with this."
Else
' Do Whatever Here...
End If
End If

EndIf


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________

 

[dean]

thanks

-----Original Message-----
Hi Dean,

You'd be better using something like:

If strLOCK_IN_FLAG = "Y" Then
If dtmREQUEST_DATE -date() <= 14 Then
call MsgBox ("You cannot delete vacation days that
are locked in this close to the first day of vaction." &
vbCrLf & vbCrLf & " Please contact your supervisor for
help with this.")
Else
'action the change.
endif
else
'do what ever you do if lock_in flag is not Y
endif



.

 

[dean]

thanks

-----Original Message-----
Dean,

I see a couple of things here. One is that you are setting
the NoChange date to be a string instead of a date. This
will fail now when you try to compare it to your RequestDate
date field. Kind of like adding 'one + 2'. Secondly, you
haven't given dtmREQUESTDATE a value. I think that your
intention is to have it be the date Start Vac Date on your
form, but it isn't assigned that anywhere. As you have your
'check' code in seperate sub, you will need to pass it the
date that you want to check from the form code calling the
routine. This will assume that you form date control is
called StartDate. Change that to whatever it is on your
form. Also, don't you mean today + 14 days?? Here is a new
version of yours, but I will give you even another
alternative below...

If strLOCK_IN_FLAG = "Y" Then
Call check_LockIn(Me!StartDate) '** New
Variation
EndIf

then:

Private Sub check_LockIn(dtmStartDate as Date)
If dtmStartDate <= Date + 14 Then
MsgBox _
"You cannot delete vacation days that are
locked in this close to the first day of vaction." &
vbCrLf & vbCrLf & _
"Please contact your supervisor for help with
this."
Else
' Do Whatever You Need Here
End If
End Sub

The basic thing that you are trying to do is find out if it
is true or false whether it is 14 days out, another way is
to create a Function that tells you that. Here is another
alternative that would keep your date checking more generic
so you can use it for more than one thing. You could have it
Private in the Form or Public in a Global Module where you
can get at it from other places. I will make it Public...


Public Function LessThan14(dtmStartDate as Date) As Boolean
If dtmStartDate <= Date + 14 Then
LessThan14 = True
Else
LessThan14 = False
End If
End Function

Now call it like this from wherever you trigger event is...

If strLOCK_IN_FLAG = "Y" Then
If LessThan14(Me!StartDate) = True Then
MsgBox _
"You cannot delete vacation days that are
locked in this close to the first day of
vaction." &
vbCrLf & vbCrLf & _
"Please contact your supervisor for help
with this."
Else
' Do Whatever Here...
End If
End If

EndIf


--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________



.