Access question

[andrew.menendez]

Hopefully this will be easy for some people.

I'm trying to write an If statement in VBA for access, however I think
I am formatting it wrong. What I am logically trying to do is this: If
the first 6 numbers in this given field does not equal 000001 or
000002, then carry out the rest of the function. Currently I have it
formatted like this:

If IdentificationNumber <> 000001 Or IdentificationNumber <> 000002
Then

Any suggestions? Thanks!

-Jimmy

 

[Guest]

First, change the "Or" to "And"

Think about it a moment, IdentificationNumber = "000002" and you test
If IdentificationNumber <> 000001 is true, it doesn't, but
If IdentificationNumber <> 000002 is false, it is equal
because one of the two conditions was true the "Then" portion will be
executed.

My other question is what type of data is IdentificationNumber? If it is
numeric, then it really doesn't have leading zeros - internally it would just
be 1 or 2 although with formatting it may appear with the leading zeros.

If it is a numeric field and there's no chance of the value being zero, then
you could also write the test as:
If IdentificationNumber > 2 Then

If the IdentificationNumber field is text, then you need to enclose the test
values within quotes as <> "000001" and <> "000002"

You mentioned the "first 6 numbers" - by "first" do you mean the leading 6
characters from the left or right. With text you'd normally be referring to
the 1st 6 from the left, but with numbers could be looked at differently.

If it is a text field, then you may need to do this to IdentificationNumber:

If Left(IdentificationNumber,6) <> "000001" And Left(IdentificationNumber,6)
<> "000002" Then

or again, rewritten as:
If Left(IdentificationNumber,6) > "000002" Then

Hope this helps some.