Comparison Inconsistency

[topcat39]

I am using datediff and the comparison operators to determine if the difference between two dates is greater than a baseline time frame. Some of the returned rows shows what I expect, but some do not.

Query Fields involved:
Min_FRI: (This is the baseline time frame) Long Integer from another query
LastMetBurn: Nz([MaxofBurnDate],#1/1/1900#) (First date to compare, but some records have null date)
YrsSinceLast: DateDiff("yyyy",[LastMetBurn],[DateCrit]) (DateCrit comes from a second query)

(All of the above display what I expect)

IsEA:
IIf([LastMetBurn]<>#1/1/1900#,[yrssincelast]>=[Min_FRI],-1)
Basically, if the last burn was too long ago, return true, if it's within the parameters, return false, if there was no last burn, return true


Here are two of the returned rows with DateCrit - 7/1/2013:

	Min_FRI	LastMetBurn	YrsSinceLast	IsEA
Sample 1	4	1/1/1998	15	0
Sample 2	2	3/27/2008	5	-1
Sample 3	2	5/22/2011	2	-1
Sample 4	4	4/2/2003	10	0

I have checked that being a 2 always works and a 4 doesn't, but I can find examples both ways.

I have also tried replacing the calculated fields in the IsEA field (LastMetBurn, yrssincelast) with the expanded source used to, but it still doesn't work.

Anyone have any ideas?
TIA



Terri

[June7]

The issue is Min_FRI and YrsSinceLast both appear to be treated as text values. This means alpha sort applies. 10 and 15 sort before 4 and 2 so they are less than 4 and 2, not greater than.

I don't know why they are treated as text but can assure treatment as integer with CInt() function.

[topcat39]

Thank you!!! This works. I've been hitting my head against a brick wall most of this morning!