[AccessD] less than on text field

[Gary Kjos]

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