[AccessD] less than on text field

[Bob Walsh]

Since 11 is your highest number, look at the leftmost two characters  and do your compare as NOT EQUAL

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John R Bartow
Sent: Wednesday, December 16, 2015 11:57 AM
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.nameversionVerNum
Windows Internet Explorer11.11.10586.011.11
Windows Internet Explorer11.0.9600.1809711
Windows Internet Explorer11.0.9600.1805311
Windows Internet Explorer11.0.10240.1659011
Windows Internet Explorer11.0.9600.1791411
Windows Internet Explorer10.0.9200.1749210
Windows Internet Explorer9.0.8112.164219
Windows Internet Explorer6.0.2900.55126

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
IMPORTANT NOTE: The information contained in this message may be privileged, confidential, and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately and delete this message from your computer. Acumentra Health.