Have I understood this correctly?

[UncleRobin]

I have added a couple of new fields to my main table, “First Started” and “First Ended”

I want to make sure that the dated in “First Ended” cannot be prior to the date in “First Started”. I’ve been checking the Base Guide, and I’m fairly certain this is the relevant SQL

ALTER TABLE "Loan" ADD CHECK (IFNULL("Return_Date","Loan_Date")>="Loan_Date")

If my table is “tDramas” and the new fields are “First Start Date” and “First End Date”, am I correct in thinking that

ALTER TABLE "tDramas" ADD CHECK (IFNULL("First_Start_Date","First_End_Date")>="First_End_Date")

would achieve my objective?

[RobertG]

For Base Guide: “Loan_Date” is a field, which is SET NOT NULL.
For your “tDramas” “First_End_Date” has to be set as SET NOT NULL. But if I see the screenshot (and think about "How should something end, which never has been started) you have to change “First_End_Date” and “First_Start_Date”.

[cpb]

@UncleRobin,

@UncleRobin

would achieve my objective?

the answer is NO.
.
as mentioned by RobertG you have to change “First_End_Date” and “First_Start_Date”
BUT
even with them the right way round it allows input of “First_End_Date” when “First_Start_Date” is null.
.
BACKUP YOUR DATABASE OR AT THE VERY LEAST THE TABLE “tDramas” BEFORE TRYING THIS.
.
the SQL below will work (I hope), it permits:
the saving of records when both “First_End_Date” and “First_Start_Date” are null.
the saving of records when “First_End_Date” is null and “First_Start_Date” is not null.
it denies:
the saving of records when “First_End_Date” is not null but “First_Start_Date” is null.
the saving of records when “First_End_Date” is less than “First_Start_Date”.
.
it’s good practice to name your constraint so that if necessary it can easily be dropped:

alter table "tDramas" drop constraint "CHK_DTES";

here is the constraint, execute via menu:>Tools>SQL:

alter table "tDramas" add constraint "CHK_DTES" check
(
	("First_Start_Date" is not null and coalesce("First_End_Date","First_Start_Date") >= "First_Start_Date")
	or
	"First_End_Date" is null
)
;

[UncleRobin]

Thank you both! I was a little troubled by the “Null” element when I thought about my copy-paste type edit of the SQL from Guide. Being able to leave the end dated null is important, for the sort of reason you stated, RobertG. I have made multiple backups and will try your suggested SQL,cpb once I have read it carefully to understand what it does and why. Very grateful to you both.

[UncleRobin]

I copied and pasted the SQL into the SQL window and pressed “execute” I then attempted to enter a “First_End_Date” earlier than “First_Start_Date”. This was the result:

I was able to enter the earlier date without any error message or issues. I checked my table and wondered if the fact that the two specified fields had spaces in their names rather than underscores might be the problem, so I renamed them by removing the spaces and edited the SQL accordingly. This is the SQL command window:

The same result - no difficulty adding end dates that are prior to start dates.

[cpb]

it looks like the constraint has not been applied.
if your table contains data which violates the constraint conditions then the database will reject it.
run this SQL from ‘Create Query in SQL View’

select * from INFORMATION_SCHEMA.SYSTEM_TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'CHECK'

if the constraint exists you will see CONSTRAINT_NAME = CHK_DTES and TABLE_NAME = “tDramas”
.
I downloaded your database “AsianEnt1.odb” from a previous thread of yours, added the fields “First_End_Date” and “First_Start_Date” to the table “tDramas”, executed the constraint SQL via menu:>Tools>SQL and everything works exactly as expected.

[UncleRobin]

@cpb

select * from INFORMATION_SCHEMA.SYSTEM_TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'CHECK'

Thanks again. As you said, the constraint was not being applied, there must have been data that violated the constraint. I checked this by exporting the table to Calc, and then subtracting “First_Start_Date” from “First_End_Date”. doing this returned the expected “0” or a positive diffference in all but four cases. One was a data entry error, exactly the sort of thing that I’m hoping to prevent with the constraint. The others were those records with a “First_Start_Date” value and no “First_End_Date” value

I downloaded the backup copy I had made before trying the SQL code, fixed the one actual error, made sure the field names matched the SQL exactly, and tried again. Same result. The constraint was not applied, confirmed by the constraint SQL query you supplied. Since the SQL you supplied does work as intended with null values, I take it this means that those 3 records without a “First_End_Date” in my copies entered are actually zero and not null?

I’m happy to start again from ‘scratch’ by doing as you did, adding the 2 new fields and then adding the constraint before adding data to those new fields. But I would also like to understand where I went wrong, since mistakes are great teachers when the reasons for them are understood. I really appreciate your patient assistance, again.

[cpb]

EDIT: changed table name in update statement from “tData” to “tDramas” (just a typo).
.
you could first try running an update statement:

update "tDramas" set "First_End_Date" = null, "First_Start_Date" = null;

only you know exactly what you did, it was obvious that the constraint was not applied, you received an error message via Tools>SQL, we cannot see what exactly that message said but if all went to plan then that would have been confirmed by ‘1: Command successfully executed.’.
.
I just dropped the constraint then ran the update statement on “AsianEnt1.odb”, I then re-applied the constraint and all was good.

[UncleRobin]

you could first try running an update statement:

Thanks. I did that, and then altered the table to add the constraint. The SQL command window said “command successfully executed”. I then ran the schema query, and sure enough the constraint was there.

I then opened my form, and saw the two fields blank.

I then entered data that violated the constraint, as in this screenshot

It allowed me to enter the invalid date without any error message and populated those two fields in every record with that data. I navigated through to the end using the “next” button on the form, and all records showed the same data (that shown in the screenshot). The data was not persistent, when I closed the form and reopened it, the fields were empty again. Just to test I added a “Save” button on the main form, and when I entered the invalid date and pressed the new Save button, the dates entered both fields populated all records AND persisted after closing the form.

[UncleRobin]

FIXED IT! Thanks to your patience, I knew for a fact that I had done something wrong somwhere, so I went into each control properties dialog, and found that the field names were wrong. When I corrected them, EUREKA!

THANK YOU!!!