passing a date from a form to a query

[bbxrider]

in the criteria field of a date column I am trying to pass values entered on a form
Between #forms![percent_rpt_form]![percent_start_date]# And #Forms![percent_rpt_form]![percent_end_date]#
when trying to save the query I get:


"the expression you entered has an invalid date value."
I have tried ...date.text, ...date.value, ....date].text, ....date].value
but can't seem to make access happy

[orange]

Is the Form open? try without the # signs.
Can you post the SQL of the query you are using?

[bbxrider]

thanks, this is as close to sql as I can send, it does not allow the between statement with the #(date) brackets after it is entered in the criteria field either to save or to view in sql
SELECT transaction_history.Detail, transaction_history.comm_id, transaction_history.Tier, transaction_history.eType, transaction_history.[trans-date]
FROM transaction_history
WHERE (((transaction_history.[trans-date]) Between [forms]![partic_percent_rpt_form]![partic_percent_start_date] And [Forms]![partic_percent_rpt_form]![partic_percent_end_date]));
when I run this version without the # brackets the query runs but gives results not within the date range supplied, it accepts all records in the source table, with no date range criteria applied
I used the access build editor to provision the text box fields, i.e. I did not type the values in myself

[bbxrider]

the query works if I hard code dates within the # date brackets in the between statement

[bbxrider]

ok got 1 solution anyway. I am casting the date from the form with cdate. once that is done you can do a direct compare either with 'between' or using operators, <= and >=, and no # date brackets are needed. note that the source field from the table being compared is defined as date time in the table. so far the # date brackets work with hard coded text values, eg. #11/1/14# but not sure why the # brackets can't seem to work with a variable like text box content from a form