Expression Builder Date Format

[RB85]

Just been struggling for a while to update the Control Source using the Expression Builder for a text box on my form. I want it to look at a date range linked to a selected date in another textbox on the form. The two date formats appear to be different, the expression builder is looking for US format (mm/dd/yyyy) but the text box date format is my normal dd/mm/yyyy. Can I get the date format in the control source to look for dd/mm/yyyy?

[RB85]

This is my expression:
=DMax("[qrySensorPressureCalcs]![Elevation (m H2O)]","[qrySensorPressureCalcs]","[qrySensorPressureCalcs]![RecordingDateTime]<=#" & [Date1] & "#")

Trying to get it before [Date1], which is 12 August 2023, but when I put that date in [Date1] it looks for the max before 08/12/2023. I tried changing the format of Date1 to mm/dd/yyyy in its properties but its made no difference to the expression answer only displays in that format on the form.

[June7]

Are you outside the U.S.? Is your system date in dd/mm/yyyy structure? Review http://allenbrowne.com/ser-36.html

=DMax("[Elevation (m H2O)]", "[qrySensorPressureCalcs]", "[RecordingDateTime]<=#" & Format([Date1], "mm\/dd\/yyyy") & "#")

[RB85]

Thanks for the response. I am outside US and system date is dd/mm/yyyy.

I tried that expression and with some slight adjustments looking at that link you provided but I just get #Name? error now.

[June7]

I was missing a quote mark in my example. Are you sure you have correct syntax and field names? Is Date1 a field and/or textbox in form?

[RB85]

Yeah I noticed the missing quote but didnt work with it added. Date1 is a textbox in the form.

[June7]

Sorry, remove Me. from the expression.

[RB85]

Yep that works, thanks a lot!