Conditional Formatting Dlookup help needed

Question

I have a Date Field on a continuous subform called [ExcurDate] that I want to check against another Date Field called ArriveDate in a table using a FK field called [FK_ExcurID] on the host form against a table's PK called PKID_Main.

I used the following formula in a conditional formatting 'Expression is:' entry, I get no errors reported when I close the CF box, but nothing seems to change colour on the form field in question.

[Me].[Excur_Date]=DLookUp("[ArriveDate]","tbl_Main","[PKID_Main] =" & "FK_ExcurID")

What am I doing wrong?

Answer 1

I realise I have been having in the most part, a conversation with myself.

Those are some of my best conversations. <g>

Answer 2

One thing for sure is that you have "FK_ExcurID" enclosed in quotes, making it a string literal.  Also, you don't need the "Me." qualifier (and I'm surprised it accepted it).  Try this:

    [Excur_Date]=DLookUp("ArriveDate","tbl_Main","[PKID_Main] = " & [FK_ExcurID])

Answer 3

Ok, I think I figured it out...

I didn't have a depart date in the table because it is a calculated date in the form's underlying query. So I changed both dlookup table references to the form's query. And it worked a treat!

Working formula:

[ExcurDate]<DLookUp("ArriveDate","Qry_Main","[PKID_Main] = " & [FKID_Excur]) OR [ExcurDate]>DLookUp("DepartDate","Qry_Main","[PKID_Main] = " & [FKID_Excur])

I realise I have been having in the most part, a conversation with myself. But I leave this all here in case anyone else has a need for it.

Thanks Dirk.

Answer 4

Hi Again Dirk, having the utmost faith in your syntax, I re-examined my references, which were wrong!

This is the correct formula: [ExcurDate]=DLookUp("ArriveDate","tbl_Main","[PKID_Main] = " & [FKID_Excur])

which works!

Thank you for your time.

Can you tell me how I can express a smiliar line for another box, but based on the above table reference that say's:

If [ExcurDate] is less than [ArriveDate] OR more than [DepartDate] do the conditional formatting.

I tried this: 

[ExcurDate]<DLookUp("ArriveDate","tbl_Main","[PKID_Main] = " & [FKID_Excur]) OR [ExcurDate]>DLookUp("DepartDate","tbl_Main","[PKID_Main] = " & [FKID_Excur])

but it doesn't seem to work. (double checked the references, all correct)

Answer 5

Hi Dirk, maybe it accepted it after all the relentless hacking I did to it... probably just surrendered :)

in any event, your most welcome suggestion didn't do it either? 

I know CF is working on that form because it happily displays on another unrelated field on the same form.