the important thing to understand is what datatypes you are comparing - dates or text.
as previously stated, date datatypes are number - now is 43041.5326157407 for example.
This is displayed how you want using the format property (in which case the underlying value remains numeric) or the format function, which converts the numeric value to a text datatype.
So if in a query, you are comparing date (i.e. numeric) datatypes
[date_repair_fault] >=DateAdd("h", -24, currnow)
where currnow is also a date type field in the tables used by a query, then you don't need the # characters.
However if curnow has come from a form or undeclared parameter then Curnow is text - so you use the # characters to tell sql that what comes between is to be treated as a date (i.e. numeric). And SQL (not just access sql) will interprete that date using the US structure of mm/dd/yyyy.
You can write a function where you pass a date string and a date type is returned
