Today is an Excel function, no? You mean "BETWEEN Date() AND Date()" (or BETWEEN Date and Date)??
Might work if you use
BETWEEN Format(Date,"mm/dd/yy") AND Format(Date,"mm/dd/yy")-6
If doing this in VBA I think you would require string delimiters as well.
The Format function converts Dates to strings. The opposite is CDate, which formats strings to dates. Either requires that they look like dates to Access.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.