Query result different in Access 97 and Access 2000

[josty]

The following query matches records where two absolute numbers (i.e. 10.123 and 20.234) are between a certain round number (i.e. 15). To make this match possible I use Int() to convert the abs.numbers in the DB to an Integer without fraction.

To prevent formatting errors the data-type of the absolute numbers fields in Access is 'text'.

This works fine on a server with Access 97 (Jet 3.51).
The same exact query does NOT return/find ANY result on a server with Access 2000 (Jet 4.0).

Why o why? I hope there are some greater minds then mine out there..

<cfquery name=&quot;appointments&quot; datasource=&quot;#DSN#&quot;>
SELECT * from agenda
WHERE '#some_round_number#'
BETWEEN Int(some_absolute_number)
AND Int(someother_absolute_number)
</cfquery>

Thanks in advance!

 

[CFDude]

I have had some weird problems in the past using BETWEEN not giving the results I expected. You may want to try
I'd suggest using a more standard operator like:

SELECT * from agenda
WHERE
Int(some_absolute_number) > #val(some_round_number)#
AND Int(some_absolute_number)< #val(some_round_number)#