1.

Solve : Comparing Date Values between Excel and Access (ADO)?

Answer»

Is there a simple way to MAKE identical the date format between EXCEL and Access ... i'm assuming there must be, but I can't seem to figure it out.

Basically, I need to take 2 Dates from Excel and 1 from an Access DB, and check them against each other... and if it passes, the record will be copied ONTO the Excel Worksheet.

Can anyone give me advice on how to get started with this... thank you~dteTo is a Short-Date formatted cell in Excel
datess is a field in Access

dteTo = Range("D28").Text

(1)
strQuery = "SELECT * FROM testTable1 WHERE datediff( ""d"", datess, ""02/02/2007"") = 0;"

(2)
strQuery = "SELECT * FROM testTable1 WHERE datediff( ""d"", datess, dteTo) = 0;"

1st one is ok, error for the 2nd one - 'No value given for one or more required parameters'

Someone pls help identify what is going on here... thank you~
This one of those problems where you know something is wrong but just can't see it. Actually I'm suprised example 1 worked, date literals are usually surrounded by # signs.

Quote

dteTo is a Short-Date formatted cell in Excel

I'm pretty sure the datatypes are mismatched and that by using the text property, something got messed up.

Try using dteTo = Range("D28").Value

You might try using Range("D28").Value directly in the DateDiff function or as a last resort using the CDate function to guarantee all the FIELDS are converted to dates.

Good luck.

PS. VB and VBA are not very forgiving when it comes to mismatched datatypes.


Discussion

No Comment Found

Related InterviewSolutions