Query using Date variable in Visual Basic with SQL Server

Posted on 2003-02-26
Medium Priority
Last Modified: 2010-04-17
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?

Question by:shermane
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

AndrewK earned 300 total points
ID: 8029044
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 & "'"

Author Comment

ID: 8029361
You got it dude!!!


Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
Introduction to Processes
Suggested Courses

765 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