SIMPLE but Can't find Syntax Error

[bobbye_69]

Trying to parse first two sections of a five section string. Now getting a SYNTAX COMMA ERROR. Can someone take a look and tell me what's wrong. I've looked at it TOO long. Thanks

Left((([ArborID],InStr([ArborID],"-")+1,(InStr(InStr([ArborID],"-")+1,[ArborID],"-")-InStr([ArborID],"-"))-1)),10)

 

[plog]

I see it--you're trying to do too much in one line of code.

Create a function to parse this, pass it ArborID, then use as many lines and variables as you need to return the value you want. You can even catch Nulls in that function and return a default value; whereas with this, a Null will blow it up.

 

[bobbye_69]

How is that one too much when I get this to work perfectly.
MaterialCode: IIf(Left([Description_5],1)="8",(Mid([Description_5],InStr([Description_5],"-")+1,(InStr(InStr([Description_5],"-")+1,[Description_5],".")-InStr([Description_5],"-"))-1)),999)

 

[plog]

False equivalence: http://en.wikipedia.org/wiki/False_equivalence

First, its obviously too much because you can't get it to work. Second, that abomination should be a function as well.

People aren't computers, we can't parse as well as they can. There's a reason books have spaces between words, punctuation, capitalization, indentions and chapter breaks. It helps us see what is being communicated. Your two lines of code with nth level nesting make that hard. Build a function, make it more easier for a human to read. In doing so, you can even set debug points to make sure its doing what you expect at certain moments and find the specific instance it fails.

 

[bobbye_69]

Okay. Thank you.
I'll try to build a function but haven't done one for something like this. If you have any examples or know where I can look I would appreciate it.
Thanks again.

 

[plog]

Your code will become your function--you've done 95% of the work, its just a matter of finding where its failing. Here's some code to get you started:

Public Function get_ArborID(in_ID)
    ' takes [ArborID] value, and extracts correct portion from it


    ' Left((([ArborID],InStr([ArborID],"-")+1,(InStr(InStr([ArborID],"-")+1,[ArborID],"-")-InStr([ArborID],"-"))-1)),10)
    ' initial code above


ret="Error"
    ' return value, by default will show Error


if (len(in_ID)>0) then
    ' if in_ID contains a string, will process it

    int_FirstDash=InStr(in_ID,"-")
    ret = "First Dash At Position: " & int_FirstDash

End If

get_ArborID=ret

End Function

Paste the above into a module, then to use it in a query, bring in whatever table has [ArborID], bring down [ArborID] and then make a field like this:

CorrectArborID: get_ArborID([ArborID])

Run the query and the field CorrectArborID will execute your function and either show an error if the value is null, or show you where that first dash is at. From there, keep adding code to rebuild the functionality of that initial code.

 

[bobbye_69]

I am getting a "Variable Not Defined" on ret = "Error" line.

 

[plog]

put this on the line above it:

Dim ret As String

 

[bobbye_69]

Same error on " int_FirstDash = " line.

 

[vbaInet]

Trying to parse first two sections of a five section string. Now getting a SYNTAX COMMA ERROR. Can someone take a look and tell me what's wrong. I've looked at it TOO long. Thanks

Left((([ArborID],InStr([ArborID],"-")+1,(InStr(InStr([ArborID],"-")+1,[ArborID],"-")-InStr([ArborID],"-"))-1)),10)

Instead of showing us code that doesn't work why don't you show us examples of the string followed by the parts you're trying to extract.

 

[pbaldy]

FYI, thread moved to Queries from Watercooler, which is just for non-Access discussions.

 

[bobbye_69]

Below is what I have in my table in field ArborID. I am trying to get a field in my query that would show what is depicted in the second group below.

ArborID
ABC-10-BC-KIN-01
ABC-20-AC-KIN-01
ABC-25-AC-KIN-01
ABC-25-AC-KIN-02
DEF-40-BC-KIN-01

ArborID
ABC-10
ABC-20
ABC-25
DEF-40
(* Notice the Unique Values and only one ABC-25 ... record)

 

[vbaInet]

Don't worry about suppressing duplicates just yet, that's something easy for you to fix.

Will the string always begin with three letters followed by a hyphen?

 

[bobbye_69]

No.
The first section could be ABC or DEFG.
The second could be 10, 20, 120, 130

 

[vbaInet]

Ok, just use the Split() function using "-" as your delimeter and concatenate the first two items.