date format in the required table is d/mm/yyyy
Dates are not stored in a "Format". They are stored as decimal data. Formats are only used for display purposes.

By default, SQL Server always assumes mm/dd/yyyy when it converts literal strings to dates.

So if your DatePicker value is 06/01/2004, displayed in dd/mm/yyyy format (ie Jan 6) , SQL Server will interpret that as June 1.

As suggested make sure your date literals use the ISO standard of yyyymmdd for comparisons.

Code:
adoduecallbacks.Open "Select * from tblcontacts where dtmrecontactdate = '" & Format$(DTPicker1.Value,"yyyymmdd") & "'", db, adOpenStatic, adLockOptimistic
By the way the conversion error was being caused by the % sign. % is only used as a Wildcard with Like.