BETWEEN problem

[salx]

The below SQL returns unexpected results - SQL, table and result samples
provided

The objective: return rows from the master table when a transaction exist for
the transaction date less 1 month


SELECT a.PKey, a.[SDate], a.[EDate], DateAdd('m',-1,b.TDate) AS TDless1
FROM tblMaster AS a
, tblTran AS b
WHERE (A.PKey=B.PKey)
AND (DateAdd("M",-1,B.TDate) Between A.SDate and A.EDate);

tblMASTER
-----------------
PKey SDate EDate
00001 4/1/2008 5/1/2008
00001 5/1/2008 6/1/2008
00001 6/1/2008 6/1/2008
00001 7/1/2008 7/1/2008

tblTran
----------
PKey TDate
00001 6/1/2008


Results

 

[John Spencer]

6/1/2008 adjusted by one month is 5/1/2008

5/1/2008 is between 4/1/2008 and 5/1/2008
5/1/2008 is between 5/1/2008 amd 6/1/2008

the next two records fail the test


Between is inclusive of the start and end dates

I don't understand how the record 6/1/2008 to 6/1/2008 was returned.



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

 

[KARL DEWEY]

I get the records as below --
PKey SDate EDate TDless1
00001 4/1/2008 5/1/2008 5/1/2008
00001 5/1/2008 6/1/2008 5/1/2008

--
KARL DEWEY
Build a little - Test a little

6/1/2008 adjusted by one month is 5/1/2008

5/1/2008 is between 4/1/2008 and 5/1/2008
5/1/2008 is between 5/1/2008 amd 6/1/2008

the next two records fail the test


Between is inclusive of the start and end dates

I don't understand how the record 6/1/2008 to 6/1/2008 was returned.



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

The below SQL returns unexpected results - SQL, table and result samples
provided

The objective: return rows from the master table when a transaction exist for
the transaction date less 1 month


SELECT a.PKey, a.[SDate], a.[EDate], DateAdd('m',-1,b.TDate) AS TDless1
FROM tblMaster AS a
, tblTran AS b
WHERE (A.PKey=B.PKey)
AND (DateAdd("M",-1,B.TDate) Between A.SDate and A.EDate);

tblMASTER
-----------------
PKey SDate EDate
00001 4/1/2008 5/1/2008
00001 5/1/2008 6/1/2008
00001 6/1/2008 6/1/2008
00001 7/1/2008 7/1/2008

tblTran
----------
PKey TDate
00001 6/1/2008


Results

 

[salx]

John,

Typeo in my creating the sample SQL and data. the problem was customers with
like data that I did not notice

sorry and thanks for the time.

6/1/2008 adjusted by one month is 5/1/2008

5/1/2008 is between 4/1/2008 and 5/1/2008
5/1/2008 is between 5/1/2008 amd 6/1/2008

the next two records fail the test

Between is inclusive of the start and end dates

I don't understand how the record 6/1/2008 to 6/1/2008 was returned.

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

The below SQL returns unexpected results - SQL, table and result samples
provided

[quoted text clipped - 26 lines]

00001 5/1/2008 6/1/2008 **** not expected **** any ideas?
00001 6/1/2008 6/1/2008 **** expected and ok

 

[salx]

Karl,

Typeo in my creating the sample SQL and data. the problem was customers with
like data that I did not notice

sorry and thanks for the time.

I get the records as below --
PKey SDate EDate TDless1
00001 4/1/2008 5/1/2008 5/1/2008
00001 5/1/2008 6/1/2008 5/1/2008

6/1/2008 adjusted by one month is 5/1/2008

[quoted text clipped - 42 lines]