Difference between Left() and Left$() function in Access

Question

I am trying to debug a broken query. The query uses Left$([blahblah],4) instead of Left([blahblah],4).

What is the difference between the Left() and Left$() functions in Microsoft Access?

Answer 1

The trailing $ is a type declaration character for the String data type in VBA.

The result returned from Left$() is a string, whereas Left() returns a Variant.

You must use Left(), not Left$() if there is any chance of Null values, since the Variant can be Null but the String cannot. To demonstrate that:

1. Press Ctrl+G to open the Immedate window.

2. Enter: ? Left(Null,1) The answer is Null.

3. Now enter: ? Left$(Null,1) This generates Error 94. Since the result should be Null, and the String cannot be Null, you receive the error, "Invalid use of Null".

If you are dealing with string values, in VBA code, Left$() will be slightly more efficient, as it avoids the overhead/inefficieny associated with the Variant. However, if there is any chance that Nulls may be involved, use Left(), or else explicitly handle the Null with something such as Nz().

left$() will return a string or generate an error if passed NULL

left() will return a string or NULL if passed NULL

More information on Nulls: http://allenbrowne.com/casu-11.html