Custom validation on a date field won't work

[brett s]

I have an asset register application with an ASSET table that includes two fields: a DATEACQUIRED (the date when the asset was acquired) and a DATESOLD (the date when it was sold, unless the asset is still owned then this date will be nil).

In the ASSET table, I want a field rule for DATESOLD to make sure it is not entered with a value less than DATEACQUIRED. But a nil date is OK indicating the asset is still owned.

So my custom validation for DATESOLD looks like this:

Validation: DATESOLD"{01/01/0001} .OR. .NOT.(BETWEEN(DATESOLD,{01/01/0001},DATEACQUIRED))
Error message: Date sold must not be prior to date acquired

My problem is that whatever date I enter for DATESOLD I always get the error message. For example:

DATEACQUIRED = 04/04/2003
DATESOLD = 12/12/2003

Yes, this gives the error message when it shouldn't!

I have tried several different validation formulae to try and solve this problem but I always get the error message whatever the value of DATESOLD. (But it's interesting to note that the expression builder interim result displays the correct result).

Does anyone have a solution that will work, for example that accepts the above example as valid but will reject a DATESOLD of 03/03/2003 as invalid?

[Tom Cone Jr]

RE: Custom validation on a date field won't work

Try:

IF(len(alltrim(dtos(DateSold))) = 0, .T., IF(DateSold >= DateAcquired, .T., .F.))

Field values that evaluate as .T. will be accepted, .F. will be trapped.

-- t

[Dick Blanchard]

RE: Custom validation on a date field won't work

Brett,
Try this for the custom validation in the field rule for DATESOLD
IIF(ISBLANK("DATESOLD"),.T.,IIF("DATESOLD")"=("DATEACQUIRED"),.T.,.F.))

It seems to work in my application.

Dick Blanchard

[Dick Blanchard]

RE: Custom validation on a date field won't work

Brett,
This works
IF(ISBLANK("DATESOLD"),.T.,IF(DATESOLD"=DATEACQUIRED,.T.,.F.))

You don't need all the parenthesis and quotes I put above.

Dick

[brett s]

RE: Custom validation on a date field won't work

Thanks guys, great work! This is a much better way of doing it.

[brett s]

RE: Custom validation on a date field won't work

Whoops, I spoke too soon. I have tried both suggestions and still get the same problem as I originally had and could not resolve. That is to say whatever value I try and place in the DATESOLD field in my ASSET table, I always get the same custom validation error.

DATESOLD can be nil, less than or greater than the DATEACQUIRED, I always get this error.

I have attached two JPEG screen snaps: one the custom validation for DATESOLD and the other showing the field values and error message.

Any suggestions?

[brett s]

RE: Custom validation on a date field won't work

Second JPEG screen snap attached.

[brett s]

RE: Custom validation on a date field won't work

Now here's something interesting I have just noticed.

If I edit the DATESOLD field in the ASSET table directly, the custom validation check works correctly. It's only when I edit the same field in a form (attached to the ASSET table) that I get this problem. I can't work out why this should be a problem.

[Tom Cone Jr]

RE: Custom validation on a date field won't work

Brett,

The expression I gave was tested before I posted it. Here's a working model. Unzip and extract contents to an empty folder.

There are at least two different ways to validate inputs of this type. (1) use a custom validation field rule expression, as I have done; or (2) attach a validation script to the OnDepart event of the DateSold field object in the data entry form. This latter course would involve a different script, obviously.

One should not do both. Is this possibly what's happened?

-- t

[brett s]

RE: Custom validation on a date field won't work

Thanks, Tom. I have downloaded your Zip file and will check it out. No, I have not done both the things you mention - I have a custom validation rule for the DATESOLD field, but nothing else. So I am really scratching my head on this one, it's really perplexing. The thing I have just noticed is that the custom validation works correctly when I edit the ASSET table directly. It's only on the form that the problem occurs.
If I can't get to the bottom of the problem I will try your suggestion #2.

[Tom Cone Jr]

RE: Custom validation on a date field won't work

Brett if it works correctly using the default form for the table that pretty much rules out errors in the way you've implemented it in field rules. The trouble most likely lies in the custom data entry form.

Are you by any chance entering DateSold before there's any value in DateAcquired?

All my testing here assumes your data entry person would enter DateAcquired first.

-- t

[Tom Cone Jr]

RE: Custom validation on a date field won't work

In testing just now if I skip DateAcquired (leaving it empty), and enter a value in DateSold the custom validation field rule does NOT throw an error. If I then enter a value in DateAcquired and save the field rule correctly checks the two dates and throws the error when it should. So, my guess about your data entry sequence is probably not going to be helpful after all. Sorry. -- t

[brett s]

RE: Custom validation on a date field won't work

I finally worked the problem out! I took your sample application, Tom, determined to build up my asset application step by step until the same custom validation error occurred. After doing this I found that it worked perfectly using your validation code - no problem at all! So I went back to my application and tried to discover why the same validation code didn't work in one case and did in the other. And then I discovered a copy of my original custom validation code in a completely unrelated field! I think what has happened is that I had inadvertently copied the old code there a few weeks ago when I was first having problems. So I have deleted the old code and everything now works correctly. Thanks for your help, Tom, (and Richard too), sorry to waste your time. But I'm ecstatic now that it's finally fixed!