Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Query using Date variable in Visual Basic with SQL Server

I have some VB code that works with MS Access but I want to make it work with SQL Server.

Here is a code snippet:

Dim dteLatestDate as Date

strQuery = "Select MAX(R.RATING_DATE) " & _
           "From lot_history_admin.RATING_HIST R"

rstResult.Open strQuery, strConnectionString adOpenForwardOnly, adLockReadOnly

dteLatestDate = rstResult!LATEST_DATE

strQuery = "Select R.DELIV_RATING, R.PERF_RATING, " & _
           "R.COMMENTS, R.RATING_DATE " & _
           "From lot_history_admin.RATING_HIST R " & _
           "Where ((RTrim(R.VENDOR)) = '" & strVendorCode & "') " & _
           "And R.RATING_DATE = #" & dteLatestDate & "#"

rstResult.Open strQuery strConnectionString , adOpenForwardOnly, adLockReadOnly

I'm having problems with the line:

"And R.RATING_DATE = #" & dteLatestDate & "#"

Works okay in MS Access but if I move the table data to SQL server and connect to SQL Server I get an error:

"Incorrect Syntax near '#'.

So I guess it doesn't like the # in the query.

The RATING_DATE column is Date/Time in MS Access and
is datetime in SQL Server.

I tried removing the #'s but still having problems.
I tried using search engines to try to find an example.

Anyone know to make the query work with a SQL Server table?

1 Solution
You are correct. SQL Server doesn't like the hash marks (#). What it wants is a single quote like a text field. So:

R.RATING_DATE = '" & dteLatestDate & "'"
shermaneAuthor Commented:
You got it dude!!!


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now