Trying to combine a Not statement with a substring

[Guest]

I am trying to create a substring that will only recognize the 3 and 5
characters of a set of numbers that will exclude these numbers when I run the
query. I am not sure what my syntax should be though. Possibly
substr(to_char(hours.projectNumber),3,5) <> "0599998" And
substr(to_char(hours.projectNumber),3,5) <> "0599999" ? Also where do I
enter the syntax, in SQL or could I do it in design view?

 

[Guest]

Ok let me rephrase the question since Access uses left, right and mid
functions instead. I am trying to create a substring that will only recognize
the 3 and 5
characters of a set of numbers that will exclude these numbers when I run the
query. I am not sure what my syntax should be though. Would it be
=mid([fieldname],3,5) <>"0599998" And mid([fieldname],3,5)<>"0599999" ?

 

[Guest]

Oprn the query in design view and enter this as your criteria --
Not Like "*3*" and Not Like "*5*"

This will not pull any record that has a 3 or 5 anywhere in the field.
To only eliminate those records that have the digit in a particular position
use the above but replace the asterick (*) with a string of question marks
(?) for the number of charters. Example in the third position --
Not Like "??3*" and Not Like "??5*"

 

[MacDermott]

Sorry -
I'm still not clear what you're trying to do.
You have a "set of numbers" - is this stored as a string, or as an integer
(long)?
If it's not already a string, I'd recommend the CStr() function to turn it
into one.

Now you want to "only recognize the 3 and 5 characters" -
Does this mean the 3rd and 5th characters of the string? From the left?
The 3rd character (of MyString) from the left is Mid(MyString,3,1); the
5th character is Mid(MyString,5,1).

I also understand that you want to exclude some values of MyString, but I'm
baffled as to the relationship between the 3rd and 5th characters and the
7-digit strings you offer for comparison.

Could you try again?

 

[Guest]

Ok this question is asking for two things. Sorry about the vagueness but what
I want the query to do is to only read the numbers between the 3rd and 7th
characters. The number is 0599998, I want it to exclude the first two numbers
because it represents a year and that will always change. I only want the
query to look up "99998" instead of "0599998".


After I get it to not lookup the first two numbers of the 7 digits, I want
to exclude all of the numbers when I run a search. I was able to get this
formula to work by typing: <>"0599998" However back to the first section, I
want it to exclude the first two digits from the search.

Thanks for your help

 

[MacDermott]

If I understand you correctly, you can put
hours.projectNumber
in the (top) fieldname row of the query grid,
then use a criterion like this:
Not Like "??99998"

I'm assuming since the examples you give have a leading 0, they are already
in a text format.

 

[Guest]

Thank you so much!

If I understand you correctly, you can put
hours.projectNumber
in the (top) fieldname row of the query grid,
then use a criterion like this:
Not Like "??99998"

I'm assuming since the examples you give have a leading 0, they are already
in a text format.