Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error passing date variable from Excel to SQL server.

Posted on 2014-08-11
4
Medium Priority
?
276 Views
Last Modified: 2014-08-11
I have recently started using SQL server again as a database for an Excel Application. Previously I was using Access. I am trying to pass a date variable to SQL  and am getting a syntax error. I know in access the code would read as follows. Not sure the difference in SQL. For that matter, what is the syntax for passing strings? My code might be incorrect there as well.

            strSQL1 = "Select * " _
                & "           From dbo.tblCredit" _
                & "        Where dtCreditDate = # " & dtCreditDate & " # " _
                & "             And numCompanyID = " & rs!numCompanyID & "" _
                & "             And strXOrder = '" & rs!strXOrder & "'" _
                & "              And strVendor='" & strVendor & "'"







Thanks
0
Comment
Question by:smm6809
  • 3
4 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 40253366
SQL Server uses single quote marks ' to delineate dates, access uses pound signs #.  So..
          & "        Where dtCreditDate = '" & dtCreditDate & "' " _

Open in new window

btw there's an article called Migrating your Access Queries to SQL Server Transact-SQL where this is discussed about halfway down.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40253375
btw this style might be easier to read
Dim sSQL as String
sSQL = "SELECT * " _
sSQL = sSQL  & "FROM dbo.tblCredit" 
sSQL = sSQL  & "WHERE dtCreditDate = #" & dtCreditDate & "# " 
sSQL = sSQL  & "   AND numCompanyID = " & rs!numCompanyID
sSQL = sSQL  & "   AND strXOrder = '" & rs!strXOrder & "'"
sSQL = sSQL  & "   AND strVendor='" & strVendor & "'"

Open in new window

0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 40253406
If you pass that query to SQL you don't use # as delimiter fro date but single quote:
 strSQL1 = "Select * " _
                & "           From dbo.tblCredit" _
                & "        Where dtCreditDate = '" & dtCreditDate & "'" _
                & "             And numCompanyID = " & rs!numCompanyID & "" _
                & "             And strXOrder = '" & rs!strXOrder & "'" _
                & "              And strVendor='" & strVendor & "'"

Open in new window

You also have to make sure that the date format is recognizable by SQL server. If you use
YYYY-MM-DD HH:MM:SS.mmm should be fine.
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40253616
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question