[AccessD] less than on text field

[Paul Hartland]

Ah got you now of course, could you not have used CINT...Just done another
test which seems to work....

SELECT dbo_tblTest.test
FROM dbo_tblTest
WHERE CINT(dbo_tblTest.test)<11;

On 16 December 2015 at 05:13, John R Bartow <jbartow at winhaven.net> wrote:

> Thanks Paul,
> That does work directly with the text field. The problem is:
> < 11 displays 10 but not 9, 8, 7 and 6
> > 11 displays 9 and 8 but not 10
> < 11 OR >11 returns 11.1943, etc. (all the minor versions)
>
> So I thought by converting it to integer that it would be a quick solution
> as then < 11 would work perfectly. Problem is doesn't and this hasn't been
> quick, lol. I even imported all the data into an access DB instead of
> connecting via ODBC. Nothing would work for criteria on the converted
> field.
>
> So, I used the query without the version filter, created a table and then
> changed the new table's double type to integer type made a duplicate query
> using the new table and all worked fine. Seems a bit roundabout way to find
> 4 client workstations out of 240 that need old software removed.
>
> Thanks again,
> John
> BTW this is all to find my clients with old IE versions installed (supports
> end Jan 12, 2016)
>
> https://support.microsoft.com/en-us/lifecycle#gp/Microsoft-Internet-Explorer
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Paul Hartland
> Sent: Tuesday, December 15, 2015 8:49 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] less than on text field
>
> I don't know much about Access past 2007 and at the moment only have access
> to a copy of 2003, so not sure if what I put will work.  I created a test
> table in SQL Server 2014 express with a field called test of type
> nvarchar(50), nvarchar being an equivalent type to text in MySQL and put a
> singe record in with the value 11, I then linked the table to Access and
> created a query to show records < 12 and it worked, when I went into the
> SQL
> it looked like this
>
> SELECT dbo_tblTest.test
> FROM dbo_tblTest
> WHERE (((dbo_tblTest.test)<"12"));
>
> hope this helps, note that it does not use the ! but a . instead between
> table and field names.
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>



-- 
Paul Hartland
paul.hartland at googlemail.com