date range criteria for SQL server link table

[inungh]

I have following criteria for my table which works for Access table,
but not SQL server table.

Between DateAdd("m",0,[forms]![frmMain]![dtpstartdate]) And DateAdd
("m",-2,[forms]![frmMain]![dtpStartdate])

It retrives data from Access table, but not SQL server.

The fileld type is datetime in SQL server.

The follwoing works for SQL server and Access table

Between [forms]![frmMain]![dtpstartdate] And [forms]![frmMain]!
[dtpEnddate]

It looks that SQL server does not recoginze the DateAdd function.
Are there any workaround to retrieve last 2 months data from SQL
server?

Your help is great appreciated,

 

[Stefan Hoffmann]

hi,

I have following criteria for my table which works for Access table,
but not SQL server table.

Between DateAdd("m",0,[forms]![frmMain]![dtpstartdate]) And DateAdd

Running this expression against a linked SQL Server table means that Jet
executs it and Jet can evaluate this.

It looks that SQL server does not recoginze the DateAdd function.
Are there any workaround to retrieve last 2 months data from SQL
server?

Do you have a .mdb/.accdb or a .adp?



mfG
--> stefan <--

 

[KARL DEWEY]

Try it this way --
Between DateAdd("m",0,CVDate([forms]![frmMain]![dtpstartdate])) And DateAdd
("m",-2,CVDate([forms]![frmMain]![dtpStartdate]))

 

[inungh]

hi,

I have following criteria for my table which works for Access table,
but not SQL server table.

Between DateAdd("m",0,[forms]![frmMain]![dtpstartdate]) And DateAdd

Running this expression against a linked SQL Server table means that Jet
executs it and Jet can evaluate this.

It looks that SQL server does not recoginze the DateAdd function.
Are there any workaround to retrieve last 2 months data from SQL
server?

Do you have a .mdb/.accdb or a .adp?

mfG
--> stefan <--

MDB file. I use Access 2003.

Thanks again,

 

[John Spencer]

Along with the other advice you have received make sure your dates are
in chronologic order. Access doesn't care if the values are not in
ascending order in a between ... and ..., but MS SQL Server does.
Depending on how the data is passed through by the ODBC you may be
running into that problem. So try the following.

Between DateAdd("m",-2,[forms]![frmMain]![dtpstartdate]) And DateAdd
("m",0,[forms]![frmMain]![dtpStartdate])

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[inungh]

Along with the other advice you have received make sure your dates are
in chronologic order.  Access doesn't care if the values are not in
ascending order in a between ... and ..., but MS SQL Server does.
Depending on how the data is passed through by the ODBC you may be
running into that problem.  So try the following.

Between DateAdd("m",-2,[forms]![frmMain]![dtpstartdate]) And DateAdd
("m",0,[forms]![frmMain]![dtpStartdate])

'====================================================
  John Spencer
  Access MVP 2002-2005, 2007-2009
  The Hilltop Institute
  University of Maryland Baltimore County
'====================================================



inunghwrote:

I have following criteria for my table which works for Access table,
but not SQL server table.

Between DateAdd("m",0,[forms]![frmMain]![dtpstartdate]) And DateAdd
("m",-2,[forms]![frmMain]![dtpStartdate])

It retrives data from Access table, but not SQL server.

The fileld type is datetime in SQL server.

The follwoing works for SQL server and Access table

Between [forms]![frmMain]![dtpstartdate] And [forms]![frmMain]!
[dtpEnddate]

It looks that SQL server does not recoginze the DateAdd function.
Are there any workaround to retrieve last 2 months data from SQL
server?

Your help is great appreciated,- Hide quoted text -

- Show quoted text -

Thanks millions,