Error passing date variable from Excel to SQL server.

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
smm6809Asked:
Who is Participating?
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
ZberteocCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.