Sending Form dates to query

[jwelt]

Ok here's my question:
I'm trying to display, in a text box, the total number of units between two dates inputed by the user. What I'm trying to do is below:

SELECT [Units].[Date], [Units].[Buyer]
FROM [Units]
WHERE ((([Units].[Date]) Between [Forms]![Search]![From] And [Forms]![Search]![To]));

Where [Search] is the name of the form, and [from] and [to] are the names of the text boxes that the user can enter dates. I think the above code should return all records between those dates. After that, I want sum all the products and display the answer in a text box. However, I cant get the above code to work at all. If if manually put in:

Between "01/01/01" And "08/08/08"

in the criteria box, I get correct results. but sending dates from a form seems to mess it up. Any suggestions? And also, how would I go about summing them up after?

 

[gol4]

1. Is the textbox [From] and [To] formatted as a date
if not format it to a date
2. if you are wanting to set this query as the control source for a text box it won't work , a list box yes
3. I am not sure what you want to sum date and buyer don't sound like number fields

try the above and please clarify what you want to sum

 

[Eupher]

Try enclosing the form object in pound (#) signs, like this:

SELECT [Units].[Date], [Units].[Buyer]
FROM [Units]
WHERE ((([Units].[Date]) Between #[Forms]![Search]![From]# And #[Forms]![Search]![To]#));

The # sign is the delimiter for literal dates in queries.

HTH...

--
Ken S.