Post reply

Date Problem

  • October 29, 2005 at 4:40 am

    #89718

    Line 1: incorrect syntax near ')'

     

    I am using SQL server ver 8.00, OS WIN XP.

    In VB (ADO) i am using 2 DTpicker Controls. When i write a query to select the records for in between dates, i was thrown with an error "Line 1: Incorrect syntax error near "#" ".

    sStr = "select * from reel where pdate between " & "(#" & y1 & "#)" & " and " & "(#" & y2 & "#)" & " order by reelno DESC"

    Also, when i interface with Crystal Report 8.5, it is not getting any data out. Simply empty report is spooling...

    But the same connection is working fine with Access ADO.

    Can somebody help to me solve the problem..

    Thanks in advance.

  • October 30, 2005 at 3:22 pm

    #601194

    Using the # symbols to define date is for MS Access not SQL Server.

     

    --------------------
    Colt 45 - the original point and click interface

  • October 30, 2005 at 8:09 pm

    #601207

    I tried replacing # with '. Even then "Line 1: Incorrect syntax near "y1" occurs.

     

    Any other way to get rid off.

     

    Thanks once again

  • October 30, 2005 at 9:09 pm

    #601210

    So what is the completed Query string look like? Are you able to copy it into Query Analyzer and run it?

     

    --------------------
    Colt 45 - the original point and click interface

  • October 31, 2005 at 9:04 am

    #601340

    If y1 and y2 are a date datatype, you will need to convert them to strings.  I know Crystal allows you to put columns of different data types together into a string using &, but that doesn't work in SQL.  You may even need to change the & to + in order for this to work; I've never tried the & in SQL so I'm not sure.  You may also want to change your double quotes to single quotes, although that may not matter in this case. 

    Sam

  • October 31, 2005 at 10:14 am

    #601363

    Print out sStr.

    It should look this:

    select * from reel where pdate between '10/21/2005 ' and '10/31/2005'  order by reelno DESC

  • October 31, 2005 at 11:18 am

    #601386

    Are y1 and y2 variables? If so, they should be @y1 and @y2.

    -SQLBill

  • November 1, 2005 at 2:26 am

    #601499

    As suggested , i have replaced & with ' and run the same in QUery Analyser. It is working fine now. 

    Thanks to all of you...

     

    Regards

    SRagghu

  • November 1, 2005 at 2:27 am

    #601500

    As suggested , i have replaced & with ' and run the same in QUery Analyser. It is working fine now. 

    Thanks to all of you...

     

    Regards

    SRagghu

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply