asked on

Error on nulls with Left Mid() function

this function in Access produces an error on nulls in the column:
Left(Right(,Len()-InStr(),"(")),Len(Right(,Len()-InStr((),"(")))-1) - how can this be rewritten so that it will convert nulls into empty strings?

Also, will this work in SQL 2005? thanx

ASKER CERTIFIED SOLUTION

Andrew_Webster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

I hate to say this, but your expression has many issues. For starters, your InStr() functions have no arguments ... which is your first issue. Next, Your Right() function syntax is incorrect.

Left(Right(,Len()-InStr(),"(")),Len(Right(,Len()-InStr((),"(")))-1)

Left(Right(,Len()-InStr(),"(")),Len(Right(,Len()-InStr((),"(")))-1)

Please look these up in VBA Help to get the correct syntax ...

mx

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"bar0822 declined to include the actual strings and/or fields used in the expression and in doing so"
I see. Just to make it a trick question.

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

@JDettman:
"Nice to see you wanting to go the extra mile here,"
Huh? Not related.

Andrew_Webster

I agree with everyone! Yes, the expression is a mess, and a royal pain to debug. But the question was about how to convert Nulls to empty strings, and that was the answer given.

If I was stuck with that expression, I'd probably write a VBA function to clean it up, and call that from a query. But even that might depend on the performance, as if it's in a query that hits many rows then performance could be a dog.

But I'm not going to go into "what ifs" or I'd drive myself nuts. More nuts.