Date Restrictor no longer works when linking to SQL Server table

[Chuck W]

Not sure if I should post this in this board or an SQL Server one. I have an
access database with a query called qrySelectAccNum with three fields:
AccNum, FullName, and DischargDate. In my criteria for DischargeDate I have
'between Date() and Date()-60 to get all discharges in the last 60 days. The
query worked fine. It used to be based on an Access table called tblPatients.
I deleted this table and created a link to a new SQL Server table called
Encounters. In my query, I changed the table name from tblPatients to
Encounters but my date restrictor no longer works. If I remove the date
restrictor, the query does return results but I want to restrict to the last
60 days. Does anyone know what the new date restrictor should be?

Thanks,

 

[Jeff Boyce]

Chuck

Does your [Encounters] table have a field named [DischargeDate]?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Chuck W]

Yes. The field name is the same. When I run the query against the linked
SQL Server table without the date restrictor, it returns results. Apparently
the date restrictor listed below only works for Access.

Chuck

Chuck

Does your [Encounters] table have a field named [DischargeDate]?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Not sure if I should post this in this board or an SQL Server one. I have
an
access database with a query called qrySelectAccNum with three fields:
AccNum, FullName, and DischargDate. In my criteria for DischargeDate I
have
'between Date() and Date()-60 to get all discharges in the last 60 days.
The
query worked fine. It used to be based on an Access table called
tblPatients.
I deleted this table and created a link to a new SQL Server table called
Encounters. In my query, I changed the table name from tblPatients to
Encounters but my date restrictor no longer works. If I remove the date
restrictor, the query does return results but I want to restrict to the
last
60 days. Does anyone know what the new date restrictor should be?

Thanks,

 

[John Spencer]

Try switching the arguments so that the lowest date comes first.

DischargeDate Between Date()-60 and Date()

or instead of using between use

DischargeDate <= Date() and DischargeDate >= Date()-60

As I recall JET doesn't care about the order (smaller BEFORE larger), but SQL
Server does. When the query gets "converted" to run against the SQL Server it
may not take that into account and the resulting query as interpreted might be
something like
DischargeDate >= Date() and DischargeDate <= Date()-60
which would end up finding NO matches.

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

 

[Chuck W]

Thanks John. FYI 'Between Date()-60 and Date()' did work correctly. I
didn't think it mattered which was first but apparently it does. Chuck

 

[John Spencer]

Order does matter with SQL Server, but it does not matter with Jet (the
default db engine for Access).

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