Unexpected results in a query

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

In my Access 2000 database (mdb-database) I have the
following query:

SELECT Transactions.TransactionDate,
Transactions.TransactionAmount
FROM Transactions
WHERE (((Transactions.TransactionDate) Between [Forms]!
[frmFilter]![FirstDate] And ([Forms]![frmFilter]!
[LastDate]-1)));

So, if frmFilter.FirstDate = "01.04.2004" and
frmFilter.LastDate = "19.04.2004" the query should
retrieve transactions for the period from 01.04.04 till
18.04.04.

The "Transactions" table is a linked table.
When it is linked to an Access back-end database
everything works fine, but if it is linked to an ODBC
source (SQL server) it retrieves records for the period
from 01.04.04 till 20.04.04 (one day more instead of one
day less).

Why?????? Any ideas?
 
Try using the DateAdd function to subtract the day.
Or you could use this syntax:
(YourTable.YourDate >= [Forms]![frmFilter]![FirstDate]) And
(YourTable.YourDate < [Forms]![frmFilter]![LastDate])));

Ragnar
 
Access probably trying to pass basic SQL request to SQL Server which is not
interpreted correctly. I have had similar problems with using ODBC-linked
Tables from SQL Sever databases. One case I remember is the criteria
(written by another developer):

....
WHERE [DateField] BETWEEN Now() AND (Now() - 3)

(second Date/Time is earlier than first Date/Time)

This works fine with Access-linked Tables but NOT ODBC-linked Tables from
SQL Server. The other developer couldn't work out what's wrong (neither did
I). However, I like to have the earlier date first so I changed to:

....
WHERE [DateField] BETWEEN (Now() - 3) AND Now()

and suddenly it worked correctly.

Try using the Date/Time function DateAdd rather than arithmetic addition.
 
Back
Top