Date Problem Help Needed, Please

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")

On Tue, 26 Oct 2004 18:15:20 GMT, "Dave Elliott"
wrote:

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.

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")

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.

"John Vinson" wrote in message
...
On Tue, 26 Oct 2004 18:15:20 GMT, "Dave Elliott"
wrote:

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.

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")

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

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



"John Vinson" wrote in message
...
On Tue, 26 Oct 2004 18:15:20 GMT, "Dave Elliott"
wrote:

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.

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")

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

On Wed, 27 Oct 2004 03:05:11 GMT, "Dave Elliott"
wrote:

I created (2) text boxes that may help explain what I am trying to do.
one is named Text104 which shows the week starting

Dave, did you try what I suggested?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

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


"John Vinson" wrote in message
news
On Wed, 27 Oct 2004 03:05:11 GMT, "Dave Elliott"
wrote:

I created (2) text boxes that may help explain what I am trying to do.
one is named Text104 which shows the week starting

Dave, did you try what I suggested?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

On Wed, 27 Oct 2004 12:30:57 GMT, "Dave Elliott"
wrote:

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

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 Vinson" wrote in message
...
On Wed, 27 Oct 2004 12:30:57 GMT, "Dave Elliott"
wrote:

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

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

On Wed, 27 Oct 2004 17:43:20 GMT, "Dave Elliott"
wrote:

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?

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

"John Vinson" wrote in message
...
On Wed, 27 Oct 2004 17:43:20 GMT, "Dave Elliott"
wrote:

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?

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