I used a date type variable as follows
dToday = Format(Date, "Medium Date")
vTPCode = DLookup("[TPCode]", "tblTWTTP", "[TRef]=" & iID & " AND [TPCode]=" & iTPCode & " AND " & dToday & " BETWEEN #" & [ApptStart] & "# AND #" & [ApptEnd] & "#")
However, I still get the same error.