Synthax error in Conditional formatting

[Ericks]

I want to apply Conditional Fomatting to a sub form's date control when it's of earlier date than a date in another table (form). I use following code in Expression is: DateDiff("s",DL ookUp("[LastMeeting]","LastMeetingD ate"),[DateOfInfoAdded])>0
but keep getting a syntax error message. Any idea what I'm doing wrong?

[ADezii]

I want to apply Conditional Fomatting to a sub form's date control when it's of earlier date than a date in another table (form). I use following code in Expression is: DateDiff("s",DL ookUp("[LastMeeting]","LastMeetingD ate"),[DateOfInfoAdded])>0
but keep getting a syntax error message. Any idea what I'm doing wrong?

Your syntax, is in fact, incorrect. Try:

DCount("[LastMeeting]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & "Forms!frmFormName![Date Field] & "#") > 0
 

[Ericks]

Your syntax, is in fact, incorrect. Try:

DCount("[LastMeeting]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & "Forms!frmFormName![Date Field] & "#") > 0
 

I typed in

DCount("[LastMeeting]", "LastMeetingDat e", "[DateOfInfoAdded] < =#" & "Forms!Compound Observations![DateOfInfoAdded] & "#") > 0

but still keep getting the syntax error message. Some more info:

The main form is called Compounds and contains 2 subforms:
LastMeeting (Form view) with a date control called LastMeetingDate and
CompoundObserva tions (Datasheet view) with a date control called DateOfInfoAdded .

I want the text of the DateofInfoAdded control become red when it's later than LastMeetingDate . For this I want to use the Conditional Formatting tool on the DateOfInfoAdded control. And it should work immediately after update of either dates.

[nico5038]

The " on the second line needs to be removed.:

DCount("[LastMeeting]", "LastMeetingDat e", "[DateOfInfoAdded] < =#" & Forms!CompoundO bservations![DateOfInfoAdded] & "#") > 0

Also be sure that your application works on a computer with the US regional settings. Having a default computer date format of dd-mm-yyyy will cause trouble...

Nic;o)

[Ericks]

The " on the second line needs to be removed.:

DCount("[LastMeeting]", "LastMeetingDat e", "[DateOfInfoAdded] < =#" & Forms!CompoundO bservations![DateOfInfoAdded] & "#") > 0

Also be sure that your application works on a computer with the US regional settings. Having a default computer date format of dd-mm-yyyy will cause trouble...

Nic;o)

Ok, getting there. I changed the setting to US and took out that ". Now I don't get the syntax error. But it doesn't change the color either.

[nico5038]

Hmm, could be because of a "not found" that will return Null as value for the DCOUNT(), try adding the NZ() function like:

NZ(DCount("[LastMeeting]", "LastMeetingDat e", "[DateOfInfoAdded] < =#" & Forms!CompoundO bservations![DateOfInfoAdded] & "#")) > 0

Nic;o)

[ADezii]

I typed in

DCount("[LastMeeting]", "LastMeetingDat e", "[DateOfInfoAdded] < =#" & "Forms!Compound Observations![DateOfInfoAdded] & "#") > 0

but still keep getting the syntax error message. Some more info:

The main form is called Compounds and contains 2 subforms:
LastMeeting (Form view) with a date control called LastMeetingDate and
CompoundObserva tions (Datasheet view) with a date control called DateOfInfoAdded .

I want the text of the DateofInfoAdded control become red when it's later than LastMeetingDate . For this I want to use the Conditional Formatting tool on the DateOfInfoAdded control. And it should work immediately after update of either dates.

In the AfterUpdate() Event of the [DateOfInfoAdded] Field on the CompoundObserva tions SubForm, place the following code. Similar, but different, code would also be placed in the [LastMeetingDate] Field on the LastMeeting Sub-Form:

Private Sub DateOfInfoAdded_AfterUpdate()
If Not IsNull(Me![DateOfInfoAdded]) And Not IsNull(Forms!Compounds!LastMeeting.Form![LastMeetingDate]) Then
  If CDate(Me![DateOfInfoAdded]) > CDate(Forms!Compounds!LastMeeting.Form![LastMeetingDate]) Then
    Me![DateOfInfoAdded].ForeColor = vbRed
  Else
    Me![DateOfInfoAdded].ForeColor = vbBlack
  End If
End If
End Sub

[Ericks]

In the AfterUpdate() Event of the [DateOfInfoAdded] Field on the CompoundObserva tions SubForm, place the following code. Similar, but different, code would also be placed in the [LastMeetingDate] Field on the LastMeeting Sub-Form:

Private Sub DateOfInfoAdded_AfterUpdate()
If Not IsNull(Me![DateOfInfoAdded]) And Not IsNull(Forms!Compounds!LastMeeting.Form![LastMeetingDate]) Then
  If CDate(Me![DateOfInfoAdded]) > CDate(Forms!Compounds!LastMeeting.Form![LastMeetingDate]) Then
    Me![DateOfInfoAdded].ForeColor = vbRed
  Else
    Me![DateOfInfoAdded].ForeColor = vbBlack
  End If
End If
End Sub

I added the code and yes, the dates turn red BUT only in Continious Form mode (I guess I have to live with that) AND all the dates change color. Also the once that are supposed to remain black because they are of earlier than the LastMeetingDate date.

[Ericks]

I added the code and yes, the dates turn red BUT only in Continious Form mode (I guess I have to live with that) AND all the dates change color. Also the once that are supposed to remain black because they are of earlier than the LastMeetingDate date.

It's amazing what a challenge this is. In another discussion posted earlier no solution was found. It seems to easy actually. Have the date control of one subform change colour depending on a date control on another subform.

[ADezii]

I added the code and yes, the dates turn red BUT only in Continious Form mode (I guess I have to live with that) AND all the dates change color. Also the once that are supposed to remain black because they are of earlier than the LastMeetingDate date.

I added the code and yes, the dates turn red BUT only in Continious Form mode (I guess I have to live with that) AND all the dates change color. Also the once that are supposed to remain black because they are of earlier than the LastMeetingDate date.

Glad you were successful, but it should work equally well on a Single as opposed to Continuous Form View.