SQL String in VBA in Excel 2010 with Dates

Question

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=########;

shahkalpesh · Accepted Answer · 2012-11-28 06:55:26Z

3

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") & "' "

edited Nov 28, 2012 at 6:55

answered Nov 27, 2012 at 8:05

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Willson Fang Over a year ago

I can't add # in the SQL Query, it just gives me the runtime error: Line 1: Incorrect syntax near '#'. Added extra information inregards to the field I am comparing to.

Nick.Mc Over a year ago

# is used in MS Access. This is SQL Server

Nick.Mc · Accepted Answer · 2012-11-28 05:22:27Z

1

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.

answered Nov 28, 2012 at 5:22

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

2 Comments

Willson Fang Over a year ago

Call.CallDate is definitely a DateTime, have just double checked to confirm. That query runs perfectly fine in the SQL Database View creator, but fails completely in VBA. Changed date to YYYYMMDD and all good as you suggested, chosen as answer.

Nick.Mc Over a year ago

An even more reliable one would be instead of '2012-11-27', Use CONVERT(DATETIME,'20121127',112). This provides an explicit format code to indicate exactly what you're passing.

Collectives™ on Stack Overflow

SQL String in VBA in Excel 2010 with Dates

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related