Date Problem Help Needed, Please

  • Thread starter Thread starter Dave Elliott
  • Start date Start date
D

Dave Elliott

I have a form where I enter an employees payroll time with the date criteria
for there payroll week.
The payroll week runs Thursday Thru Wednesday. See Code Below. On the form
there is a control
named WorkDate with it's control set to Work Date and this is the date
between the beginning and ending date
that calculates when they get paid, i.e. if there time is entered between
the two dates, they will get paid.
The Text Box where this code below is located is named Text999
If the date entered is not between these two dates, (Work Date control) or
name WorkDate
I need a message box to appear telling me so.
How can I do this?



=Format(CDate(Date())-(Weekday(CDate(Date())))-2,"m/d/yy") & "-" &
Format(CDate(Date())-(Weekday(CDate(Date())))+4,"m/d/yy")
 
I have a form where I enter an employees payroll time with the date criteria
for there payroll week.
The payroll week runs Thursday Thru Wednesday. See Code Below. On the form
there is a control
named WorkDate with it's control set to Work Date and this is the date
between the beginning and ending date
that calculates when they get paid, i.e. if there time is entered between
the two dates, they will get paid.
Click to expand...

I'm confused. Is the data in WorkDate a date value, or a number of
days, or what?
The Text Box where this code below is located is named Text999
If the date entered is not between these two dates, (Work Date control) or
name WorkDate
I need a message box to appear telling me so.
How can I do this?



=Format(CDate(Date())-(Weekday(CDate(Date())))-2,"m/d/yy") & "-" &
Format(CDate(Date())-(Weekday(CDate(Date())))+4,"m/d/yy")
Click to expand...

This can be simplified considerably. For one thing, you don't need to
use CDate() to convert Date() to a date - it already IS a date. For
another, you can use the optional third argument of Weekday. To get a
*text string* (which is not a date and is not a range, it's just a
human readable string) try

DateAdd("d", -WeekDay(Date(), vbThursday), Date()) & "-" &
DateAdd("d", 8-WeekDay(Date(), vbThursday), Date())

You might need to wrap the DateAdd() calls in Format.

To validate the WorkDate control, try code like this in the control's
BeforeUpdate event:

Private Sub WorkDate_BeforeUpdate(Cancel as Intager)
If CDate(Me!WorkDate) < DateAdd("d", -Weekday(Date(), vbThursday), _
Date()) OR _
CDate(Me!WorkDate) > DateAdd("d", 8-Weekday(Date(), vbThursday), _
Date()) Then
Cancel = True
MsgBox "Enter a date within the work week", vbOKOnly
End If
End Sub

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
The data in the WorkDate is a date in m/d/yy format with a default of
Date()
Example; The current work week is 10/21/04 thru 10/27/04
Any date outside these parameters should not work.
 
I created (2) text boxes that may help explain what I am trying to do.
one is named Text104 which shows the week starting
date:=Format(CDate(Date())-(Weekday(CDate(Date())))-2,"m/d/yy")
the other is named Text106 which shows the week end
date:=Format(CDate(Date())-(Weekday(CDate(Date())))+4,"m/d/yy")
I then tried this code in the beforeupdate event of WorkDate;
Of course it didnt work. But this is what I am after.

If ([Text104]) < Date Or ([Text106]) > Date Then
Cancel = True
MsgBox "Enter a date within the work week", vbOKOnly
End If
 
Yes, it wouldnt let me put in the DateAdd() for the date and so code did not
work. To be sure I understand, I tried this

If CDate(Me!WorkDate) < DateAdd()("d", -Weekday(Date(), vbThursday), _
Date()) OR _
CDate(Me!WorkDate) > DateAdd()("d", 8-Weekday(Date(), vbThursday), _
Date()) Then
Cancel = True
MsgBox "Enter a date within the work week", vbOKOnly
End If
 
Yes, it wouldnt let me put in the DateAdd() for the date and so code did not
work. To be sure I understand, I tried this

If CDate(Me!WorkDate) < DateAdd()("d", -Weekday(Date(), vbThursday), _
Date()) OR _
CDate(Me!WorkDate) > DateAdd()("d", 8-Weekday(Date(), vbThursday), _
Date()) Then
Cancel = True
MsgBox "Enter a date within the work week", vbOKOnly
End If
Click to expand...

That's not what I posted. You're putting in DateAdd() - which will
fail, because you're passing it zero arguments and DateAdd needs
three; and then you have the arguments in parentheses afterwards.

Try

If CDate(Me!WorkDate) < DateAdd("d", -Weekday(Date(), vbThursday), _
Date()) OR _
CDate(Me!WorkDate) > DateAdd("d", 8-Weekday(Date(), vbThursday), _
Date()) Then
Cancel = True
MsgBox "Enter a date within the work week", vbOKOnly
End If


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
John, tried your code, but nothing happened, I could enter a future date or
a past date outside the present date week with no problems.
Any More Suggestions?
 
John, tried your code, but nothing happened, I could enter a future date or
a past date outside the present date week with no problems.
Any More Suggestions?
Click to expand...

Please post your actual code. Other than using the debugger to step
through the code line by line, checking the values of the various
dates, I don't know what else to suggest!

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
The database is quite complicated, The code below should check for dates
between
(CDate(Date())-(Weekday(CDate(Date())))-2,"m/d/yy") 'This weeks Thursday
(CDate(Date())-(Weekday(CDate(Date())))+4,"m/d/yy") 'This weeks Wednesday
Anyway, I thank you for your help. Actual code is below.

If CDate(Me!WorkDate) < DateAdd("d", -Weekday(Date, vbThursday), Date) _
Or CDate(Me! _
WorkDate) > DateAdd("d", 8 - Weekday(Date, vbThursday), Date) Then _

Cancel = True
MsgBox "Enter a date within the work week", vbOKOnly
End If
 
The database is quite complicated, The code below should check for dates
between
(CDate(Date())-(Weekday(CDate(Date())))-2,"m/d/yy") 'This weeks Thursday
(CDate(Date())-(Weekday(CDate(Date())))+4,"m/d/yy") 'This weeks Wednesday
Anyway, I thank you for your help. Actual code is below.

If CDate(Me!WorkDate) < DateAdd("d", -Weekday(Date, vbThursday), Date) _
Or CDate(Me! _
WorkDate) > DateAdd("d", 8 - Weekday(Date, vbThursday), Date) Then _

Cancel = True
MsgBox "Enter a date within the work week", vbOKOnly
End If
Click to expand...

Looks good to me. Where is this code? What form event? What happens
when you step through it line by line with the debugger? What are the
values of Me!Workdate at the time?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
This code is on the before update event of the control Work Date. Values of
WorkDate are between Thursday and Wednesday of the current week.
i.e. 10/21 thru 10/27. Dont understand the debug process. When I enter a
date, any date in the workdate field, nothing happens!
 
This code is on the before update event of the control Work Date. Values of
WorkDate are between Thursday and Wednesday of the current week.
i.e. 10/21 thru 10/27. Dont understand the debug process. When I enter a
date, any date in the workdate field, nothing happens!
Click to expand...

Put a breakpoint on some line (the first line after any Dim statement)
by clicking your mouse in the grey bar along the left edge of the VBA
code window. A brown circle will mark the breakpoint.

Enter a date (valid or invalid) in the Form; the BeforeUpdate event
should fire, and you'll be thrown into the VBA editor in debug mode.
Use the Debug menu option to get the hotkeys - F8 will step one line
at a time; use the Immediate window to ascertain the values.

Hmmm... one possible concern... do you have any field or control
*NAMED* Date? It's conceivable that Access is getting it confused with
the Date() function.

John W. Vinson

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
The name of the TxetBox is WorkDate and the control source is Work Date, is
this a problem?
 
Back
Top