Restrict Data Entry for Dates

[Doctor Kronenbo]

I have a HUGE system which presently restricts updating a "Final Action" date to today's date only. However, I want to change it so users can input dates for only the present working week. The present coding is as follows...

Private Sub dteActioned_AfterUpdate()
Dim strTime As String

strTime = "07:30"

gintChanged = gintChanged + 1

If Not IsNull(Me![dteActioned]) Then
If Me![dteActioned] > Now Then
gintReply = MsgBox("Your actioned date is later than today !!", vbOKOnly + vbCritical, "Oops")
Me![dteActioned] = Null
Me![dteActioned].SetFocus
GoTo Exit_dteActioned_AfterUpdate:
Else
If Format(Me![dteActioned], "dd/mm/yyyy") < Format(Date, "dd/mm/yyyy") Then ' final action earlier than today
If Weekday(Now) = 2 And Format(Now, "hh:mm") > strTime Then
' If Weekday(Now) = 6 And Format(Now, "hh:mm") > strTime Then
' Not acceptable to enter a previous date
' older than 7 days is not acceptable
gintReply = MsgBox("Now too late to accept this 'Final Action' date !", vbOKOnly + vbCritical, "Oops")
Me![dteActioned] = Null
Me![dteActioned].SetFocus
GoTo Exit_dteActioned_AfterUpdate:
Else
' Previous date is acceptable but only as far back as 7 days
If CVDate(Me![dteActioned]) < CVDate(Now) Then
' older than 7 days is not acceptable
gintReply = MsgBox("Now too late to accept this 'Final Action' date !", vbOKOnly + vbCritical, "Oops")
Me![dteActioned] = Null
Me![dteActioned].SetFocus
GoTo Exit_dteActioned_AfterUpdate:
End If
End If
Else
' Actioned date is acceptable since it is today's date
DoEvents
End If
End If
End If
' if today is a Monday Only accept final action dates which fall within the previous week if today

Exit_dteActioned_AfterUpdate:

End Sub

Probably very simple but I just can't get it to work. Help please! Time is short.

 

[Pat Hartman]

Several problems:
1. Now() should not be used in place of Date() when all you want is the current date. Now() also includes time of day and will cause problems when used as criteria.
2. When you format a date it becomes a string. If you must for some reason format dates to compare them, they MUST be formated in year, month, day order. Otherwise you will not get the results you anticipate. For example 02/01/03 will be GREATER than 01/01/04.
3. Me![dteActioned] = Null -- you can't compare for nulls this way. The answer will ALWAYS be false regardless of the contents of dteActioned. You MUST use either IsNull(dteActioned) or since this is a date field you can use IsDate(dteActioned)
4. Your Me! references should be Me. so you will get intellisense help.

I would use the week of the year for the compare.

If Format(Date(), "yyyy/ww") = Format(dteActioned, "yyyy/ww") Then

This makes sure that the date entered is in the same calendar week as the current date. If that works for you, great!

 

[Doctor Kronenbo]

Thanks very much for your reply. Much appreciated.
I've incorporated your suggestion into my coding as best I can and it blocks any entry belonging to last week. However, if you enter a date belong to last month it accepts it. Is there anyway around this?

 

[Pat Hartman]

The code I suggested is comparing two week numbers for equality. There is no way for a day from last month to be part of this week since we are more than 7 days into the month. Post your SQL.

 

[Doctor Kronenbo]

Apologies - should have explained I had to adapt the function you suggested to fit my coding the best I could (as below). When I entered dates on Friday for the previous week it is blocked but for some reason accepts 31st January and before...

Private Sub dteActioned_AfterUpdate()
Dim strTime As String

strTime = "07:30"

gintChanged = gintChanged + 1

If Not IsNull(Me![dteActioned]) Then
If Me![dteActioned] > Now Then
gintReply = MsgBox("Your actioned date is later than today !!", vbOKOnly + vbCritical, "Error")
Me![dteActioned] = Null
Me![dteActioned].SetFocus
GoTo Exit_dteActioned_AfterUpdate:
Else
If Format(Me![dteActioned], "dd/mm/yyyy") < Format(Date, "dd/mm/yyyy") Then ' final action earlier than today
If Weekday(Now) = 2 And Format(Now, "hh:mm") > strTime Then
' If Weekday(Now) = 6 And Format(Now, "hh:mm") > strTime Then
' Not acceptable to enter a previous date
' older than 7 days is not acceptable
gintReply = MsgBox("Now too late to accept this 'Final Action' date !", vbOKOnly + vbCritical, "Error")
Me![dteActioned] = Null
Me![dteActioned].SetFocus
GoTo Exit_dteActioned_AfterUpdate:
Else
' Previous date is acceptable but only as far back as present calendar week
If Format([dteActioned], "yyyy/ww") < Format(Date, "yyyy/ww") Then
' older than present calendar week is not acceptable
gintReply = MsgBox("Now too late to accept this 'Final Action' date !", vbOKOnly + vbCritical, "Error")
Me![dteActioned] = Null
Me![dteActioned].SetFocus
GoTo Exit_dteActioned_AfterUpdate:
End If
End If
Else
' Actioned date is acceptable since it is today's date
DoEvents
End If
End If
End If
' if today is a Monday Only accept final action dates which fall within the previous week if today

Exit_dteActioned_AfterUpdate:

End Sub

 

[Doctor Kronenbo]

Got it working! 2nd ELSE Statement was interfering with the coding. The following works...
Only problem is my manager now wants a Sunday to belong to the previous week for clearance purposes!! (ie if staff are in on a Sunday they can use Friday as a clearance date). I could scream...
Anyway, thanks for your help.

Private Sub dteActioned_AfterUpdate()
Dim strTime As String

strTime = "07:30"

gintChanged = gintChanged + 1

If Not IsNull(Me![dteActioned]) Then
If Me![dteActioned] > Now Then
gintReply = MsgBox("Your actioned date is later than today !!", vbOKOnly + vbCritical, "Error")
Me![dteActioned] = Null
Me![dteActioned].SetFocus
GoTo Exit_dteActioned_AfterUpdate:
Else
' Previous date is acceptable but only as far back as present week
If Format([dteActioned], "yyyy/ww") < Format(Date, "yyyy/ww") Then
' older than present week is not acceptable
gintReply = MsgBox("Now too late to accept this 'Final Action' date !", vbOKOnly + vbCritical, "Error")
Me![dteActioned] = Null
Me![dteActioned].SetFocus
GoTo Exit_dteActioned_AfterUpdate:
End If
End If

' Actioned date is acceptable since it is today's date
DoEvents
End If
' if today is a Monday Only accept final action dates which fall within the previous week if today

Exit_dteActioned_AfterUpdate:

End Sub

 

[Pat Hartman]

You apparently didn't take any of my earlier advice so I'm not sure why I'm bothering.

To change the day a week starts on, you'll need to use the DatePart() function to extract the year and week. You will need to do them separately and concatenate them. Don't forget to also Format() them since DatePart() returns an integer so there won't be any leading zeros.

Help for functions is found by asking from the VB window rather than the database container window.

 

[Doctor Kronenbo]

You apparently didn't take any of my earlier advice so I'm not sure why I'm bothering.

Frankly I'm not sure why you have made this comment. I did take your advice and was emmensely grateful for it. However, I have only been using access and VB for approx 5 months and while I have learned a lot in that time my knowledge is still extremely limited (I did not create this system) and I cannot always immediately understand procedures new to me so have a care please. Re your previous points...

1) As you can see I replaced Now() with Date() as you suggested.
2) This helped me understand the problem.
3) I am not comparing Nulls here. Simply reseting the field to blank when a user block message appears.
4) I did change Me! to Me. but found it little help. After experiencing an error I pasted an old version of the code back in to start from scratch and found little sense in changing all the Me!s again.

Plus I found the function you suggested "If Format(Date(), "yyyy/ww") = Format(dteActioned, "yyyy/ww") Then" to be extremely helpful. So again thank you for your help but if you find new programmers so exasperating please don't bother.

 

[Pat Hartman]

Sorry to have been rude but you didn't change all instances of Now() to Date() and you are still using Me! which is less efficient and less helpful than Me.

You are comparing:
Me![dteActioned] > Now <---- this should be Date
and later,
If Format([dteActioned], "yyyy/ww") < Format(Date, "yyyy/ww")
but not handling = date. One of the two conditions should be changed to handle the = condition or you need to handle it with an else.

In the future when you post code use the # button so that the code retains its indentation (assuming you properly indent your code when you write it). It makes the code much easier to read.

 

[Doctor Kronenbo]

Thanks. I will use Me. in the future but as the system is rather large and was written by another programmer I think I'll change them as I come across them...
I have changed the remaining Now to Date.

My supervisors have changed their minds again and want the weekly block now removed and replaced with a monthly block. This seems to work perfectly by replacing "ww" with "m".

When you mention the = date handling do you mean I have to write a condition if the date entered is today's date? In this system the current date is acceptable. Please explain in case I'm being thick.

Yeah I do indent my code. It's just the paste function that removed it. Apologies.