CODEWITHSUNDEEP
sqlexcelvba
Willson Fang
Willson Fang

Reputation: 13

SQL String in VBA in Excel 2010 with Dates

I've had a look around but cannot find the issue with this SQL Statement:

    strSQL = "SELECT Directory.DisplayName, Department.DisplayName, Call.CallDate, Call.Extension, Call.Duration, Call.CallType, Call.SubType FROM (((Department INNER JOIN Directory ON Department.DepartmentID = Directory.DepartmentID) INNER JOIN Extension ON (Department.DepartmentID = Extension.DepartmentID) AND (Directory.ExtensionID = Extension.ExtensionID)) INNER JOIN Site ON Extension.SiteCode = Site.SiteCode) INNER JOIN Call ON Directory.DirectoryID = Call.DirectoryID WHERE (Call.CallDate)>=27/11/2012"

Regardless of what I change the WHERE it always returns every single value in the database (atleast I assume it does since excel completely hangs when I attempt this) this SQL statement works perfectly fine in Access (if dates have # # around them). Any idea how to fix this, currently trying to create a SQL statement that allows user input on different dates, but have to get over the this random hurdle first.

EDIT: The date field in the SQL Database is a DD/MM/YY HH:MM:SS format, and this query is done in VBA - EXCEL 2010.
Also to avoid confusion have removed TOP 10 from the statement, that was to stop excel from retrieving every single row in the database.
Current Reference I have activated is: MicrosoftX Data Objects 2.8 Library
Database is a MSSQL, using the connection string:
Provider=SQLOLEDB;Server=#######;Database=#######;User ID=########;Password=########;

Upvotes: 1

Views: 20420

Answers (2)

shahkalpesh
shahkalpesh

Reputation: 33474

WHERE (Call.CallDate) >= #27/11/2012#

Surround the date variable with #.

EDIT: Please make date string unambiguous, such as 27-Nov-2012

strSQL = "SELECT ........ WHERE myDate >= #" & Format(dateVar, "dd-mmm-yyyy") & "# "

If you are using ado, you should look at Paramaters instead of using dynamic query.

EDIT2: Thanks to @ElectricLlama for pointing out that it is SQL Server, not MS-Access

strSQL = "SELECT ........ WHERE myDate >= '" & Format(dateVar, "mm/dd/yyyy") & "' "

Upvotes: 3

Nick.Mc
Nick.Mc

Reputation: 19225

Please verify that the field Call.CallDate is of datatype DATETIME or DATE

If you are indeed running this against SQL Server, try this syntax for starters:

SELECT Directory.DisplayName, Department.DisplayName, Call.CallDate, 
Call.Extension, Call.Duration, Call.CallType, Call.SubType 
FROM (((Department INNER JOIN Directory 
ON Department.DepartmentID = Directory.DepartmentID) 
INNER JOIN Extension ON (Department.DepartmentID = Extension.DepartmentID) 
AND (Directory.ExtensionID = Extension.ExtensionID)) 
INNER JOIN Site ON Extension.SiteCode = Site.SiteCode) 
INNER JOIN Call ON Directory.DirectoryID = Call.DirectoryID 
WHERE (Call.CallDate)>= '2012-11-27'

The date format you see is simply whatever format your client tool decides to show it in. Dates are not stored in any format, they are effectively stored as a duration since x.

By default SQL Uses the format YYYY-MM-DD if you want to use a date literal.

But you are much better off defining a parameter of type date in your code and keeping your date a data type 'date' for as long as possible. This may include only allowing them to enter the date using a calendar control to stop ambiguities.

Upvotes: 1

Related Questions