By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,335 Members | 2,313 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,335 IT Pros & Developers. It's quick & easy.

Synthax error in Conditional formatting

Ericks
P: 74
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",DLookUp("[LastMeeting]","LastMeetingDate"),[DateOfInfoAdded])>0
but keep getting a syntax error message. Any idea what I'm doing wrong?
Oct 6 '07 #1
Share this Question
Share on Google+
20 Replies


ADezii
Expert 5K+
P: 8,638
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",DLookUp("[LastMeeting]","LastMeetingDate"),[DateOfInfoAdded])>0
but keep getting a syntax error message. Any idea what I'm doing wrong?
Your syntax, is in fact, incorrect. Try:
Expand|Select|Wrap|Line Numbers
  1. DCount("[LastMeeting]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & "Forms!frmFormName![Date Field] & "#") > 0
  2.  
Oct 6 '07 #2

Ericks
P: 74
Your syntax, is in fact, incorrect. Try:
Expand|Select|Wrap|Line Numbers
  1. DCount("[LastMeeting]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & "Forms!frmFormName![Date Field] & "#") > 0
  2.  
I typed in

DCount("[LastMeeting]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & "Forms!CompoundObservations![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
CompoundObservations (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.
Oct 7 '07 #3

nico5038
Expert 2.5K+
P: 3,072
The " on the second line needs to be removed.:

DCount("[LastMeeting]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & Forms!CompoundObservations![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)
Oct 7 '07 #4

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

DCount("[LastMeeting]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & Forms!CompoundObservations![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.
Oct 7 '07 #5

nico5038
Expert 2.5K+
P: 3,072
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]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & Forms!CompoundObservations![DateOfInfoAdded] & "#")) > 0

Nic;o)
Oct 7 '07 #6

ADezii
Expert 5K+
P: 8,638
I typed in

DCount("[LastMeeting]", "LastMeetingDate", "[DateOfInfoAdded] < =#" & "Forms!CompoundObservations![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
CompoundObservations (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 CompoundObservations SubForm, place the following code. Similar, but different, code would also be placed in the [LastMeetingDate] Field on the LastMeeting Sub-Form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub DateOfInfoAdded_AfterUpdate()
  2. If Not IsNull(Me![DateOfInfoAdded]) And Not IsNull(Forms!Compounds!LastMeeting.Form![LastMeetingDate]) Then
  3.   If CDate(Me![DateOfInfoAdded]) > CDate(Forms!Compounds!LastMeeting.Form![LastMeetingDate]) Then
  4.     Me![DateOfInfoAdded].ForeColor = vbRed
  5.   Else
  6.     Me![DateOfInfoAdded].ForeColor = vbBlack
  7.   End If
  8. End If
  9. End Sub
Oct 7 '07 #7

Ericks
P: 74
In the AfterUpdate() Event of the [DateOfInfoAdded] Field on the CompoundObservations SubForm, place the following code. Similar, but different, code would also be placed in the [LastMeetingDate] Field on the LastMeeting Sub-Form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub DateOfInfoAdded_AfterUpdate()
  2. If Not IsNull(Me![DateOfInfoAdded]) And Not IsNull(Forms!Compounds!LastMeeting.Form![LastMeetingDate]) Then
  3.   If CDate(Me![DateOfInfoAdded]) > CDate(Forms!Compounds!LastMeeting.Form![LastMeetingDate]) Then
  4.     Me![DateOfInfoAdded].ForeColor = vbRed
  5.   Else
  6.     Me![DateOfInfoAdded].ForeColor = vbBlack
  7.   End If
  8. End If
  9. 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.
Oct 7 '07 #8

Ericks
P: 74
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.
Oct 7 '07 #9

ADezii
Expert 5K+
P: 8,638
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.
Oct 7 '07 #10

ADezii
Expert 5K+
P: 8,638
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.
It's all a matter of code context (where the code is executing from). The reference to the Field on the 2nd Sub-Form would require a fully qualified Reference to the Control as I displayed, or use of the Parent Property, which I explicitly chose not to use. In any event, Good luck!
Oct 7 '07 #11

Ericks
P: 74
It's all a matter of code context (where the code is executing from). The reference to the Field on the 2nd Sub-Form would require a fully qualified Reference to the Control as I displayed, or use of the Parent Property, which I explicitly chose not to use. In any event, Good luck!
Thanks for helping out. As said, it still doesn't work but I will keep working on it till it does.
Oct 7 '07 #12

ADezii
Expert 5K+
P: 8,638
Thanks for helping out. As said, it still doesn't work but I will keep working on it till it does.
Ericks, are you sure you are referencing the name of the Sub-Form Control and not the Sub-Form itself? This is a common mistake.
Oct 7 '07 #13

puppydogbuddy
Expert 100+
P: 1,923
Eric,
I see you are still struggling with this. Let me ask a couple of questions that may help clear things up.
1. You have a form and two subforms. Is each of the subforms set up as a child of the main form, or is subform1 set up as a child of the main form and subform2 set up as the child of subform one?
2. List the fields you have as the master and child links for each of the subforms.
Oct 8 '07 #14

Ericks
P: 74
Eric,
I see you are still struggling with this. Let me ask a couple of questions that may help clear things up.
1. You have a form and two subforms. Is each of the subforms set up as a child of the main form, or is subform1 set up as a child of the main form and subform2 set up as the child of subform one?
2. List the fields you have as the master and child links for each of the subforms.
Yep, still struggling with something that I'm sure is easy but that Im apparently doing wrong.

The form Compounds has 2 subforms:
1. LastMeeting (in Form view), which is not a child of anything ( I think this is called an unbound form) and which has a date control called LastMeetingDate.
2. CompoundObservations (in Datasheet view), which is a child of the Form Compounds and which has a date control called DateOfInfoAdded.

The Compounds form and CompoundObservations subform are linked through a linked field called [Compound id] . The Lastmeeting form is not linked to anything. It only serves the purpose to have data entered after this date being highlighted with red letters.

After changing the regional settings to US (my settings are European since I live in Switzerland) the later dates changed colour, but again, only in form mode (no problem, I dont insist on the subform being in datasheet view). And it changes all the dates, even the onces that are to remain of black text because they are of earlier date than the last meeting.
Oct 8 '07 #15

puppydogbuddy
Expert 100+
P: 1,923
Yep, still struggling with something that I'm sure is easy but that Im apparently doing wrong.

The form Compounds has 2 subforms:
1. LastMeeting (in Form view), which is not a child of anything ( I think this is called an unbound form) and which has a date control called LastMeetingDate.
2. CompoundObservations (in Datasheet view), which is a child of the Form Compounds and which has a date control called DateOfInfoAdded.

The Compounds form and CompoundObservations subform are linked through a linked field called [Compound id] . The Lastmeeting form is not linked to anything. It only serves the purpose to have data entered after this date being highlighted with red letters.

After changing the regional settings to US (my settings are European since I live in Switzerland) the later dates changed colour, but again, only in form mode (no problem, I dont insist on the subform being in datasheet view). And it changes all the dates, even the onces that are to remain of black text because they are of earlier date than the last meeting.
So, is LastMeeting really a subform....or is it just a popup form that you are using to enter the last meeting date?
Oct 8 '07 #16

Ericks
P: 74
So, is LastMeeting really a subform....or is it just a popup form that you are using to enter the last meeting date?
LastMeeting is a subform. I can either enter a date by typing it in the field or by using that little Access calendar.
Oct 8 '07 #17

puppydogbuddy
Expert 100+
P: 1,923
LastMeeting is a subform. I can either enter a date by typing it in the field or by using that little Access calendar.

Eric,
is there some reason you don.t want to bind your LastMeeting subform to the underlying table? I think that is your problem. Try setting the record source property of the LastMeeting Subform to the LastMeeting table. Then try your application and see how it works.
Oct 8 '07 #18

Ericks
P: 74
Eric,
is there some reason you don.t want to bind your LastMeeting subform to the underlying table? I think that is your problem. Try setting the record source property of the LastMeeting Subform to the LastMeeting table. Then try your application and see how it works.
Yes, the lastmeeting record source on the subform is bound to the lastmeeting table. It stores the last meeting date in that table which contains just one record: the last meeting date. The subform sits on the Compounds form and is not linked to that as is the CompoundsObservation form. Is there a way I could send you part of this database for you to take a look at?
Oct 8 '07 #19

puppydogbuddy
Expert 100+
P: 1,923
Yes, the lastmeeting record source on the subform is bound to the lastmeeting table. It stores the last meeting date in that table which contains just one record: the last meeting date. The subform sits on the Compounds form and is not linked to that as is the CompoundsObservation form. Is there a way I could send you part of this database for you to take a look at?
Yes, go to my profile and download my VCard. It contains my email address. I have Access 2000, so your file has to be versions 97 or 2000.
Oct 8 '07 #20

Ericks
P: 74
Yes, go to my profile and download my VCard. It contains my email address. I have Access 2000, so your file has to be versions 97 or 2000.
Ok, thanks. I will have dinner soon so I will send it tomorrow.
Oct 8 '07 #21

Post your reply

Sign in to post your reply or Sign up for a free account.