[AccessD] less than on text field

[John R Bartow]

Gary,
Good thought - tried that with the same resulting error: "Data Type Mismatch
in criteria expression"

That's what is frustrating, the Val() obviously does the conversion to
numeric when creating a new table with the data but it doesn't seem to work
the same during the query.

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

Would it work if you had one query doing the val function converting it to
numerics and then using that query as the source for a second query?

GK

On Wed, Dec 16, 2015 at 1:56 PM, John R Bartow <jbartow at winhaven.net> wrote:
> 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



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