#Error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SJW_OST - 29 May 2008 19:43 GMT

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.

Reply to this Message

Michel Walsh - 29 May 2008 19:50 GMT

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.
>
[quoted text clipped - 12 lines]
>
> Thank you in advance.

Reply to this Message

KARL DEWEY - 29 May 2008 20:18 GMT

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))

Signature

KARL DEWEY
Build a little - Test a little

> prepend one, InStrRev( "_" & [List Name] , "_") so there will be always
> one.
[quoted text clipped - 20 lines]
> >
> > Thank you in advance.

Reply to this Message

SJW_OST - 29 May 2008 20:26 GMT

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.
[quoted text clipped - 26 lines]
> > >
> > > Thank you in advance.

Reply to this Message

SJW_OST - 29 May 2008 20:20 GMT

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.
[quoted text clipped - 20 lines]
> >
> > Thank you in advance.

Reply to this Message

S.Clark - 29 May 2008 20:52 GMT

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

Signature

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
[quoted text clipped - 30 lines]
> > >
> > > Thank you in advance.

Reply to this Message