[AccessD] less than on text field

[Paul Hartland]

I don't know much about Access past 2007 and at the moment only have access
to a copy of 2003, so not sure if what I put will work.  I created a test
table in SQL Server 2014 express with a field called test of type
nvarchar(50), nvarchar being an equivalent type to text in MySQL and put a
singe record in with the value 11, I then linked the table to Access and
created a query to show records < 12 and it worked, when I went into the
SQL it looked like this

SELECT dbo_tblTest.test
FROM dbo_tblTest
WHERE (((dbo_tblTest.test)<"12"));

hope this helps, note that it does not use the ! but a . instead between
table and field names.

On 15 December 2015 at 22:36, John R Bartow <jbartow at winhaven.net> wrote:

> The second one is what I tried. It returns "Data Type Mismatch in Criteria
> Expression".
>
> Now that I tried it again, it doesn't matter what I put in the criteria
> field, it returns the same error.
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Gary Kjos
> Sent: Tuesday, December 15, 2015 3:36 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] less than on text field
>
> Don't know about MySQL but wouldn't it be
>
> [as_software]![version] < '11'
>
> since it's a text field
>
> Or
>
> Val([as_software]![version]) < 11
>
> which would give you the numeric value of the characters in the text field
> ?
>
> On Tue, Dec 15, 2015 at 1:46 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
>
>
>
> --
> Gary Kjos
> garykjos at gmail.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
>



-- 
Paul Hartland
paul.hartland at googlemail.com