1) Date is a reserved word in access, you shouldnt use it as a column
2) you "cant" use left on a date field, use format if you are wanting it in a text format
3) a text of MM/DD/YYYY isnt workable for between purposes
4) Why format the date as text??? a date is date for a reason.
5) It isnt stored as LONG DATE, all dates are dates, period. It is FORMATTED as long date.
6) See you left an important part out of your OP, remove your left function and try again using US format for the parameters, I think you will find it magicaly works now.