Originally posted by siva sankar:
Am unable to retrieve the users information according to the datewise through the query from access database. The datefield datatype is declared as 'shortdate'. The error is showing as "datetype mismatch criteria".
eg: select name,ddate,department from tablename where ddate between '2006-12-01' and '2006-12-31';
I tried in all date formats like mm/dd/yyyy,dd/mm/yyyy and so on.
Hi Siva,
If you are using Microsoft's Access database, it may have something to do with the syntax of the query itself. If you construct the query as a
string (instead of a parameterized query), Access requires a pound sign '#' before and after the date string. I also think Access stores date in the dd/mm/yyyy format (unless changed). Here is a sample from Microsoft's site:
SELECT *
FROM tblInvoices
WHERE CustomerID = 1 AND InvoiceDate > #01/01/98#
Here is a sample used from an access database I quickly made:
SELECT test.invoice
FROM
test WHERE (((test.invoice)<#1/1/2007# And (test.invoice)>=#1/1/2006#));
which gives me all records for 2006.
I hope this helps.