Conditional Formatting using data from other Table (1 Viewer)

[Shoutaro]

Hi to everyone!

I have a subform in datasheet view, based from a query which is a collection of events data during the years. I wanted to apply conditional formatting to highlight the Public Holidays. I have a table [tblPublicHolidays] listing the relevant public holidays dates throughout a number of years.

I thought its simple apply conditional formatting to that field (using the wizard to be sure to have the correct path) thus <Field Value is -> equal to ->[tblPublicHolidays]![Date] but it didn't work I tried to set it as an expression but the same result. :banghead:

I know I'm missing something but I cannot figure it out.

Please can you help me out?

Thanks

 

[June7]

Can't reference table directly that way. Use DLookup domain aggregate function.

DLookup("[Date]", "[tblPublicHolidays]", "[Date]=#" & [fieldname] & "#")

Another option may be to include tblPublicHolidays in the form RecordSource with RIGHT JOIN on date fields. Then rule:

Field Value is: Equal to: [Date]

Date is a reserved word and should not use reserved word as name for anything. I would use HolDate in tblPublicHolidays.

 

[Shoutaro]

Good morning

Thanks very much for your assistance, I tried the DLookup function but it didn't work then I tried to work around it: DLookUp("[Date]","[tblPublicHolidays]","[Date]= " & [Forms]![qryEventsGlobal]![EventDate]) but it didn't work either there must be something wrong with the way I'm referencing the where condition.

 

[June7]

Note the # characters in my example.

 

[Shoutaro]

Good morning

I tried that but I missed something for sure, but in the meanwhile I adopted your second suggestion and I Right Joint the other table and it works just perfect.

Thanks very much for all your help!!