Validations not working in Access 2000

[truthlover]

I have been trying to include some validation (w/ validation text) in my forms but I cant seem to get it to work. I'm currently using a DB in Access 2000 format, but I tried it in 2003 and 2007 with no better results.

I need the date of one field to be equal or less than another date field.

The validation I entered was <=[cboStartDate] and I had corresponding validation text, but when I tested the form it accepted the invalid entry and gave no error message.

I think the problem is because I am using a calendar control to insert the date, because when I manually type the date in, the validation works.

Is there a way to get this rule to work without giving up the calendar control?

Thanks!

[Stewart Ross]

I have been trying to include some validation (w/ validation text) in my forms but I cant seem to get it to work. I'm currently using a DB in Access 2000 format, but I tried it in 2003 and 2007 with no better results.

I need the date of one field to be equal or less than another date field.

The validation I entered was <=[cboStartDate] and I had corresponding validation text, but when I tested the form it accepted the invalid entry and gave no error message.

I think the problem is because I am using a calendar control to insert the date, because when I manually type the date in, the validation works.

Is there a way to get this rule to work without giving up the calendar control?

Thanks!

Hi. If you are testing the value of a combo box which is not bound to an underlying date field the date shown is really a text representation of a date, not a date value. (Dates are actually represented as numeric values internally.) You can get your comparison to work by explicitly converting the combo value to a date using the CDate function:

<=CDate([cboStartDate])

No need to change your calendar control!

-Stewart

[truthlover]

Thanks Stewart,

I just tried it. It didnt work.

I'm not entirely sure, but i think the date field *is* bound to an underlying date field.

Here is the code (there are several controls using the same calendar):

Private Sub cboStartDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
' Note which combo box called the calendar
	Set cboOriginator = cboStartDate
' Unhide the calendar and give it the focus
	ocxCalendar.Visible = True
	ocxCalendar.SetFocus
' Match calendar date to existing date if present or today's date
	If Not IsNull(cboOriginator) Then
		ocxCalendar.Value = cboOriginator.Value
	Else
		ocxCalendar.Value = Date
	End If
End Sub
Private Sub cboReqStartDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
' Note which combo box called the calendar
	Set cboOriginator = cboReqStartDate
' Unhide the calendar and give it the focus
	ocxCalendar.Visible = True
	ocxCalendar.SetFocus
' Match calendar date to existing date if present or today's date
	If Not IsNull(cboOriginator) Then
		ocxCalendar.Value = cboOriginator.Value
	Else
		ocxCalendar.Value = Date
	End If
End Sub
Private Sub cboEndDate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
' Note which combo box called the calendar
	Set cboOriginator = cboEndDate
' Unhide the calendar and give it the focus
	ocxCalendar.Visible = True
	ocxCalendar.SetFocus
' Match calendar date to existing date if present or today's date
	If Not IsNull(cboOriginator) Then
		ocxCalendar.Value = cboOriginator.Value
	Else
		ocxCalendar.Value = Date
	End If
End Sub
Private Sub cboKODate_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
' Note which combo box called the calendar
	Set cboOriginator = cboKODate
' Unhide the calendar and give it the focus
	ocxCalendar.Visible = True
	ocxCalendar.SetFocus
' Match calendar date to existing date if present or today's date
	If Not IsNull(cboOriginator) Then
		ocxCalendar.Value = cboOriginator.Value
	Else
		ocxCalendar.Value = Date
	End If
End Sub
Private Sub cboComplete_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
' Note which combo box called the calendar
	Set cboOriginator = cboComplete
' Unhide the calendar and give it the focus
	ocxCalendar.Visible = True
	ocxCalendar.SetFocus
' Match calendar date to existing date if present or today's date
	If Not IsNull(cboOriginator) Then
		ocxCalendar.Value = cboOriginator.Value
	Else
		ocxCalendar.Value = Date
	End If
End Sub

Hi. If you are testing the value of a combo box which is not bound to an underlying date field the date shown is really a text representation of a date, not a date value. (Dates are actually represented as numeric values internally.) You can get your comparison to work by explicitly converting the combo value to a date using the CDate function:

<=CDate([cboStartDate])

No need to change your calendar control!

-Stewart

[Stewart Ross]

Hi again. Well, at first glance all seems OK with the use of the calendar control. Bound controls already do an implicit type conversion for the field to which they are bound, which is why I mentioned an unbound control in my first reply. Could you paste the SQL of your query in which you are performing the comparison so that I can see what you are comparing in full?

Thanks

Stewart

[truthlover]

I wasnt using a query to do the comparison. I was using the Validation option in the properties of the control.

Hi again. Well, at first glance all seems OK with the use of the calendar control. Bound controls already do an implicit type conversion for the field to which they are bound, which is why I mentioned an unbound control in my first reply. Could you paste the SQL of your query in which you are performing the comparison so that I can see what you are comparing in full?

Thanks

Stewart

[Stewart Ross]

I wasnt using a query to do the comparison. I was using the Validation option in the properties of the control.

Ah, I see. In which case it may after all be some kind of issue between the calendar and the validation rule. Since you want to retain the calendar (and you should, because it's really not the problem) you may be better off taking out your validation rule - which is of limited use anyway - and instead to use some VB code on the After Update event of the date you want to check. This gives you much more flexibility with what you can test. An example of the kind of simple but flexible validation that using VB gives you is listed below.

[code=vb]Dim IsError As Boolean, Result As VbMsgBoxResult, ErrMsg as String
IsError = IsNull(Me![otherctrlname])
If IsError Then
ErrMsg = "Please provide a valid date"
else
IsError = CDate(Me![otherctrlname]) <= Cdate(Me!cboSta rtDate)
If IsError then
ErrMsg = "Date Value cannot be < " & Me!cboStartDate
Endif
End If
If IsError Then
Result = MsgBox(ErrMsg, vbExclamation + vbOKOnly, "Date Error")
End If[/code]The first test is for a null date, and the second for a date less than or equal to your combo value. The null test is not done in your validation rule at present.

I do not know the name of the field in which you currently have the validation rule, so otherctrlname above is just a placeholder.

-Stewart

[truthlover]

I just tested it and it didn't work.

Just to be sure I did it right, I was supposed to put that code under After Update of the control, right?

Here is the code
[CODE=vb]
Private Sub cboKODate_After Update()
Dim IsError As Boolean, Result As VbMsgBoxResult, ErrMsg As String
IsError = IsNull(Me![cboKODate])
If IsError Then
ErrMsg = "Please provide a valid date"
Else
IsError = CDate(Me![cboKODate]) <= CDate(Me!cboSta rtDate)
If IsError Then
ErrMsg = "Date Value cannot be < " & Me!cboStartDate
End If
End If
If IsError Then
Result = MsgBox(ErrMsg, vbExclamation + vbOKOnly, "Date Error")
End If
End Sub
[/CODE]

Did I do something wrong?

Thanks

Ah, I see. In which case it may after all be some kind of issue between the calendar and the validation rule. Since you want to retain the calendar (and you should, because it's really not the problem) you may be better off taking out your validation rule - which is of limited use anyway - and instead to use some VB code on the After Update event of the date you want to check. This gives you much more flexibility with what you can test. An example of the kind of simple but flexible validation that using VB gives you is listed below.

[code=vb]Dim IsError As Boolean, Result As VbMsgBoxResult, ErrMsg as String
IsError = IsNull(Me![otherctrlname])
If IsError Then
ErrMsg = "Please provide a valid date"
else
IsError = CDate(Me![otherctrlname]) <= Cdate(Me!cboSta rtDate)
If IsError then
ErrMsg = "Date Value cannot be < " & Me!cboStartDate
Endif
End If
If IsError Then
Result = MsgBox(ErrMsg, vbExclamation + vbOKOnly, "Date Error")
End If[/code]The first test is for a null date, and the second for a date less than or equal to your combo value. The null test is not done in your validation rule at present.

I do not know the name of the field in which you currently have the validation rule, so otherctrlname above is just a placeholder.

-Stewart

[Stewart Ross]

I just tested it and it didn't work. ...
Did I do something wrong?

You have not done anything wrong, and placing the code in after update is correct. What didn't work - the comparison? The Null test? Or was there a compiler error?

As you might expect, I tested this on a testbed Access 2003 system before posting, using a textbox and a combo box on a test form bound to a date value in a test table and running test values, all of which worked as expected. I did not have a calendar control like yours, though.

It would be helpful to provide example values to let me know what it did (or did not) do. Was a null value trapped? (delete the date in the cboKOfield to test that).

I think it would be worth setting a breakpoint at the first line after the DIM statement and stepping through code to check the values of each control. There is something amiss when explicit date comparisons (which Cdate ensures, and you will see it is used on each side of the comparison) appear to be failing in your database.

-Stewart

[truthlover]

There were no error messages, it just didnt work. And I tried it on a new record to ensure there was no hidden data, but nothing happened. It just accepted the bad dates and went on like nothing was wrong.

I think it would be worth setting a breakpoint at the first line after the DIM statement and stepping through code to check the values of each control.

Unfortunately, my knowledge and/or understanding of VB is next to nothing, so I wouldnt even know where to begin.

I would be willing to send you a copy of the database if you wanted to look at it.

Thanks!

You have not done anything wrong, and placing the code in after update is correct. What didn't work - the comparison? The Null test? Or was there a compiler error?

As you might expect, I tested this on a testbed Access 2003 system before posting, using a textbox and a combo box on a test form bound to a date value in a test table and running test values, all of which worked as expected. I did not have a calendar control like yours, though.

It would be helpful to provide example values to let me know what it did (or did not) do. Was a null value trapped? (delete the date in the cboKOfield to test that).

I think it would be worth setting a breakpoint at the first line after the DIM statement and stepping through code to check the values of each control. There is something amiss when explicit date comparisons (which Cdate ensures, and you will see it is used on each side of the comparison) appear to be failing in your database.

-Stewart

[Stewart Ross]

There were no error messages, it just didnt work. And I tried it on a new record to ensure there was no hidden data, but nothing happened. It just accepted the bad dates and went on like nothing was wrong.

Unfortunately, my knowledge and/or understanding of VB is next to nothing, so I wouldnt even know where to begin.

I would be willing to send you a copy of the database if you wanted to look at it.

Thanks!

Hi. I'll send you a PM shortly with my e-mail address so you can send me a copy of the DB. -Stewart

[Stewart Ross]

Hi again, and thanks for sending me a copy of the DB. The problem was lack of firing of the after update event. The only way to enter a date in your controls is via the OCX calendar (no keyboard entry possible). The OCX calendar sets the date value of the control directly and in doing so no After Update event is triggered, so no validation.

I revised the general return subroutine of the OCX control code to test the name of the originating control which called it. If it was the one where validation is required it would return the value then call the after update event to validate the entry.

-Stewart

[truthlover]

Hi Stewart,

I did recieve the revised files you sent.

It didn’t occur to me that the Start Date would need to be required in order for the validation to work. That field cannot be required because the person filling out the form may not know that date.

The Kickoff Meeting date, however, is required. If I just reverse the names of the controls, and change <= to >= , will allow that me to have it validate in reverse (start date cannot be sooner than KO date)?

Thanks

Hi again, and thanks for sending me a copy of the DB. The problem was lack of firing of the after update event. The only way to enter a date in your controls is via the OCX calendar (no keyboard entry possible). The OCX calendar sets the date value of the control directly and in doing so no After Update event is triggered, so no validation.

I revised the general return subroutine of the OCX control code to test the name of the originating control which called it. If it was the one where validation is required it would return the value then call the after update event to validate the entry.

-Stewart

[Stewart Ross]

Hi Stewart,

I did recieve the revised files you sent.

It didn’t occur to me that the Start Date would need to be required in order for the validation to work. That field cannot be required because the person filling out the form may not know that date.

The Kickoff Meeting date, however, is required. If I just reverse the names of the controls, and change <= to >= , will allow that me to have it validate in reverse (start date cannot be sooner than KO date)?

Thanks

Hi again. All of this should be fine - but you need to change the name of the control in the IF statement I added to the OCX calendar, which is currently referring to the cboKODate, to refer to the start date instead.

-Stewart

[truthlover]

I must be doing something wrong because the code you sent back to me didnt work either.

But dont even worry about it. The whole issue of how the dates are picked (in what order and by whom) ends up making circular references that make it too hard to try to validate.

Thanks for your help though, i'm sure I'll be able to use this in other applications.

Hi again. All of this should be fine - but you need to change the name of the control in the IF statement I added to the OCX calendar, which is currently referring to the cboKODate, to refer to the start date instead.

-Stewart