Conditional formatting MS Access continuous form

Question

I have a continuous form on MS Access 2019. The data source of the form is a query. It has date field as column from the query and unbounded text box. I also have day, month and year values present on the same form from three other unbounded controls.

I am trying to put conditional formatting expression for the unbounded textbox in MS Access but it is not changing the background color. My computer setting shows date in dd-mm-yyyy format.

I tried for the following but without success:

"[Data]=#" & [Me!txtdate.Value] & "-" & [Me!txtCurrMonth.value] & "-" & [Me!cboYear.value] & "#"

[Data]= "#" & [txtdate] & "-" & [txtCurrMonth] & "-" & [cboYear] & "#"

Please help with the correct expression for conditional formatting when date has to be formed by picking values from three other controls on the same form.

Thanks

Answer 1

I would suggest using the DateSerial function to return a date value from the three unbound controls.

Since this function returns a date value directly rather than a string representation of a date, it avoids potential regional differences in date format.

For example, try setting the Conditional Formatting expression to the folllowing:

DateValue([Date]) = DateSerial([cboYear],[txtCurrMonth],[txtdate])

Here I've also used the DateValue function to return only the date component from a datetime value which could have both a date & time component.