Solved Conditional formatting for date and text field

[quest]

Hi, i am lately little obsessed with conditional formatting. i have two fields that i can not do (not sure how). first is that field in the new record (date field) should be equal or bigger than previous record and second is text field that shouldn't be different value (from three rows in the same field made with ctrl and enter).

 

[quest]

conditional formatting for text field solved can anyone help with date field?

 

[The_Doc_Man]

Are you trying to base a color change on a conditional format by comparing to data in a different record (row)? Conditional formatting isn't supposed to be able to do that. Has to compare in same record/row only, unless you use some tricks.

 

[quest]

Are you trying to base a color change on a conditional format by comparing to data in a different record (row)? Conditional formatting isn't supposed to be able to do that. Has to compare in same record/row only, unless you use some tricks.

thank you i wanted to compare with previous record to show me if new date isn't correct that means new date should be equal or bigger no smaller. smaller is mistake that is why should be in red.

 

[theDBguy]

Can you show us your CF expression? Are you using an Autonumber field as the primary key? Or, so you have a date field to help determine which record is the “previous” record?

 

[quest]

Can you show us your CF expression? Are you using an Autonumber field as the primary key? Or, so you have a date field to help determine which record is the “previous” record?

yes i have autonumber field as primary key. i tried few different expresions none working. here is one [dtmDatumNaPriemot]<Nz(DLookUp("dtmDatumNaPriemot";"tbl_Boleduvanja";"BoleduvanjaID=" & [BoleduvanjaID]-1);[dtmDatumNaPriemot]-1)

 

[quest]

yes i have autonumber field as primary key. i tried few different expresions none working. here is one [dtmDatumNaPriemot]<Nz(DLookUp("dtmDatumNaPriemot";"tbl_Boleduvanja";"BoleduvanjaID=" & [BoleduvanjaID]-1);[dtmDatumNaPriemot]-1)

actually start working now like it needed time to start working.

 

[theDBguy]

actually start working now like it needed time to start working.

Glad to hear you got it working, but using -1 in your expression may not be reliable. Potentially, you could end up having gaps in your Autonumber fields and -1 will end up failing. Just a thought…

 

[quest]

Glad to hear you got it working, but using -1 in your expression may not be reliable. Potentially, you could end up having gaps in your Autonumber fields and -1 will end up failing. Just a thought…

so do you have some suggestion something that will work better

 

[Ken Sheridan]

actually start working now like it needed time to start working.

As theDBguy points out, an autonumber column is unreliable for anything other than ensuring distinct values. To ensure an unboken sequence you can compute the value when a row is inserted into a table. The attached little demo file illustrates this. Alternatively you can add a DateTimeStamp column of DateTime data type to the table, setting its DefaultValue property to the Now() function. Determining the 'previous' record then means identifying the row with the highest value where the value is less than the current row. The following query would do this for a DoB column in a Contacts table. This query would always return zero to satisfy your constraint. I tried, as an experiment, using its SQL statement as a CHECK CONSTRAINT, but I get a syntax error in the CHECK clause. I know the query works correctly, so I suspect the 'error' is due to its complexity.

SELECT
    COUNT(*)
FROM
    Contacts AS C1
WHERE
    C1.DoB < (
        SELECT
            C2.DoB
        FROM
            Contacts AS C2
        WHERE
            C2.DateTimeStamp = (
                SELECT
                    MAX(DateTimeStamp)
                FROM
                    Contacts AS C3
                WHERE
                    C3.DateTimeStamp < C1.DateTimeStamp
            )
    );

 

[quest]

As theDBguy points out, an autonumber column is unreliable for anything other than ensuring distinct values. To ensure an unboken sequence you can compute the value when a row is inserted into a table. The attached little demo file illustrates this. Alternatively you can add a DateTimeStamp column of DateTime data type to the table, setting its DefaultValue property to the Now() function. Determining the 'previous' record then means identifying the row with the highest value where the value is less than the current row. The following query would do this for a DoB column in a Contacts table. This query would always return zero to satisfy your constraint. I tried, as an experiment, using its SQL statement as a CHECK CONSTRAINT, but I get a syntax error in the CHECK clause. I know the query works correctly, so I suspect the 'error' is due to its complexity.

SELECT
    COUNT(*)
FROM
    Contacts AS C1
WHERE
    C1.DoB < (
        SELECT
            C2.DoB
        FROM
            Contacts AS C2
        WHERE
            C2.DateTimeStamp = (
                SELECT
                    MAX(DateTimeStamp)
                FROM
                    Contacts AS C3
                WHERE
                    C3.DateTimeStamp < C1.DateTimeStamp
            )
    );

thank you i will study it.

 

[theDBguy]

so do you have some suggestion something that will work better

Instead of assuming the previous record is only 1 value before the current one, you could search for the highest autonumber value that is less than the current one.

 

[quest]

Instead of assuming the previous record is only 1 value before the current one, you could search for the highest autonumber value that is less than the current one.

understand it is logical but why it give results no mater autonumber has gap or not. i am testing all the time what i am writing for results for this sample database autonumber sequence is 14, 15, 16, 17, 20 yet it is still working.

 

[Ken Sheridan]

understand it is logical but why it give results no mater autonumber has gap or not. i am testing all the time what i am writing for results for this sample database autonumber sequence is 14, 15, 16, 17, 20 yet it is still working.

In that context, with the form at record 20, the DLookup function will return Null, which the NZ function will replace with the date before the date in the current row, so the expression as a whole will evaluate to False, regardless of the value of the previous date in record 17.

 

[Ken Sheridan]

Instead of assuming the previous record is only 1 value before the current one, you could search for the highest autonumber value that is less than the current one.

That assumes the previous values are incremental. Even this cannot be guaranteed with absolute certainty. I'd add a DateTimeStamp column to the table and use that.

 

[quest]

That assumes the previous values are incremental. Even this cannot be guaranteed with absolute certainty. I'd add a DateTimeStamp column to the table and use that.

thank you i will try that version also.