To use dates in expressions, you need mm/dd/yyyy or yyyy-mm-dd format
In your case, also add the time part - mm/dd/yyyy hh:nn:ss
Code:
SchemaName = DLookup("UserSchema", "t_currentschema", "UserInputDate=#" & Format(DMax("UserInputDate", "t_currentschema"), "mm/dd/yyyy hh:nn:ss") & "#")
You might be better doing this in two steps - get the latest UserInputDate then get the schema name
Code:
Dim dteDate As Date
dteDate = DMax("UserInputDate", "t_currentschema")
SchemaName = DLookup("UserSchema", "t_currentschema", "UserInputDate=#" & Format(dteDate, "mm/dd/yyyy hh:nn:ss") & "#")