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

"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

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

"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.

"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.

"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)

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)

"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.