[AccessD] less than on text field

[John R Bartow]

Hi Bob,
I did try that and got the same result of error: "Data Type Mismatch in
criteria expression"

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

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.

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