Maybe someone more knowledgeable can comment about why the function works when a string variable needs delimiters still.
The difference whether or not you are creating a string that gets passed to the query processor. Your query string contains a non-string element and that is the name of the function. The interface with the function defines what kind of variable is being returned.so it doesn't need to be delimited.
So:
sqlWhere = " Where MyField = #" & Format(Forms!yourform!txtMyField, "mm/dd/yyyy") & "#" -- Not only must the date be enclosed in octothorps, it must also be in mdy format OR in an unambiguous format such as yyyy/mm/dd. This is because SQL Server assumes US format for ambiguous dates so 1/2/21 = Jan 2 but in Europe, you would see this date as Feb 1. If you are using a windows date setting of mdy, you do NOT need the format.
However:
sqlWhere = " Where MyField = Forms!yourform!txtMyField" -- will work without any octothorps or formatting provided the form control is bound to a date data type or if unbound, the control specifies a date format which implies it will hold a date.
Most of the time, I use saved querydefs so the second style is what I would use to pass arguments to a query. It is not wrong to build SQL strings in code. It is purely a matter of choice. In older versions of Access, the queyrdef had some technical advantages but in current versions, there is little difference unless your SQL string is embedded in a loop. It could be slower because of the extra overhead of computing an accces plan each time the query executes. For querydefs, the access plan is calculated and stored the first time a query runs.