[AccessD] less than on text field

[Jim Dettman]

 I guess I'm a bit dense here, but I still don't understand what the problem
is exactly

 Do you get more records than expected, not enough, etc.

 If you add the check of <11, to the SQL below, it should come back with:

Windows Internet Explorer	10.0.9200.17492	10
Windows Internet Explorer	9.0.8112.16421	9
Windows Internet Explorer	6.0.2900.5512	6

 and I don't understand why what your doing would not work.

Jim.

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

Hi Jim,
Thanks for the comments. 
 Issue was (and remains) that I cannot filter the field "VerNum". I had
wanted to filter it to display records less than 11.
The query is:
SELECT client.name, site.name, as_device.name, as_catalog.name,
as_software.version, Val([as_software]![version]) AS VerNum
FROM client INNER JOIN (site INNER JOIN ((as_device INNER JOIN as_software
ON as_device.deviceid = as_software.deviceid) INNER JOIN as_catalog ON
as_software.catalogid = as_catalog.catalogid) ON site.siteid =
as_device.siteid) ON client.clientid = as_device.clientid
WHERE (((as_catalog.name)="Windows Internet Explorer"));

The text field is actually all numbers in the _filtered_ results. But may
not be in that field throughout the table. Could it be an issue that Val()
is evaluated before the filter is applied? Other software is listed in the
table and may contain non numeric data.

The results do come back a bit odd. Here is a subset (some of the column
spacing are a bit off):
as_catalog.name	version	VerNum
Windows Internet Explorer	11.11.10586.0	11.11
Windows Internet Explorer	11.0.9600.18097	11
Windows Internet Explorer	11.0.9600.18053	11
Windows Internet Explorer	11.0.10240.16590	11
Windows Internet Explorer	11.0.9600.17914	11
Windows Internet Explorer	10.0.9200.17492	10
Windows Internet Explorer	9.0.8112.16421	9
Windows Internet Explorer	6.0.2900.5512	6

This shows that, apparently, once Val() gets to a zero it stops, hence the
11.11 listing. That's seems inconsistent.

However, when I created a new table from this query it saved the VerNum
field as double. I converted it to Integer without complaint. No rows lost.

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


 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.


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