[AccessD] less than on text field

[Jim Dettman]

 I've looked back through the thread and I don't see where you say exactly
what the issue is.

 But as a comment, depending on VAL() to extract a numeric on a text field
can be problematic and depends on what's in the field as data.

  For example:  val("123 E 76th street")

  Will result in  1.23E+78 

  The issue here is that if the result of the VAL() ends up as a number that
Access can't handle, you'll get all #Error's in the output.

  Your best bet when you need to do this is to loop through the field data
character by character building up the number to be converted, then convert
it.  That way you can impose limits/checks on the data rather than relying
on VAL()'s built-in logic.

Jim.

 

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
John R Bartow
Sent: Wednesday, December 16, 2015 10:58 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] less than on text field
Importance: High

I tried that too, no go. Maybe it's an A2013 thing? 

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Paul Hartland
Sent: Tuesday, December 15, 2015 11:45 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] less than on text field

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-Ex
> plorer
>
> -----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
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com