Date Difference Code Help Needed!

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

Dave Elliott

Both Text108 and Text106 are in format m/d/yy
Text108 control source is another date field.
The code below is on the beforeupdate event of a date field (Work Date) as
well which checks for the code below.
It does not work,why?
Text108 equals a control named Work Date which is a date field; m/d/yy
Text106 has it's control source set to
=Format(CDate(Date())-(Weekday(CDate(Date())))-2,"m/d/yy")


If (Text108) > (Text106) Then
Cancel = True
MsgBox "Enter a work date within the work week"
End If
 
Ok a couple of questions and comments
- why Cdate(date()) ? Date() is a date function that returns a date,
you don't need CDate()
- Why are you needing the Weekday() function?
- I think you'll find that if you're in the US with US regional
settings that you don't need the Format() either because the default
date display is mm/dd/yy or in text, the function recognises "Short
Date"

So at the moment, Text108 = Today minus 2 days ago ... is this right?

From your question though, it sounds like you just wanted the value
between two dates (although this wasn't entirely clear)

To know if your code is going to work, I guess it would help if we
knew what you want to do and where in your forms you are currently
getting the WorkDate.

Brett

Both Text108 and Text106 are in format m/d/yy
Text108 control source is another date field.
The code below is on the beforeupdate event of a date field (Work Date) as
well which checks for the code below.
It does not work,why?
Text108 equals a control named Work Date which is a date field; m/d/yy
Text106 has it's control source set to
=Format(CDate(Date())-(Weekday(CDate(Date())))-2,"m/d/yy")


If (Text108) > (Text106) Then
Cancel = True
MsgBox "Enter a work date within the work week"
End If
Click to expand...

Cheers,
Brett
 
The code returns the date of the current week being a Thursday, hence the
need for the Cdate and minus 2
If the date enetered in the control Work Date is a date beyond;
Wednesday of the current week then a message and cancel = True. (Format
m/d/yy) for control Work Date
The code is located in a sub-form on my main form
And the code neeeds to be triggered on the before update event.
This is a payroll module and uses the current weeks dates to calculate,
current week is
Start of week Thursday and end of week Wednesday, i.e. Between 10/28/04
and 11/03/04
 
Back
Top