The SQL statement

SELECT pendingdetailsDrum.ID, DateValue([orderDate]) AS DatePortion
FROM pendingdetailsDrum
WHERE DateValue([orderDate])=DateValue('1/2/2006')

is giving me Internal OLE Automation Error, however when I remove the Where clause it works.

Can someone explain why? OR...

The reason I am using DateValue is because the orderDate field has a date portion and a time portion. When I put a date in the criteria it is not found because of the time portion. I guess I could run an update query and change all the date + time to just date, however , I would rather use the SQL Statement using the DateValue function.

Thanks so much for your responses.

I have never used the DateValue function.
You could use the Format function to return a string instead of a variant.
You could use a subquery in which you add a field
Select format([orderdate],'dd/mm/yyyy') as OrderDateString, ...and use this query as a basis to produce the final result.

HTH:D

You could also try using

SELECT pendingdetailsDrum.ID, DateValue([orderDate]) AS DatePortion
FROM pendingdetailsDrum
WHERE [orderDate] = #1/2/2006#

SELECT pendingdetailsDrum.ID, DateValue([orderDate]) AS DatePortion
FROM pendingdetailsDrum
WHERE DateValue([orderDate])=DateValue('1/2/2006')

yes its the syntax in the where clause - dates need to wrapped in # characters as rabbie says, so

WHERE DateValue([orderDate]) = # " & datereference & "#"

so either directly, as rabbie says, or yuor method should work, or use a form reference to make it generically useful

be careful with misleading results caused by US/UK date issues - eg 1/2/06 may be taken as

Jan 2nd (US) or 1st Feb (UK)

format([orderdate],'dd/mm/yyyy') and DateValue([orderDate]) work if there is nothing in the Criteria. However, even when I put something in the criteria for
DateOrder: format([orderdate],'dd/mm/yyyy') or DateValue([orderDate]) I get an error.

For DateOrder: format([orderdate],'dd/mm/yyyy')

I get the following error: "Invalid Procedure Call."

I decided to strip the table field of the time information and then just used the regular #//# in the criteria because I had to meet my deadline.

Would still like to know why the above does not work.

Thanks.

I can't reproduce the error. Perhaps you miss some of the references.
If you still want to know what the error causes, post a sample database describing the problem.