#Error

[SJW_OST]

I am using the following formula in my Query.

Correct: Left$([List Name],InStrRev([List Name],"_")-1)

I am using this formula to convert some data to the following;

"aaaa_hi_e_28" converts to "aaaa_hi_e" or
"aaaa_e_28" converts to "aaaa_e" or
"aaaa_hi_e_0528" converts to "aaaa_hi_e"

But, if I have some data that does not have an underscore & "date" at the
end, like D86547, I get #Error. How do I change the formula to just use the
original name if #Error occurs?

Thank you in advance.

 

[Michel Walsh]

prepend one, InStrRev( "_" & [List Name] , "_") so there will be always
one.

Alternatively, use iif to test the result of InStrRev, and if it returns 0,
let your iif return 1 instead (or whatever the logic dictates).



Vanderghast, Access MVP

 

[KARL DEWEY]

I think you need to go with the IIF method as I do believe that 'prepending'
one will return a zero lenght string.

Correct: IIF(InStrRev([List Name], "_")=0, [List Name], Left$([List Name],
InStrRev([List Name],"_")-1))

--
KARL DEWEY
Build a little - Test a little

prepend one, InStrRev( "_" & [List Name] , "_") so there will be always
one.

Alternatively, use iif to test the result of InStrRev, and if it returns 0,
let your iif return 1 instead (or whatever the logic dictates).



Vanderghast, Access MVP

I am using the following formula in my Query.

Correct: Left$([List Name],InStrRev([List Name],"_")-1)

I am using this formula to convert some data to the following;

"aaaa_hi_e_28" converts to "aaaa_hi_e" or
"aaaa_e_28" converts to "aaaa_e" or
"aaaa_hi_e_0528" converts to "aaaa_hi_e"

But, if I have some data that does not have an underscore & "date" at the
end, like D86547, I get #Error. How do I change the formula to just use
the
original name if #Error occurs?

Thank you in advance.

 

[SJW_OST]

Ok, so that eliminated the #Error result, if D86547 is an original name. It
now leaves that result blank. I need for it to pull in the original name
also. So if D86547 is an original name I need the same formula to pull
D86547. So with this one formula;

"aaaa_hi_e_28" converts to "aaaa_hi_e"
"D86547" remains "D86547"

Thank you for your continued assistance.

prepend one, InStrRev( "_" & [List Name] , "_") so there will be always
one.

Alternatively, use iif to test the result of InStrRev, and if it returns 0,
let your iif return 1 instead (or whatever the logic dictates).



Vanderghast, Access MVP

I am using the following formula in my Query.

Correct: Left$([List Name],InStrRev([List Name],"_")-1)

I am using this formula to convert some data to the following;

"aaaa_hi_e_28" converts to "aaaa_hi_e" or
"aaaa_e_28" converts to "aaaa_e" or
"aaaa_hi_e_0528" converts to "aaaa_hi_e"

But, if I have some data that does not have an underscore & "date" at the
end, like D86547, I get #Error. How do I change the formula to just use
the
original name if #Error occurs?

Thank you in advance.

 

[SJW_OST]

That did it! Thank you Karl Dewey and thank you goes to Michel Walsh and
S.Clark as well. All you guys have been a great help. Thank you ver much!

I think you need to go with the IIF method as I do believe that 'prepending'
one will return a zero lenght string.

Correct: IIF(InStrRev([List Name], "_")=0, [List Name], Left$([List Name],
InStrRev([List Name],"_")-1))

--
KARL DEWEY
Build a little - Test a little

prepend one, InStrRev( "_" & [List Name] , "_") so there will be always
one.

Alternatively, use iif to test the result of InStrRev, and if it returns 0,
let your iif return 1 instead (or whatever the logic dictates).



Vanderghast, Access MVP

I am using the following formula in my Query.

Correct: Left$([List Name],InStrRev([List Name],"_")-1)

I am using this formula to convert some data to the following;

"aaaa_hi_e_28" converts to "aaaa_hi_e" or
"aaaa_e_28" converts to "aaaa_e" or
"aaaa_hi_e_0528" converts to "aaaa_hi_e"

But, if I have some data that does not have an underscore & "date" at the
end, like D86547, I get #Error. How do I change the formula to just use
the
original name if #Error occurs?

Thank you in advance.

 

[S.Clark]

Normally, I would use InStr() to check for the existence of the "_", to know
whether to process.

e.g. IIF(Instr([field], "_") >0, do this, do that)

But, given that you have other underscores in the name, you're going to need
to soul search to determine when a name really has a suffix that you want to
strip. By soul search, I mean you're going to have to come up with SOME KIND
of naming convention such that programming can occur.

My suggestions would be either:

1. that a suffix is defined as the underscore with four trailing characters.
e.g. _0001, _0ab3, _1234

2. A name can't have any previous underscores

3. a suffix always contains two underscores
e.g. __0001, __a3, __34

--
Steve Clark,
Former Access MVP
FMS, Inc
http://www.fmsinc.com/consulting

Ok, so that eliminated the #Error result, if D86547 is an original name. It
now leaves that result blank. I need for it to pull in the original name
also. So if D86547 is an original name I need the same formula to pull
D86547. So with this one formula;

"aaaa_hi_e_28" converts to "aaaa_hi_e"
"D86547" remains "D86547"

Thank you for your continued assistance.

prepend one, InStrRev( "_" & [List Name] , "_") so there will be always
one.

Alternatively, use iif to test the result of InStrRev, and if it returns 0,
let your iif return 1 instead (or whatever the logic dictates).



Vanderghast, Access MVP

I am using the following formula in my Query.

Correct: Left$([List Name],InStrRev([List Name],"_")-1)

I am using this formula to convert some data to the following;

"aaaa_hi_e_28" converts to "aaaa_hi_e" or
"aaaa_e_28" converts to "aaaa_e" or
"aaaa_hi_e_0528" converts to "aaaa_hi_e"

But, if I have some data that does not have an underscore & "date" at the
end, like D86547, I get #Error. How do I change the formula to just use
the
original name if #Error occurs?

Thank you in advance.