criteria in a query 2

[joeythelips]

Hi,

I want to return only numbers that are 9 digits long, and also want to eliminate dates from the same filed.

Anyone any ideas?

 

[tempclerk]

Len(Cstr([Field Name]))=9 should work for both?

 

[joeythelips]

Thanks for that.
However I am getting a message saying "invalid use of null" wheni run the query??

 

[tempclerk]

Probably can't convert null to a string. Try Len(Cstr(Nz([Field Name])))=9

 

[JoeMiller]

Add a column to your query by putting this in the "Field:" row of the QBE:

FieldLength: Len(Cstr(Nz([MyFieldName])))

Now you'll have a new column that shows the character length of every value in MyFieldName. Now in the criteria down below FieldLength, type in 9 so that you only get values where MyFieldName is 9 characters long. Since a date is not 9 characters long, it won't be returned.

HTH Joe Miller
joe.miller@flotech.net