Need help with Instr Function

[Guest]

I compare billing data with a mdb using queries that someone helped me
create. I'm trying to understand this expression -
Not Like Left([SH_Description],InStr(1,[SH_Description],"SUPPLY",1)+6) & "*"
I dont understand the ,1)+6)&"*" it was written to not compare text beyond
the word supply - but I dont understand what the +6 is telling it to do?
I have read the Instr function help but I'm very confused.

 

[Ken Snell [MVP]]

This part of the expression:

InStr(1,[SH_Description],"SUPPLY",1)+6

is defiining the number of characters that the LEFT function is to select.

So, what the expression does is this: start at the first character in the
[SH_Description] field; find the text string "SUPPLY" (ignore the case of
the letters [that is what the 1 means]), then add 6 to the character
location where the S of SUPPLY is found -- thus, the +6 moves to the first
character after the Y of SUPPLY.

 

[Ken Snell [MVP]]

Sorry -- mistakenly sent before I finished:

This part of the expression:

InStr(1,[SH_Description],"SUPPLY",1)+6

is defiining the number of characters that the LEFT function is to select.

So, what the expression does is this: start at the first character in the
[SH_Description] field; find the text string "SUPPLY" (ignore the case of
the letters [that is what the 1 means]), then add 6 to the character
location where the S of SUPPLY is found -- thus, the +6 moves to the first
character after the Y of SUPPLY.

This means that the LEFT function selects all characters to the left of
where SUPPLYoccurs in the string, plus the string SUPPLY, plus the very next
character. So, let's assume that [SH_Description] contained this text
string:
"KenMVPSupply512345"

Your expression

Not Like Left([SH_Description],InStr(1,[SH_Description],"SUPPLY",1)+6) & "*"

would look like this when the query runs:

Not Like "KenMVPSupply5" & "*"

--

Ken Snell
<MS ACCESS MVP>

This part of the expression:

InStr(1,[SH_Description],"SUPPLY",1)+6

is defiining the number of characters that the LEFT function is to select.

So, what the expression does is this: start at the first character in the
[SH_Description] field; find the text string "SUPPLY" (ignore the case of
the letters [that is what the 1 means]), then add 6 to the character
location where the S of SUPPLY is found -- thus, the +6 moves to the first
character after the Y of SUPPLY.

--

Ken Snell
<MS ACCESS MVP>

I compare billing data with a mdb using queries that someone helped me
create. I'm trying to understand this expression -
Not Like Left([SH_Description],InStr(1,[SH_Description],"SUPPLY",1)+6) &
"*"
I dont understand the ,1)+6)&"*" it was written to not compare text
beyond
the word supply - but I dont understand what the +6 is telling it to do?
I have read the Instr function help but I'm very confused.

 

[Guest]

Ken, thank you so much that really helped me - I read the help in Access
queries for the function but it just dosent put it as simple as you did.
I really appreciate your taking the time to put it in laymens terms.

Have a nice day.

Sorry -- mistakenly sent before I finished:

This part of the expression:

InStr(1,[SH_Description],"SUPPLY",1)+6

is defiining the number of characters that the LEFT function is to select.

So, what the expression does is this: start at the first character in the
[SH_Description] field; find the text string "SUPPLY" (ignore the case of
the letters [that is what the 1 means]), then add 6 to the character
location where the S of SUPPLY is found -- thus, the +6 moves to the first
character after the Y of SUPPLY.

This means that the LEFT function selects all characters to the left of
where SUPPLYoccurs in the string, plus the string SUPPLY, plus the very next
character. So, let's assume that [SH_Description] contained this text
string:
"KenMVPSupply512345"

Your expression

Not Like Left([SH_Description],InStr(1,[SH_Description],"SUPPLY",1)+6) & "*"

would look like this when the query runs:

Not Like "KenMVPSupply5" & "*"

--

Ken Snell
<MS ACCESS MVP>

This part of the expression:

InStr(1,[SH_Description],"SUPPLY",1)+6

is defiining the number of characters that the LEFT function is to select.

So, what the expression does is this: start at the first character in the
[SH_Description] field; find the text string "SUPPLY" (ignore the case of
the letters [that is what the 1 means]), then add 6 to the character
location where the S of SUPPLY is found -- thus, the +6 moves to the first
character after the Y of SUPPLY.

--

Ken Snell
<MS ACCESS MVP>

I compare billing data with a mdb using queries that someone helped me
create. I'm trying to understand this expression -
Not Like Left([SH_Description],InStr(1,[SH_Description],"SUPPLY",1)+6) &
"*"
I dont understand the ,1)+6)&"*" it was written to not compare text
beyond
the word supply - but I dont understand what the +6 is telling it to do?
I have read the Instr function help but I'm very confused.