Access And SQL date statement


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Access And SQL date statement

  1. #1
    Saiful Guest

    Access And SQL date statement


    Hi All

    I have this Select statement that access some date field that I use to access
    an Access Database. This statement works fine.

    THE STATEMENT:-
    cmdstring = "SELECT shft, datevalue(#" & NowDate & "#) + adjust_nr FROM shift_tm
    where #" & _
    NowTime & "# between start_tm and end_tm"

    But then, my program should not only access MS Access Database but it should
    also be able to access SQL Server database as well.
    So when I execute my program, it prompts me error for the above statement.
    Error is:-

    Incorrect syntax near '#'.

    Can anyone suggest a statement that works for both the Access Database as
    well as the SQL Server database.


    rgds,
    Saiful

  2. #2
    Arthjur Wood Guest

    Re: Access And SQL date statement


    "Saiful" <md-saifulamri_omar@hp.com> wrote:
    >
    >Hi All
    >
    >I have this Select statement that access some date field that I use to access
    >an Access Database. This statement works fine.
    >
    >THE STATEMENT:-
    >cmdstring = "SELECT shft, datevalue(#" & NowDate & "#) + adjust_nr FROM

    shift_tm
    >where #" & _
    > NowTime & "# between start_tm and end_tm"
    >
    >But then, my program should not only access MS Access Database but it should
    >also be able to access SQL Server database as well.
    >So when I execute my program, it prompts me error for the above statement.
    >Error is:-
    >
    >Incorrect syntax near '#'.
    >
    >Can anyone suggest a statement that works for both the Access Database as
    >well as the SQL Server database.
    >
    >
    >rgds,
    >Saiful



  3. #3
    Arthjur Wood Guest

    Re: Access And SQL date statement


    Saiful,
    What you are encountering is the fact the SQL Server and the Jet engine
    speak slightly different dialects of SQL. In particular, Jet requires that
    dates be delimited by "#", while SQL Server uses "'". You will have to adjust
    you code,depending on which Back end database you are using, I am not aware
    of any easy way around this problem, though there may be others who frequent
    this News Group who van suggest a better solution.

    Arthur Wood

    "Saiful" <md-saifulamri_omar@hp.com> wrote:
    >
    >Hi All
    >
    >I have this Select statement that access some date field that I use to access
    >an Access Database. This statement works fine.
    >
    >THE STATEMENT:-
    >cmdstring = "SELECT shft, datevalue(#" & NowDate & "#) + adjust_nr FROM

    shift_tm
    >where #" & _
    > NowTime & "# between start_tm and end_tm"
    >
    >But then, my program should not only access MS Access Database but it should
    >also be able to access SQL Server database as well.
    >So when I execute my program, it prompts me error for the above statement.
    >Error is:-
    >
    >Incorrect syntax near '#'.
    >
    >Can anyone suggest a statement that works for both the Access Database as
    >well as the SQL Server database.
    >
    >
    >rgds,
    >Saiful



  4. #4
    jy Guest

    Re: Access And SQL date statement


    "Arthjur Wood" <wooda@saic-trsc.com> wrote:
    >
    >Saiful,
    > What you are encountering is the fact the SQL Server and the Jet engine
    >speak slightly different dialects of SQL. In particular, Jet requires that
    >dates be delimited by "#", while SQL Server uses "'". You will have to

    adjust
    >you code,depending on which Back end database you are using, I am not aware
    >of any easy way around this problem, though there may be others who frequent
    >this News Group who van suggest a better solution.
    >
    >Arthur Wood
    >
    >"Saiful" <md-saifulamri_omar@hp.com> wrote:
    >>
    >>Hi All
    >>
    >>I have this Select statement that access some date field that I use to

    access
    >>an Access Database. This statement works fine.
    >>
    >>THE STATEMENT:-
    >>cmdstring = "SELECT shft, datevalue(#" & NowDate & "#) + adjust_nr FROM

    >shift_tm
    >>where #" & _
    >> NowTime & "# between start_tm and end_tm"
    >>
    >>But then, my program should not only access MS Access Database but it should
    >>also be able to access SQL Server database as well.
    >>So when I execute my program, it prompts me error for the above statement.
    >>Error is:-
    >>
    >>Incorrect syntax near '#'.
    >>
    >>Can anyone suggest a statement that works for both the Access Database

    as
    >>well as the SQL Server database.
    >>
    >>
    >>rgds,
    >>Saiful

    >

    the alternative is to open the recordset through a command object, setting
    the command text to something like:

    cmd.commandtext="SELECT shft, datevalue(?)+ adjust_nr FROM shift_tm where
    ? between start_tm and end_tm"

    and then manually append parameters:

    cmd.parameters.append cmd.createparameter(name:="@NowDate", type:=adDate,
    value:=NowDate)
    cmd.parameters.append cmd.createparameter(name:="@NowTime", type:=adDate,
    value:=NowTime)

    finally opening the recordset so:

    set rst=cmd.execute

    you will however be limited to a forward-only read-only recordset with server-side
    cursor location, unless you wish to manually transfer it to a second hand-rolled
    recordset.

    J-Y.

  5. #5
    jy Guest

    Re: Access And SQL date statement


    "jy" <jy@directdialog.com> wrote:
    >
    >"Arthjur Wood" <wooda@saic-trsc.com> wrote:
    >>
    >>Saiful,
    >> What you are encountering is the fact the SQL Server and the Jet engine
    >>speak slightly different dialects of SQL. In particular, Jet requires

    that
    >>dates be delimited by "#", while SQL Server uses "'". You will have to

    >adjust
    >>you code,depending on which Back end database you are using, I am not

    aware
    >>of any easy way around this problem, though there may be others who frequent
    >>this News Group who van suggest a better solution.
    >>
    >>Arthur Wood
    >>
    >>"Saiful" <md-saifulamri_omar@hp.com> wrote:
    >>>
    >>>Hi All
    >>>
    >>>I have this Select statement that access some date field that I use to

    >access
    >>>an Access Database. This statement works fine.
    >>>
    >>>THE STATEMENT:-
    >>>cmdstring = "SELECT shft, datevalue(#" & NowDate & "#) + adjust_nr FROM

    >>shift_tm
    >>>where #" & _
    >>> NowTime & "# between start_tm and end_tm"
    >>>
    >>>But then, my program should not only access MS Access Database but it

    should
    >>>also be able to access SQL Server database as well.
    >>>So when I execute my program, it prompts me error for the above statement.
    >>>Error is:-
    >>>
    >>>Incorrect syntax near '#'.
    >>>
    >>>Can anyone suggest a statement that works for both the Access Database

    >as
    >>>well as the SQL Server database.
    >>>
    >>>
    >>>rgds,
    >>>Saiful

    >>

    >the alternative is to open the recordset through a command object, setting
    >the command text to something like:
    >
    >cmd.commandtext="SELECT shft, datevalue(?)+ adjust_nr FROM shift_tm where
    >? between start_tm and end_tm"
    >
    >and then manually append parameters:
    >
    >cmd.parameters.append cmd.createparameter(name:="@NowDate", type:=adDate,
    >value:=NowDate)
    >cmd.parameters.append cmd.createparameter(name:="@NowTime", type:=adDate,
    >value:=NowTime)
    >
    >finally opening the recordset so:
    >
    >set rst=cmd.execute
    >
    >you will however be limited to a forward-only read-only recordset with server-side
    >cursor location, unless you wish to manually transfer it to a second hand-rolled
    >recordset.
    >
    >J-Y.

    didn't realise before - if you've got ado 2.5 and up, you can happily set
    the recordset proprties to your heart's content. :-) J-Y.

  6. #6
    jy Guest

    Re: Access And SQL date statement


    "jy" <jy@directdialog.com> wrote:
    >
    >"jy" <jy@directdialog.com> wrote:
    >>
    >>"Arthjur Wood" <wooda@saic-trsc.com> wrote:
    >>>
    >>>Saiful,
    >>> What you are encountering is the fact the SQL Server and the Jet engine
    >>>speak slightly different dialects of SQL. In particular, Jet requires

    >that
    >>>dates be delimited by "#", while SQL Server uses "'". You will have to

    >>adjust
    >>>you code,depending on which Back end database you are using, I am not

    >aware
    >>>of any easy way around this problem, though there may be others who frequent
    >>>this News Group who van suggest a better solution.
    >>>
    >>>Arthur Wood
    >>>
    >>>"Saiful" <md-saifulamri_omar@hp.com> wrote:
    >>>>
    >>>>Hi All
    >>>>
    >>>>I have this Select statement that access some date field that I use to

    >>access
    >>>>an Access Database. This statement works fine.
    >>>>
    >>>>THE STATEMENT:-
    >>>>cmdstring = "SELECT shft, datevalue(#" & NowDate & "#) + adjust_nr FROM
    >>>shift_tm
    >>>>where #" & _
    >>>> NowTime & "# between start_tm and end_tm"
    >>>>
    >>>>But then, my program should not only access MS Access Database but it

    >should
    >>>>also be able to access SQL Server database as well.
    >>>>So when I execute my program, it prompts me error for the above statement.
    >>>>Error is:-
    >>>>
    >>>>Incorrect syntax near '#'.
    >>>>
    >>>>Can anyone suggest a statement that works for both the Access Database

    >>as
    >>>>well as the SQL Server database.
    >>>>
    >>>>
    >>>>rgds,
    >>>>Saiful
    >>>

    >>the alternative is to open the recordset through a command object, setting
    >>the command text to something like:
    >>
    >>cmd.commandtext="SELECT shft, datevalue(?)+ adjust_nr FROM shift_tm where
    >>? between start_tm and end_tm"
    >>
    >>and then manually append parameters:
    >>
    >>cmd.parameters.append cmd.createparameter(name:="@NowDate", type:=adDate,
    >>value:=NowDate)
    >>cmd.parameters.append cmd.createparameter(name:="@NowTime", type:=adDate,
    >>value:=NowTime)
    >>
    >>finally opening the recordset so:
    >>
    >>set rst=cmd.execute
    >>
    >>you will however be limited to a forward-only read-only recordset with

    server-side
    >>cursor location, unless you wish to manually transfer it to a second hand-rolled
    >>recordset.
    >>
    >>J-Y.

    >didn't realise before - if you've got ado 2.5 and up, you can happily set
    >the recordset proprties to your heart's content. :-) J-Y.

    (using rst.open ... J-Y)

  7. #7
    saiful Guest

    Re: Access And SQL date statement


    Hi JY

    What da you mean by setting the properties on the recordset in ADO 2.5 or
    above. Hope that you don't mind clarifying further?

    Anyway, thanks all for the advice given

    rgds,
    Saiful


    "jy" <jy@directdialog.com> wrote:
    >
    >"jy" <jy@directdialog.com> wrote:
    >>
    >>"jy" <jy@directdialog.com> wrote:
    >>>
    >>>"Arthjur Wood" <wooda@saic-trsc.com> wrote:
    >>>>
    >>>>Saiful,
    >>>> What you are encountering is the fact the SQL Server and the Jet

    engine
    >>>>speak slightly different dialects of SQL. In particular, Jet requires

    >>that
    >>>>dates be delimited by "#", while SQL Server uses "'". You will have

    to
    >>>adjust
    >>>>you code,depending on which Back end database you are using, I am not

    >>aware
    >>>>of any easy way around this problem, though there may be others who frequent
    >>>>this News Group who van suggest a better solution.
    >>>>
    >>>>Arthur Wood
    >>>>
    >>>>"Saiful" <md-saifulamri_omar@hp.com> wrote:
    >>>>>
    >>>>>Hi All
    >>>>>
    >>>>>I have this Select statement that access some date field that I use

    to
    >>>access
    >>>>>an Access Database. This statement works fine.
    >>>>>
    >>>>>THE STATEMENT:-
    >>>>>cmdstring = "SELECT shft, datevalue(#" & NowDate & "#) + adjust_nr FROM
    >>>>shift_tm
    >>>>>where #" & _
    >>>>> NowTime & "# between start_tm and end_tm"
    >>>>>
    >>>>>But then, my program should not only access MS Access Database but it

    >>should
    >>>>>also be able to access SQL Server database as well.
    >>>>>So when I execute my program, it prompts me error for the above statement.
    >>>>>Error is:-
    >>>>>
    >>>>>Incorrect syntax near '#'.
    >>>>>
    >>>>>Can anyone suggest a statement that works for both the Access Database
    >>>as
    >>>>>well as the SQL Server database.
    >>>>>
    >>>>>
    >>>>>rgds,
    >>>>>Saiful
    >>>>
    >>>the alternative is to open the recordset through a command object, setting
    >>>the command text to something like:
    >>>
    >>>cmd.commandtext="SELECT shft, datevalue(?)+ adjust_nr FROM shift_tm where
    >>>? between start_tm and end_tm"
    >>>
    >>>and then manually append parameters:
    >>>
    >>>cmd.parameters.append cmd.createparameter(name:="@NowDate", type:=adDate,
    >>>value:=NowDate)
    >>>cmd.parameters.append cmd.createparameter(name:="@NowTime", type:=adDate,
    >>>value:=NowTime)
    >>>
    >>>finally opening the recordset so:
    >>>
    >>>set rst=cmd.execute
    >>>
    >>>you will however be limited to a forward-only read-only recordset with

    >server-side
    >>>cursor location, unless you wish to manually transfer it to a second hand-rolled
    >>>recordset.
    >>>
    >>>J-Y.

    >>didn't realise before - if you've got ado 2.5 and up, you can happily set
    >>the recordset proprties to your heart's content. :-) J-Y.

    >(using rst.open ... J-Y)



  8. #8
    jy Guest

    Re: Access And SQL date statement


    "saiful" <md-saifulamri_omar@hp.com> wrote:
    >
    >Hi JY
    >
    >What da you mean by setting the properties on the recordset in ADO 2.5 or
    >above. Hope that you don't mind clarifying further?
    >
    >Anyway, thanks all for the advice given
    >
    >rgds,
    >Saiful
    >
    >
    >"jy" <jy@directdialog.com> wrote:
    >>
    >>"jy" <jy@directdialog.com> wrote:
    >>>
    >>>"jy" <jy@directdialog.com> wrote:
    >>>>
    >>>>"Arthjur Wood" <wooda@saic-trsc.com> wrote:
    >>>>>
    >>>>>Saiful,
    >>>>> What you are encountering is the fact the SQL Server and the Jet

    >engine
    >>>>>speak slightly different dialects of SQL. In particular, Jet requires
    >>>that
    >>>>>dates be delimited by "#", while SQL Server uses "'". You will have

    >to
    >>>>adjust
    >>>>>you code,depending on which Back end database you are using, I am not
    >>>aware
    >>>>>of any easy way around this problem, though there may be others who

    frequent
    >>>>>this News Group who van suggest a better solution.
    >>>>>
    >>>>>Arthur Wood
    >>>>>
    >>>>>"Saiful" <md-saifulamri_omar@hp.com> wrote:
    >>>>>>
    >>>>>>Hi All
    >>>>>>
    >>>>>>I have this Select statement that access some date field that I use

    >to
    >>>>access
    >>>>>>an Access Database. This statement works fine.
    >>>>>>
    >>>>>>THE STATEMENT:-
    >>>>>>cmdstring = "SELECT shft, datevalue(#" & NowDate & "#) + adjust_nr

    FROM
    >>>>>shift_tm
    >>>>>>where #" & _
    >>>>>> NowTime & "# between start_tm and end_tm"
    >>>>>>
    >>>>>>But then, my program should not only access MS Access Database but

    it
    >>>should
    >>>>>>also be able to access SQL Server database as well.
    >>>>>>So when I execute my program, it prompts me error for the above statement.
    >>>>>>Error is:-
    >>>>>>
    >>>>>>Incorrect syntax near '#'.
    >>>>>>
    >>>>>>Can anyone suggest a statement that works for both the Access Database
    >>>>as
    >>>>>>well as the SQL Server database.
    >>>>>>
    >>>>>>
    >>>>>>rgds,
    >>>>>>Saiful
    >>>>>
    >>>>the alternative is to open the recordset through a command object, setting
    >>>>the command text to something like:
    >>>>
    >>>>cmd.commandtext="SELECT shft, datevalue(?)+ adjust_nr FROM shift_tm where
    >>>>? between start_tm and end_tm"
    >>>>
    >>>>and then manually append parameters:
    >>>>
    >>>>cmd.parameters.append cmd.createparameter(name:="@NowDate", type:=adDate,
    >>>>value:=NowDate)
    >>>>cmd.parameters.append cmd.createparameter(name:="@NowTime", type:=adDate,
    >>>>value:=NowTime)
    >>>>
    >>>>finally opening the recordset so:
    >>>>
    >>>>set rst=cmd.execute
    >>>>
    >>>>you will however be limited to a forward-only read-only recordset with

    >>server-side
    >>>>cursor location, unless you wish to manually transfer it to a second

    hand-rolled
    >>>>recordset.
    >>>>
    >>>>J-Y.
    >>>didn't realise before - if you've got ado 2.5 and up, you can happily

    set
    >>>the recordset proprties to your heart's content. :-) J-Y.

    >>(using rst.open ... J-Y)

    >

    what i've found up to and including ado 2.1 was that, if you opened a recordset
    through a command object (using cmd.execute or rst.open), you always got
    a forward-only read-only recordset with a server-side cursor, and had assumed
    it was the same for ado 2.5; however after doing what i should have done
    as soon as i started with 2.5 and actually played about, i've found that
    you can set the recordset properties (cursorlocation, cursortype, locktype,
    etc.) before opening it (eg. rst.open source:=cmd, options:=adCmdStoredProc)
    and not have them overwritten by the command's defaults. so for instance
    you can now return disconnected updateable recordsets if you require. hope
    this helps. J-Y.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center