[AccessD] less than on text field

[Bill Benson]

So you may need to add NZ (..., '') to the formula.
On Dec 16, 2015 6:18 PM, "John R Bartow" <jbartow at winhaven.net> wrote:

> Bill,
> CLNG (LEFT ( [as_software]![version],2)) worked for setting the field to an
> whole number. And it did sort as numeric - so great for that purpose!
>
> However, any criteria I set to filter the results ends with the Error:
> "Invalid use of null".
>
> And BTW this is straight Access data now. To troubleshoot this issue I
> imported all of the data into Access so there are no MySQL concerns. It'd
> be
> nice to have it query against MySQL data and avoid the extra step of
> importing it because the data has to be loaded into MySQL from a dump file
> to start with which takes a couple of hours.
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Bill Benson
> Sent: Wednesday, December 16, 2015 4:45 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] less than on text field
>
> If you use <11 I think you'll be good. Maybe not. It depends if clng ("9.")
> becomes 9 or produces an error.
> It also will goof up if there is more than version 99, because 100 will
> become 10. You can play with INSTR (OR my sql equivalent if not using
> native
> Access query) to check for "."
>
> 11.x will be 11 and skipped
> 10.x will be 10 and included
> 3.xx will be 3. Etc... unless the . throws an error.
>
> Untested as I don't have MySQL, and am not at a 'puter.
> On Dec 15, 2015 2:47 PM, "John R Bartow" <jbartow at winhaven.net> wrote:
>
> > I have a text field "version" in a MySQL DB, I connect to the DB with
> > A2013, I make a query of the data and only want to show the results
> > where version is less than 11.
> >
> > VerNum: Val([as_software]![version]) returns an integer - why can't I
> > filter this with the criteria: < 11 ?
> >
> >
> >
> > TIA
> >
> > John
> >
> >
> >
> >
> > --
> > 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
>