How to prevent "backdating" using a date interval query?

[Tim Mullin]

Hello All,

First off I don't know if "backdating " is the correct term for this issue, but I'm unsure of what else to call it.

I have a form that has two fields (we'll call them A & B) that provide parameters for a query, both of which are formatted as General Dates. They are used to select an interval of dates to find which contracts end between those two dates.

The query works perfectly at the moment and returns the data that it should, but there is a minor loophole I would like to close up before launching the database company wide - if the user selects a second date(field B) that happens to be before the first date(field A) results still come back...

Is there any way to have an error message appear if the user selects a date in Field B that occurs before Field A? I would imagine there is but due to my amateur Access knowledge I can't seem to figure it out.

Any help would be greatly appreciated! Thanks!

[NeoPa]

In the Validation Rule property for control (not field) A use <=[B].
In the Validation Rule property for control B use >=[A].

[Tim Mullin]

I entered the code as the Validation Rule for the start date and when I tried to run the query it said that the value I entered did not meet the validation rules for that field/control. I'm assuming that because it tries to validate that the start date is before the end date when there is no end date entered yet it kicks out the error.

I could possibly be putting the rule in the wrong place, you mention control A & B, but how is that different than the field properties? Where do I find the control validation rule if it is different?

Thanks again for the help!

[Tim Mullin]

I was able to figure it out! I did not enter the validation rule for Control A and just set Control B using your suggested code and it works perfectly now!

Do you see any potential problems not have control A to be set to

<=[B]

?

[NeoPa]

Good question Tim.

First let me clarify. The Validation Rule can certainly be set for the field rather than the control. The item on the form however, is not a field, but a control. If you want to go to the table design and change things there, then that would even be preferable, on the probably reliable assumption that this relationship is required universally for the data in the table (It is conceivable however, that data in a form can have different requirements from those in the table itself. Rare but can happen).

It is also usual for controls to be named differently from the fields they are used to represent. For instance, a TextBox control bound to a field named [Description] is usually named [txtDescription] or similar.

In answer to your question Do you see any potential problems not have control A to be set to <=[B], I would say yes. I'm afraid I do. If ever the [B] date is entered ok, but then the [A] date is updated afterwards, the Validation Rule would not apply, and it would be possible to enter an invalid date. Not a very likely scenario I accept, but possible. Let me suggest an alternative response for you, which deals with this situation more fully :

In the Validation Rule property for field A use <=Nz([B],#12/31/9999#).
In the Validation Rule property for field B use >=Nz([A],#1/1/1900#).