query not correct: between dates ?

  • Thread starter Thread starter Rick Stahl
  • Start date Start date
R

Rick Stahl

The following query is not working properly. The data displayed on certain
WorkEndingDates should not be returned because it is not between the
EffectiveDate and EndingDate. What is odd I am using the same subquery and
table in another query that returns the correct data within the given dates.
Any help is greatly appreciated ! Thanks.


SELECT TotalGrossWeeklyPay.dtmWorkEndingDate,
TotalGrossWeeklyPay.strFirstName, TotalGrossWeeklyPay.strLastName,
[tblNon-TaxDeductions].curOtherDeductions,
[tblNon-TaxDeductions].strComments
FROM TotalGrossWeeklyPay INNER JOIN [tblNon-TaxDeductions] ON
(TotalGrossWeeklyPay.strLastName = [tblNon-TaxDeductions].strLastName) AND
(TotalGrossWeeklyPay.strFirstName = [tblNon-TaxDeductions].strFirstName)
WHERE
(([tblNon-TaxDeductions]![dtmEffectiveDate]<=[TotalGrossWeeklyPay]![dtmWorkEndingDate]<=[tblNon-TaxDeductions]![dtmEndingDate]
And ([tblNon-TaxDeductions].curOtherDeductions) Is Not Null));
 
The following query is not working properly. The data displayed on certain
WorkEndingDates should not be returned because it is not between the
EffectiveDate and EndingDate. What is odd I am using the same subquery and
table in another query that returns the correct data within the given dates.
Any help is greatly appreciated ! Thanks.
Click to expand...

The expression

(([tblNon-TaxDeductions]![dtmEffectiveDate]<=[TotalGrossWeeklyPay]![dtmWorkEndingDate]<=[tblNon-TaxDeductions]![dtmEndingDate]))

makes sense to a human - but not to a computer. Or more accurately, it makes
sense to a computer *but not in the same way*! It's comparing the value of

[tblNon-TaxDeductions]![dtmEffectiveDate]

to the value of the expression

[TotalGrossWeeklyPay]![dtmWorkEndingDate]<=[tblNon-TaxDeductions]![dtmEndingDate]
which is either TRUE (0) or FALSE (-1). In any case, it won't find those
records where demWorkEndingDate is between the other two dates!

Boolean Algebra and English language constructs *are different*.

Try

(([tblNon-TaxDeductions]![dtmEffectiveDate]<=[TotalGrossWeeklyPay]![dtmWorkEndingDate]
AND
[tblNon-TaxDeductions]![dtmEffectiveDate]<=[tblNon-TaxDeductions]![dtmEndingDate]))


This will evaluate the two binary conditions as TRUE or FALSE; the AND logical
operator will return TRUE for the entire expression if and only if both
component expressions are TRUE.

John W. Vinson [MVP]
 
Thanks again John ! I got it. It was actually a slight variation of what you suggested (just a typo) instead of
(([tblNon-TaxDeductions]![dtmEffectiveDate]<=[TotalGrossWeeklyPay]![dtmWorkEndingDate]
AND
[tblNon-TaxDeductions]![dtmEffectiveDate]<=[tblNon-TaxDeductions]![dtmEndingDate]))
Click to expand...

it was

(([tblNon-TaxDeductions]![dtmEffectiveDate]<=[TotalGrossWeeklyPay]![dtmWorkEndingDate]
AND
[TotalGrossWeeklyPay]![dtmWorkEndingDate]<=[tblNon-TaxDeductions]![dtmEndingDate]))

I understood the concept completely. Thanks again !




John W. Vinson said:
The following query is not working properly. The data displayed on certain
WorkEndingDates should not be returned because it is not between the
EffectiveDate and EndingDate. What is odd I am using the same subquery and
table in another query that returns the correct data within the given dates.
Any help is greatly appreciated ! Thanks.
Click to expand...

The expression

(([tblNon-TaxDeductions]![dtmEffectiveDate]<=[TotalGrossWeeklyPay]![dtmWorkEndingDate]<=[tblNon-TaxDeductions]![dtmEndingDate]))

makes sense to a human - but not to a computer. Or more accurately, it makes
sense to a computer *but not in the same way*! It's comparing the value of

[tblNon-TaxDeductions]![dtmEffectiveDate]

to the value of the expression

[TotalGrossWeeklyPay]![dtmWorkEndingDate]<=[tblNon-TaxDeductions]![dtmEndingDate]
which is either TRUE (0) or FALSE (-1). In any case, it won't find those
records where demWorkEndingDate is between the other two dates!

Boolean Algebra and English language constructs *are different*.

Try

(([tblNon-TaxDeductions]![dtmEffectiveDate]<=[TotalGrossWeeklyPay]![dtmWorkEndingDate]
AND
[tblNon-TaxDeductions]![dtmEffectiveDate]<=[tblNon-TaxDeductions]![dtmEndingDate]))


This will evaluate the two binary conditions as TRUE or FALSE; the AND logical
operator will return TRUE for the entire expression if and only if both
component expressions are TRUE.

John W. Vinson [MVP]
Click to expand...
 
For a Criteria expression, you could probably use Between as well:

[TotalGrossWeeklyPay]![dtmWorkEndingDate] Between [tblNon-TaxDeductions]![dtmEffectiveDate] AND [tblNon-TaxDeductions]![dtmEndingDate]

Between is inclusive, so equal date values would be included rather than excluded (which is what you have).

--
HTH,
George
(([tblNon-TaxDeductions]![dtmEffectiveDate]<=[TotalGrossWeeklyPay]![dtmWorkEndingDate]
AND
[tblNon-TaxDeductions]![dtmEffectiveDate]<=[tblNon-TaxDeductions]![dtmEndingDate]))
Click to expand...

it was

(([tblNon-TaxDeductions]![dtmEffectiveDate]<=[TotalGrossWeeklyPay]![dtmWorkEndingDate]
AND
[TotalGrossWeeklyPay]![dtmWorkEndingDate]<=[tblNon-TaxDeductions]![dtmEndingDate]))

I understood the concept completely. Thanks again !




John W. Vinson said:
The following query is not working properly. The data displayed on certain
WorkEndingDates should not be returned because it is not between the
EffectiveDate and EndingDate. What is odd I am using the same subquery and
table in another query that returns the correct data within the given dates.
Any help is greatly appreciated ! Thanks.
Click to expand...

The expression

(([tblNon-TaxDeductions]![dtmEffectiveDate]<=[TotalGrossWeeklyPay]![dtmWorkEndingDate]<=[tblNon-TaxDeductions]![dtmEndingDate]))

makes sense to a human - but not to a computer. Or more accurately, it makes
sense to a computer *but not in the same way*! It's comparing the value of

[tblNon-TaxDeductions]![dtmEffectiveDate]

to the value of the expression

[TotalGrossWeeklyPay]![dtmWorkEndingDate]<=[tblNon-TaxDeductions]![dtmEndingDate]
which is either TRUE (0) or FALSE (-1). In any case, it won't find those
records where demWorkEndingDate is between the other two dates!

Boolean Algebra and English language constructs *are different*.

Try

(([tblNon-TaxDeductions]![dtmEffectiveDate]<=[TotalGrossWeeklyPay]![dtmWorkEndingDate]
AND
[tblNon-TaxDeductions]![dtmEffectiveDate]<=[tblNon-TaxDeductions]![dtmEndingDate]))


This will evaluate the two binary conditions as TRUE or FALSE; the AND logical
operator will return TRUE for the entire expression if and only if both
component expressions are TRUE.

John W. Vinson [MVP]
Click to expand...
 
Great. Thank you George. You are correct.


For a Criteria expression, you could probably use Between as well:

[TotalGrossWeeklyPay]![dtmWorkEndingDate] Between [tblNon-TaxDeductions]![dtmEffectiveDate] AND [tblNon-TaxDeductions]![dtmEndingDate]

Between is inclusive, so equal date values would be included rather than excluded (which is what you have).

--
HTH,
George
(([tblNon-TaxDeductions]![dtmEffectiveDate]<=[TotalGrossWeeklyPay]![dtmWorkEndingDate]
AND
[tblNon-TaxDeductions]![dtmEffectiveDate]<=[tblNon-TaxDeductions]![dtmEndingDate]))
Click to expand...

it was

(([tblNon-TaxDeductions]![dtmEffectiveDate]<=[TotalGrossWeeklyPay]![dtmWorkEndingDate]
AND
[TotalGrossWeeklyPay]![dtmWorkEndingDate]<=[tblNon-TaxDeductions]![dtmEndingDate]))

I understood the concept completely. Thanks again !




John W. Vinson said:
The following query is not working properly. The data displayed on certain
WorkEndingDates should not be returned because it is not between the
EffectiveDate and EndingDate. What is odd I am using the same subquery and
table in another query that returns the correct data within the given dates.
Any help is greatly appreciated ! Thanks.
Click to expand...

The expression

(([tblNon-TaxDeductions]![dtmEffectiveDate]<=[TotalGrossWeeklyPay]![dtmWorkEndingDate]<=[tblNon-TaxDeductions]![dtmEndingDate]))

makes sense to a human - but not to a computer. Or more accurately, it makes
sense to a computer *but not in the same way*! It's comparing the value of

[tblNon-TaxDeductions]![dtmEffectiveDate]

to the value of the expression

[TotalGrossWeeklyPay]![dtmWorkEndingDate]<=[tblNon-TaxDeductions]![dtmEndingDate]
which is either TRUE (0) or FALSE (-1). In any case, it won't find those
records where demWorkEndingDate is between the other two dates!

Boolean Algebra and English language constructs *are different*.

Try

(([tblNon-TaxDeductions]![dtmEffectiveDate]<=[TotalGrossWeeklyPay]![dtmWorkEndingDate]
AND
[tblNon-TaxDeductions]![dtmEffectiveDate]<=[tblNon-TaxDeductions]![dtmEndingDate]))


This will evaluate the two binary conditions as TRUE or FALSE; the AND logical
operator will return TRUE for the entire expression if and only if both
component expressions are TRUE.

John W. Vinson [MVP]
Click to expand...
 
Back
Top