Len([field])<9 return records with values of 10

[Mike]

Last I checked, 10 was MORE than 9.

How can the string actually return a value of 10 in one area and be listed
in a query using <9 ??

 

[Michel Walsh]

You probably have alphanumerical value.

'AZ' comes before 'Z', right? so '19' comes before '9' (change A with 1
and Z with 9).

'19' < '9'

but

19 > 9

and

'19' > '09'



You can try

val( fieldstring) < 9



where the pre-defined function val returns the number starting the string:

? val( "10abc" )
10




Next time, be sure you are using values, not strings



Vanderghast, Access MVP

 

[KARL DEWEY]

Post your complete SQL statement.

 

[Mike]

I see what you're saying, and since the field is formatted as text, it makes
sense to me. I had wondered about that earlier today, becuase it was the
only thing that explained it.

Now, i'm just having issues with combining val and len.

I tried val(len([field]))>9 and I get data type mismatch...unless you can't
do it that way in a query. Creating a separate expression column in the
query and simply using >9 works though, just wanted to keep it in the same
column.

I was using this to see if there was a problem with my existing field. I
have a combobox that uses code to look for a record and if it finds it, goes
to that record. If it doesn't find it - it prompts you to create a new
record. Used to work fine when i had my 5 test records in the db. But once i
added the old data - it doesn't work when the combo box has an input mask. I
was checking the fields to make sure there weren't hidden characters or
anything. I was starting to wondering if there was really something wrong
with the existing data for that field.

Still can't make the input mask part work, but that's another issue

Thanks!
mike

 

[Michel Walsh]

If you use LEN( fieldName ) all alone, as expression, then it should return
a number, NOT a string. But if you concatenate the result with another
string, then it becomes a string:

? len("abc") & " inches"
3 inches


Not seeing your whole SQL statement, it is hard to see if it is a string or
a number. It is also possible that somehow, invisible characters have been
added to your values, so even if you see 9 characters, you may have 10 of
them. Note that TRIM( string ) removes the starting and ending spaces, if
that can help.


? "-" & TRIM(" hello " ) & "-"
-hello-

? "-" & " hello " & "-"
- hello -


Vanderghast, Access MVP

I see what you're saying, and since the field is formatted as text, it
makes sense to me. I had wondered about that earlier today, becuase it was
the only thing that explained it.

Now, i'm just having issues with combining val and len.

I tried val(len([field]))>9 and I get data type mismatch...unless you
can't do it that way in a query. Creating a separate expression column in
the query and simply using >9 works though, just wanted to keep it in the
same column.

I was using this to see if there was a problem with my existing field. I
have a combobox that uses code to look for a record and if it finds it,
goes to that record. If it doesn't find it - it prompts you to create a
new record. Used to work fine when i had my 5 test records in the db. But
once i added the old data - it doesn't work when the combo box has an
input mask. I was checking the fields to make sure there weren't hidden
characters or anything. I was starting to wondering if there was really
something wrong with the existing data for that field.

Still can't make the input mask part work, but that's another issue

Thanks!
mike

You probably have alphanumerical value.

'AZ' comes before 'Z', right? so '19' comes before '9' (change A with
1 and Z with 9).

'19' < '9'

but

19 > 9

and

'19' > '09'



You can try

val( fieldstring) < 9



where the pre-defined function val returns the number starting the
string:

? val( "10abc" )
10




Next time, be sure you are using values, not strings



Vanderghast, Access MVP

 

[Mike]

Cut and pasted from the query screen (right click, SQL view)

SELECT customers.Phone, customers.Pext, customers.CustID,
customers.FirstName, customers.LastName, customers.email, customers.Address,
customers.City, customers.State, customers.Zip, customers.History
FROM customers
WHERE ((Len([Phone])>"9"))
ORDER BY customers.Phone;

FWIW, the column in the query view is

Field: Phone
table: customers
Sort: Ascending
Show: checked
Criteria: Len([Phone])>"'9"

If you use LEN( fieldName ) all alone, as expression, then it should
return a number, NOT a string. But if you concatenate the result with
another string, then it becomes a string:

? len("abc") & " inches"
3 inches


Not seeing your whole SQL statement, it is hard to see if it is a string
or a number. It is also possible that somehow, invisible characters have
been added to your values, so even if you see 9 characters, you may have
10 of them. Note that TRIM( string ) removes the starting and ending
spaces, if that can help.


? "-" & TRIM(" hello " ) & "-"
-hello-

? "-" & " hello " & "-"
- hello -


Vanderghast, Access MVP

I see what you're saying, and since the field is formatted as text, it
makes sense to me. I had wondered about that earlier today, becuase it was
the only thing that explained it.

Now, i'm just having issues with combining val and len.

I tried val(len([field]))>9 and I get data type mismatch...unless you
can't do it that way in a query. Creating a separate expression column in
the query and simply using >9 works though, just wanted to keep it in the
same column.

I was using this to see if there was a problem with my existing field. I
have a combobox that uses code to look for a record and if it finds it,
goes to that record. If it doesn't find it - it prompts you to create a
new record. Used to work fine when i had my 5 test records in the db. But
once i added the old data - it doesn't work when the combo box has an
input mask. I was checking the fields to make sure there weren't hidden
characters or anything. I was starting to wondering if there was really
something wrong with the existing data for that field.

Still can't make the input mask part work, but that's another issue

Thanks!
mike

You probably have alphanumerical value.

'AZ' comes before 'Z', right? so '19' comes before '9' (change A with
1 and Z with 9).

'19' < '9'

but

19 > 9

and

'19' > '09'



You can try

val( fieldstring) < 9



where the pre-defined function val returns the number starting the
string:

? val( "10abc" )
10




Next time, be sure you are using values, not strings



Vanderghast, Access MVP



Last I checked, 10 was MORE than 9.

How can the string actually return a value of 10 in one area and be
listed in a query using <9 ??

 

[Mike]

Crap, i may have figured this out.

len([Phone])<Val(10) seems to be working. I was wondering about it changing
all the >#'s to >"#" - it was treating the number itself as a string and not
a number. Telling to look for a val(10) or whatever seems to set it
straight.

Does that make sense to eveyone?

Cut and pasted from the query screen (right click, SQL view)

SELECT customers.Phone, customers.Pext, customers.CustID,
customers.FirstName, customers.LastName, customers.email,
customers.Address, customers.City, customers.State, customers.Zip,
customers.History
FROM customers
WHERE ((Len([Phone])>"9"))
ORDER BY customers.Phone;

FWIW, the column in the query view is

Field: Phone
table: customers
Sort: Ascending
Show: checked
Criteria: Len([Phone])>"'9"

If you use LEN( fieldName ) all alone, as expression, then it should
return a number, NOT a string. But if you concatenate the result with
another string, then it becomes a string:

? len("abc") & " inches"
3 inches


Not seeing your whole SQL statement, it is hard to see if it is a string
or a number. It is also possible that somehow, invisible characters have
been added to your values, so even if you see 9 characters, you may have
10 of them. Note that TRIM( string ) removes the starting and ending
spaces, if that can help.


? "-" & TRIM(" hello " ) & "-"
-hello-

? "-" & " hello " & "-"
- hello -


Vanderghast, Access MVP

I see what you're saying, and since the field is formatted as text, it
makes sense to me. I had wondered about that earlier today, becuase it
was the only thing that explained it.

Now, i'm just having issues with combining val and len.

I tried val(len([field]))>9 and I get data type mismatch...unless you
can't do it that way in a query. Creating a separate expression column
in the query and simply using >9 works though, just wanted to keep it in
the same column.

I was using this to see if there was a problem with my existing field. I
have a combobox that uses code to look for a record and if it finds it,
goes to that record. If it doesn't find it - it prompts you to create a
new record. Used to work fine when i had my 5 test records in the db.
But once i added the old data - it doesn't work when the combo box has
an input mask. I was checking the fields to make sure there weren't
hidden characters or anything. I was starting to wondering if there was
really something wrong with the existing data for that field.

Still can't make the input mask part work, but that's another issue

Thanks!
mike

You probably have alphanumerical value.

'AZ' comes before 'Z', right? so '19' comes before '9' (change A
with 1 and Z with 9).

'19' < '9'

but

19 > 9

and

'19' > '09'



You can try

val( fieldstring) < 9



where the pre-defined function val returns the number starting the
string:

? val( "10abc" )
10




Next time, be sure you are using values, not strings



Vanderghast, Access MVP



Last I checked, 10 was MORE than 9.

How can the string actually return a value of 10 in one area and be
listed in a query using <9 ??

 

[John W. Vinson]

Cut and pasted from the query screen (right click, SQL view)

Just remove the quotes on the criterion:

SELECT customers.Phone, customers.Pext, customers.CustID,
customers.FirstName, customers.LastName, customers.email, customers.Address,
customers.City, customers.State, customers.Zip, customers.History
FROM customers
WHERE ((Len([Phone])>9))
ORDER BY customers.Phone;

Len() returns a number; you want to compare it with a number, not with a text
string.

John W. Vinson [MVP]

 

[Mike]

My post after that explains what I had to do to fix it.

In column mode in the query i can't make it take the statement without
quotes around the '9'. I take them off, hit enter or tab or whatever and it
sticks them back. If i take it out of the sql statement directly, will they
stick?

Cut and pasted from the query screen (right click, SQL view)

Just remove the quotes on the criterion:

SELECT customers.Phone, customers.Pext, customers.CustID,
customers.FirstName, customers.LastName, customers.email,
customers.Address,
customers.City, customers.State, customers.Zip, customers.History
FROM customers
WHERE ((Len([Phone])>9))
ORDER BY customers.Phone;

Len() returns a number; you want to compare it with a number, not with a
text
string.

John W. Vinson [MVP]

 

[John W. Vinson]

My post after that explains what I had to do to fix it.

Well, if it works, I guess... but I can't see why you should need to use
Val(10) instead of just 10 (or for that matter > Val(9) instead of just >9).

In column mode in the query i can't make it take the statement without
quotes around the '9'. I take them off, hit enter or tab or whatever and it
sticks them back. If i take it out of the sql statement directly, will they
stick?

Well, I don't know. Try it. I don't know why it should be putting in quotes
for you anyway. Is this 2007? All service packs applied? Have you compacted
the database?

John W. Vinson [MVP]

 

[Gary Walter]

:

FWIW, the column in the query view is

Field: Phone
table: customers
Sort: Ascending
Show: checked
Criteria: Len([Phone])>"'9"

<snip>

Hi Mike,

I think above grid may be why
you were having problems.

Usually when you want to apply
a criteria to a calculation, you
use another column in the grid
for the calculation...

Field: Phone Len([Phone])
table: customers
Sort: Ascending
Show: checked
Criteria: >9

Access was using its "Intellisense"
knowing that the Field Phone was
text, so Criteria 9 must need to be
wrapped in quotes.

does that make sense?

good luck,

gary

 

[Mike]

It makes perfect sense.

At one point, as I mentioned, i made my own column to do the criteria as
mentioned below. I can't take all the credit for that idea because access
did it itself a couple times.

And doing it that way, it did work.

I've always just been a personal fan of having the formula in the column
it's sorting. I guess I need to get used to doing it the proper way, eh?

Thanks to all!

Mike

:

FWIW, the column in the query view is

Field: Phone
table: customers
Sort: Ascending
Show: checked
Criteria: Len([Phone])>"'9"

<snip>

Hi Mike,

I think above grid may be why
you were having problems.

Usually when you want to apply
a criteria to a calculation, you
use another column in the grid
for the calculation...

Field: Phone Len([Phone])
table: customers
Sort: Ascending
Show: checked
Criteria: >9

Access was using its "Intellisense"
knowing that the Field Phone was
text, so Criteria 9 must need to be
wrapped in quotes.

does that make sense?

good luck,

gary