need to find all records that end with 001 and higher

[cdoyle]

Hi,

I know I've seen this done before, but now I can't find it.

I have a field in my database that has a 13 digit number, I need to find all records that end with 001, 002, 003 etc. Basically I need to find all records that don't end with 000.

I'm trying to recall how it's done, I thought what you did was write the query so it excludes the first 10 numbers and only looks at the last 3. Is that right?

 

[cdoyle]

OK, not sure if this is right but what I did is

put this in my criteria.
Not Like "******000"

That seems to work, but now I have something else I need to do.
All the records that this query finds, there are a matching record that does end with 000.

for example

0054541002
would have a matching.
0054541000

So I need to create a query using the first query, that will now pull all the 000's

how would I do this?

 

[Mr. B]

Try creating a new field in your query. Like the following:

Chars: Right([YourTableName]![YourFieldName],3)>001

When you test this you may need to convert the returned value from the Right function to a string so you can compare it to the "001" string.

HTH

 

[gemma-the-husky]

when you see you have a 13 digit number, do you really mean a 13 digit text value?

if not, what data type is your 13 digit number (since 13 digits exceeds a longint)

if its text, then you can just use simply right(numberstring,3)

 

[raskew]

Given the examples, with the 'number' starting with '00', it's not a number -- it must be a text field. So, val(right([yourfield], 3)) > 0 would allow you to determine the value of the right(3) characters.

Bob

 

[cdoyle]

Hi,
A friend asked me about how to do this, and I was assuming it was a number field, but once I tried to test it on a test db, I realized it had to be a text field.

I was able to get it to work using the right function.

Thank you everyone.

 

[namliam]

RIght(yourfield,3) > "000" will also work, but will also allow for "AAA" for example...