[AccessD] less than on text field

Wed Dec 16 16:51:47 CST 2015

Bob,
I cannot add any type of comparison to the field without getting the same
error.

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

You  are getting the error because the way you have VerNum defined it is a
string field not a numeric field.
So either add quotes around the 11 and compare for not equal OR convert that
value to a numeric

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

Well then I'm in good company because I am thinking the same thing ;-)

The problem is that when I add <11 it results in error: "Data Type Mismatch
in criteria expression" and the query will not run.

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

 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 Explorer10.0.9200.1749210 Windows Internet
Explorer9.0.8112.164219 Windows Internet Explorer6.0.2900.55126

 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.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

--
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 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