Difference between Left() and Left$() function

Hello All,

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?

Thanks!
--
Nathan

Keywords: MS Microsoft Access Functions Function Left Left$ Left()
Left$() Query

re: Difference between Left() and Left$() function

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Nathan Given" <ngiven@hotmail.com> wrote in message
news:f749d926.0405040739.6ec5eedb@posting.google.c om...[color=blue]
> Hello All,
>
> 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?
>
> Thanks!
> --
> Nathan
>
> Keywords: MS Microsoft Access Functions Function Left Left$ Left()
> Left$() Query[/color]

re: Difference between Left() and Left$() function

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

"Nathan Given" <ngiven@hotmail.com> wrote in message
news:f749d926.0405040739.6ec5eedb@posting.google.c om...[color=blue]
> Hello All,
>
> 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?
>
> Thanks!
> --
> Nathan
>
> Keywords: MS Microsoft Access Functions Function Left Left$ Left()
> Left$() Query[/color]

re: Difference between Left() and Left$() function

gRE/[color=blue]
> However, if there is *any* chance that Nulls may be involved, use
>Left(), or else explicitly handle the Null with something such as Nz().[/color]

Rightly or wrongly....I always tack a blank
string on to any field I'm feeding to
a string function:

Left$(xyz & "",3)
or
If Len(xyz & "") > 0 Then...
--
PeteCresswell

re: Difference between Left() and Left$() function

Well it's right if you don't want to differentiate between a Null and an
empty string, otherwise it's wrong.

<g>

--
Terry Kreft
MVP Microsoft Access


"(Pete Cresswell)" <x@y.z> wrote in message
news:hh9e90lt50p0h0hqcvu33bocflscl0i7m7@4ax.com...[color=blue]
> gRE/[color=green]
> > However, if there is *any* chance that Nulls may be involved, use
> >Left(), or else explicitly handle the Null with something such as Nz().[/color]
>
> Rightly or wrongly....I always tack a blank
> string on to any field I'm feeding to
> a string function:
>
> Left$(xyz & "",3)
> or
> If Len(xyz & "") > 0 Then...
> --
> PeteCresswell[/color]

re: Difference between Left() and Left$() function

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in
news:4097bf71$0$16597$5a62ac22@freenews.iinet.net. au:
[color=blue]
> 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().[/color]

Has anyone ever tested exactly how much more efficient this is?

I have never bothered to use any of the string functions, not
because I'm worried about Nulls, just because I don't see the point.
In most cases where I'm using these typse of functions, I abort if
the value being processed is Null, anyway, so I *could* use them.

Obviously, there would have to be lots of calls to it for it to
matter. But how much does it matter? If 100K calls with Left() take
twice the time as the same calls to Left$(), it may matter if the
variance is of an order of magnitude greater than a second (though
I'm not sure I'd alter my code if the difference were 2 vs. 1
seconds), and the difference is close to the magnitude of the amount
of time the faster one takes in total. That is, if the difference is
1 second, but it's a comparison of 10 seconds vs. 9 seconds, I'd
probably not bother. But it if it's 2 vs. 1, I would.

Anyone have a test code handy that they could check this?

My other question would be whether or not all the $ functions have
the same advantage.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Difference between Left() and Left$() function

David, without testing, I would expect the time difference is going to be
pretty academic unless we are talking tight loops.

For me, it's more a matter of thinking clearly about the data. Am I dealing
with a string? or a variant? If a string, I don't *want* Access messing with
it, turning it into a Variant and then interpreting the Variant as a String.
Admittedly, it would be an unusual situation for VBA to get this wrong, but
it can happen when concatenating Variants (esp. if they contain numeric
values). And yes, this applies to all the functions that have the $ suffix.

It's mostly about writing clear, robust code. IMHO, VBA does us a great
disservice by trying to hide the data types from the user. It's not quite as
bad as the way as the Lookup Wizard hides the true data type from the
uniniated in Table Design, but misinterpreted data types cause some of the
problems posted here every day.

My personal practice is to use the type delcaration constants in literals in
code as well - again, not for speed by for clarity. So if Len() returns a
long, the way I code is:
If Len(strName) > 0& Then

Likewise, if I am passing the value of a text box into a function that
receives a Variant, I write:
Call MyFunc(Me.Text1.Value)
so that it is the Value that gets passed and not the entire text box.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
news:Xns94E076BD2F60Edfentonbwaynetinvali@24.168.1 28.78...[color=blue]
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in
> news:4097bf71$0$16597$5a62ac22@freenews.iinet.net. au:
>[color=green]
> > 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().[/color]
>
> Has anyone ever tested exactly how much more efficient this is?
>
> I have never bothered to use any of the string functions, not
> because I'm worried about Nulls, just because I don't see the point.
> In most cases where I'm using these typse of functions, I abort if
> the value being processed is Null, anyway, so I *could* use them.
>
> Obviously, there would have to be lots of calls to it for it to
> matter. But how much does it matter? If 100K calls with Left() take
> twice the time as the same calls to Left$(), it may matter if the
> variance is of an order of magnitude greater than a second (though
> I'm not sure I'd alter my code if the difference were 2 vs. 1
> seconds), and the difference is close to the magnitude of the amount
> of time the faster one takes in total. That is, if the difference is
> 1 second, but it's a comparison of 10 seconds vs. 9 seconds, I'd
> probably not bother. But it if it's 2 vs. 1, I would.
>
> Anyone have a test code handy that they could check this?
>
> My other question would be whether or not all the $ functions have
> the same advantage.[/color]

re: Difference between Left() and Left$() function

Well, rather than talk about it, here is one simple test:

Sub TestThis()
Dim lngCounter As Long, lngStart As Long, lngEnd As Long, lngMaxCounter
As Long, strX As String
lngMaxCounter = 10000000
lngStart = GetTickCount()
For lngCounter = 1 To lngMaxCounter
strX = Left$("abcdefg", 4)
Next
lngEnd = GetTickCount()
Debug.Print "Count = " & lngMaxCounter, ", Ticks = ", lngEnd - lngStart
End Function

Using Left$(), the ticks were 1750 to 1766 after the first run.

Using Left(), the ticks were 5000 to 5015 after the first run.

That's a factor of 2.8 faster using Left$(), on a Pentium 2.2 GHz, 512 MB
Ram.

Of course, that was 10 million calls, the overall time is negligible
compared to disk access.

- Steve

"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
news:Xns94E076BD2F60Edfentonbwaynetinvali@24.168.1 28.78...[color=blue]
>
> Has anyone ever tested exactly how much more efficient this is?
>[/color]

re: Difference between Left() and Left$() function

"Stephen K. Young" <s k y @ stanleyassociates . com> wrote in
news:c7bapr$1ugkt$1@ID-65843.news.uni-berlin.de:
[color=blue]
> Well, rather than talk about it, here is one simple test:
>
> Sub TestThis()
> Dim lngCounter As Long, lngStart As Long, lngEnd As Long,
> lngMaxCounter
> As Long, strX As String
> lngMaxCounter = 10000000
> lngStart = GetTickCount()
> For lngCounter = 1 To lngMaxCounter
> strX = Left$("abcdefg", 4)
> Next
> lngEnd = GetTickCount()
> Debug.Print "Count = " & lngMaxCounter, ", Ticks = ", lngEnd -
> lngStart
> End Function
>
> Using Left$(), the ticks were 1750 to 1766 after the first run.
>
> Using Left(), the ticks were 5000 to 5015 after the first run.
>
> That's a factor of 2.8 faster using Left$(), on a Pentium 2.2 GHz, 512
> MB Ram.
>
> Of course, that was 10 million calls, the overall time is negligible
> compared to disk access.[/color]

Do you think the difference would be as great if you declared
varY As Variant (or String for that matter)
and used
varY = "abcdefg"
and
compared
Left$(varY, 4)
and
Left(varY, 4)

?

Perhaps the expressions
Left$("abcdefg", 4)
and
Left("abcdefg", 4)
would never be used as we can type
"abcd" more easily than
Left[$]("abcdefg", 4)


--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

re: Difference between Left() and Left$() function

"Stephen K. Young" <s k y @ stanleyassociates . com> wrote in
news:c7bapr$1ugkt$1@ID-65843.news.uni-berlin.de:
[color=blue]
> Well, rather than talk about it, here is one simple test:
>
> Sub TestThis()
> Dim lngCounter As Long, lngStart As Long, lngEnd As Long,
> lngMaxCounter As Long, strX As String
> lngMaxCounter = 10000000
> lngStart = GetTickCount()
> For lngCounter = 1 To lngMaxCounter
> strX = Left$("abcdefg", 4)
> Next
> lngEnd = GetTickCount()
> Debug.Print "Count = " & lngMaxCounter, ", Ticks = ", lngEnd -
> lngStart
> End Function
>
> Using Left$(), the ticks were 1750 to 1766 after the first run.
>
> Using Left(), the ticks were 5000 to 5015 after the first run.
>
> That's a factor of 2.8 faster using Left$(), on a Pentium 2.2 GHz,
> 512 MB Ram.
>
> Of course, that was 10 million calls, the overall time is
> negligible compared to disk access.[/color]

How many tick counts in a second?

It looks pretty neglible to me, and so performance seems not to be
the proper reason for justifying using of the $ functions.

Allen Browne's argument makes more sense to me.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Difference between Left() and Left$() function

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in
news:40990eff$0$16589$5a62ac22@freenews.iinet.net. au:
[color=blue]
> David, without testing, I would expect the time difference is
> going to be pretty academic unless we are talking tight loops.[/color]

Based on Stephen's test with a million iterations, it's completely
academic in loops, too, which was what I expected.

My point was that performance is not an adequate justification for
using the $ functions.
[color=blue]
> For me, it's more a matter of thinking clearly about the data. Am
> I dealing with a string? or a variant? If a string, I don't *want*
> Access messing with it, turning it into a Variant and then
> interpreting the Variant as a String. . .[/color]

Hold on -- won't it return a variant of subtype string? And won't
that then be handled properly in concatenation?
[color=blue]
> . . . Admittedly, it would be an
> unusual situation for VBA to get this wrong, but it can happen
> when concatenating Variants (esp. if they contain numeric values).
> And yes, this applies to all the functions that have the $ suffix.[/color]

Well, the problem seems to be concatenating variants of different
subtypes, so you'd coerce the problematic ones. In my experience,
string variants are very reliable -- it's the non-string variants
that cause problems. Thus, if I was concatenating variants, I'd
coerce the non-strings to strings and leave the string variants
alone.
[color=blue]
> It's mostly about writing clear, robust code. . . .[/color]

Much of my use of these functions is in queries or in
controlsources, where I want to handle Nulls (since it's running
against fields that can be Null). In that case, concatenating the
input value with a zero-length-string would return a ZLS, which can
be a problem (e.g., if your appending the result to columns that
disallows ZLS).
[color=blue]
> . . . IMHO, VBA does us a
> great disservice by trying to hide the data types from the user.[/color]

It does?

It does implicit type coercion, yes, but that follows pretty
reliable rules, in my experience.
[color=blue]
> It's not quite as bad as the way as the Lookup Wizard hides the
> true data type from the uniniated in Table Design, but
> misinterpreted data types cause some of the problems posted here
> every day.[/color]

Related aside: I just ran into another case where lookups in
tabledefs cause problems. I was altering a really old app of mine
(from 1997), when I still thought lookups were a good thing. I was
changing name of the PK of the table being looked up. At home, I
used Speed Ferret, but at the client, I was changing their data file
manually, and so I didn't even know the lookup was there. This
resulted in all sorts of problems that were a real pain to fix.
[color=blue]
> My personal practice is to use the type delcaration constants in
> literals in code as well - again, not for speed by for clarity. So
> if Len() returns a long, the way I code is:
> If Len(strName) > 0& Then[/color]

I don't understand that one. Why would it get interpreted
incorrectly? Or, put another way, why would *you* interpret it
incorrectly? Zero is not inherently a long, so why declare it to be
so? Zero can be stored in any numeric data type.
[color=blue]
> Likewise, if I am passing the value of a text box into a function
> that receives a Variant, I write:
> Call MyFunc(Me.Text1.Value)
> so that it is the Value that gets passed and not the entire text
> box.[/color]

That's a different issue entirely. In that case you could accomplish
the same thing by making sure you declare your parameter as ByVal
instead of the implicit ByRef.

I get the concatenation case for the $ functions, because you don't
have to coerce any data types. But I don't actually think it's much
of a problem since Left() is going to return a variant of subtype
string (which will be handled correctly in all concatenations), or
enough of a problem to justify blanket use of the $ functions.

Convince me -- I definitely am for good coding practices. I'm trying
to be argued out of my lethargy in coding the other way forever and
ever.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Difference between Left() and Left$() function

"Stephen K. Young" <s k y @ stanleyassociates . com> wrote in
news:c7bapr$1ugkt$1@ID-65843.news.uni-berlin.de:
[color=blue]
> Of course, that was 10 million calls, the overall time is
> negligible compared to disk access.[/color]

I'd be interested to see if other $ functions exhibit the same
profile in terms of time difference. I'm not quite interested enough
to do the testing myself ;) or arrogant enough to ask anyone else to
do it.

But it would sure be interesting if it turned out that some of the $
functions are substantially more efficient than others.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Difference between Left() and Left$() function

Well, one other test I did run was to detect if there was any difference in
10 million iterations of:

lngX = lngX + 1&
If lngX <> -1& Then
Stop
End If

versus the same iterations without the & character:

lngX = lngX + 1
If lngX <> -1 Then
Stop
End If

No reliable difference.

- Steve

"David W. Fenton" <dXXXfenton@bway.net.invalid> wrote in message
news:Xns94E09E49F3844dfentonbwaynetinvali@24.168.1 28.86...[color=blue]
> "Stephen K. Young" <s k y @ stanleyassociates . com> wrote in
> news:c7bapr$1ugkt$1@ID-65843.news.uni-berlin.de:
>[color=green]
> > Of course, that was 10 million calls, the overall time is
> > negligible compared to disk access.[/color]
>
> I'd be interested to see if other $ functions exhibit the same
> profile in terms of time difference. I'm not quite interested enough
> to do the testing myself ;) or arrogant enough to ask anyone else to
> do it.
>
> But it would sure be interesting if it turned out that some of the $
> functions are substantially more efficient than others.
>
> --
> David W. Fenton http://www.bway.net/~dfenton
> dfenton at bway dot net http://www.bway.net/~dfassoc[/color]

re: Difference between Left() and Left$() function

"Lyle Fairfield" <MissingAddress@Invalid.Com> wrote in message
news:Xns94E092A5FEF15FFDBA@130.133.1.4...[color=blue]
> "Stephen K. Young" <s k y @ stanleyassociates . com> wrote in
> news:c7bapr$1ugkt$1@ID-65843.news.uni-berlin.de:
>[color=green]
> > Well, rather than talk about it, here is one simple test:
> >
> > Sub TestThis()
> > Dim lngCounter As Long, lngStart As Long, lngEnd As Long,
> > lngMaxCounter
> > As Long, strX As String
> > lngMaxCounter = 10000000
> > lngStart = GetTickCount()
> > For lngCounter = 1 To lngMaxCounter
> > strX = Left$("abcdefg", 4)
> > Next
> > lngEnd = GetTickCount()
> > Debug.Print "Count = " & lngMaxCounter, ", Ticks = ", lngEnd -
> > lngStart
> > End Function
> >
> > Using Left$(), the ticks were 1750 to 1766 after the first run.
> >
> > Using Left(), the ticks were 5000 to 5015 after the first run.
> >
> > That's a factor of 2.8 faster using Left$(), on a Pentium 2.2 GHz, 512
> > MB Ram.
> >
> > Of course, that was 10 million calls, the overall time is negligible
> > compared to disk access.[/color]
>
> Do you think the difference would be as great if you declared
> varY As Variant (or String for that matter)
> and used
> varY = "abcdefg"
> and
> compared
> Left$(varY, 4)
> and
> Left(varY, 4)
>
> ?
>
> Perhaps the expressions
> Left$("abcdefg", 4)
> and
> Left("abcdefg", 4)
> would never be used as we can type
> "abcd" more easily than
> Left[$]("abcdefg", 4)
>
>
> --
> Lyle
> (for e-mail refer to http://ffdba.com/contacts.htm)[/color]

Ok, good point, a typical usage would never use literal string values. So
here is another test:

Function TestThis() As Long
Dim lngCounter As Long, lngStart As Long, lngEnd As Long, lngMaxCounter
As Long
Dim strX As String, strY As String
strY = "abcdefg"
lngMaxCounter = 10000000
lngStart = GetTickCount()
For lngCounter = 1 To lngMaxCounter
strX = Left$(strY, 4)
Next
lngEnd = GetTickCount()
Debug.Print "Count = " & lngMaxCounter, ", Ticks = ", lngEnd - lngStart
End Function

With Left$(), run time was about 1780.
With Left(), run time was about 3078, a ratio of 1.7.
With Left() and strY declared as a variant instead, run time was 2484, a
ratio of 1.4.

So, you can improve the variant version by eliminating the implied type cast
to a variant argument using Left().

But of course, no real application looks anything like these tests.

Personally, I use string versions whenever I expect the input to be a
non-null string and not a variant. This serves as a precondition assertion
of non-nullness, assuming of course that error handling is added to make a
fuss if the precondition fails. If the input comes from something that might
be Null, then I use the variant versions and handle nulls explicitly as
needed.

- Steve

re: Difference between Left() and Left$() function

Same as in VB -- one returns a string, the other returns a variant of
string type. Neither should cause a problem with a query, though since you
did not explain what is broken you can take that last sentence with a grain
of salt since it may well be responsible for whatever problem you are
seeing.


--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies

This posting is provided "AS IS" with
no warranties, and confers no rights.


"Nathan Given" <ngiven@hotmail.com> wrote in message
news:f749d926.0405040739.6ec5eedb@posting.google.c om...[color=blue]
> Hello All,
>
> 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?
>
> Thanks!
> --
> Nathan
>
> Keywords: MS Microsoft Access Functions Function Left Left$ Left()
> Left$() Query[/color]

re: Difference between Left() and Left$() function

Chuck Grimsby <c.grimsby@worldnet.att.net.invalid> wrote in
news:6dri90l5nj8n2dlcf340cgd15rbvf6q2fr@4ax.com:
[color=blue]
> On Wed, 05 May 2004 19:31:26 GMT, "David W. Fenton"
><dXXXfenton@bway.net.invalid> wrote:
>[color=green]
>>"Stephen K. Young" <s k y @ stanleyassociates . com> wrote in
>>news:c7bapr$1ugkt$1@ID-65843.news.uni-berlin.de:[color=darkred]
>>> Of course, that was 10 million calls, the overall time is
>>> negligible compared to disk access.[/color][/color]
>[color=green]
>>I'd be interested to see if other $ functions exhibit the same
>>profile in terms of time difference. I'm not quite interested
>>enough to do the testing myself ;) or arrogant enough to ask
>>anyone else to do it.[/color]
>[color=green]
>>But it would sure be interesting if it turned out that some of the
>>$ functions are substantially more efficient than others.[/color]
>
> Assuming the tests from over in the VB newsgroups have any
> relevance, they are![/color]

Can you supply a Google Groups URL for that thread?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Difference between Left() and Left$() function

"Stephen K. Young" <s k y @ stanleyassociates . com> wrote in
news:c7bili$21a78$1@ID-65843.news.uni-berlin.de:
[color=blue]
>
> "Lyle Fairfield" <MissingAddress@Invalid.Com> wrote in message
> news:Xns94E092A5FEF15FFDBA@130.133.1.4...[color=green]
>> "Stephen K. Young" <s k y @ stanleyassociates . com> wrote in
>> news:c7bapr$1ugkt$1@ID-65843.news.uni-berlin.de:
>>[color=darkred]
>> > Well, rather than talk about it, here is one simple test:
>> >
>> > Sub TestThis()
>> > Dim lngCounter As Long, lngStart As Long, lngEnd As Long,
>> > lngMaxCounter
>> > As Long, strX As String
>> > lngMaxCounter = 10000000
>> > lngStart = GetTickCount()
>> > For lngCounter = 1 To lngMaxCounter
>> > strX = Left$("abcdefg", 4)
>> > Next
>> > lngEnd = GetTickCount()
>> > Debug.Print "Count = " & lngMaxCounter, ", Ticks = ", lngEnd
>> > - lngStart
>> > End Function
>> >
>> > Using Left$(), the ticks were 1750 to 1766 after the first run.
>> >
>> > Using Left(), the ticks were 5000 to 5015 after the first run.
>> >
>> > That's a factor of 2.8 faster using Left$(), on a Pentium 2.2
>> > GHz, 512 MB Ram.
>> >
>> > Of course, that was 10 million calls, the overall time is
>> > negligible compared to disk access.[/color]
>>
>> Do you think the difference would be as great if you declared
>> varY As Variant (or String for that matter)
>> and used
>> varY = "abcdefg"
>> and
>> compared
>> Left$(varY, 4)
>> and
>> Left(varY, 4)
>>
>> ?
>>
>> Perhaps the expressions
>> Left$("abcdefg", 4)
>> and
>> Left("abcdefg", 4)
>> would never be used as we can type
>> "abcd" more easily than
>> Left[$]("abcdefg", 4)
>>
>>
>> --
>> Lyle
>> (for e-mail refer to http://ffdba.com/contacts.htm)[/color]
>
> Ok, good point, a typical usage would never use literal string
> values. So here is another test:
>
> Function TestThis() As Long
> Dim lngCounter As Long, lngStart As Long, lngEnd As Long,
> lngMaxCounter
> As Long
> Dim strX As String, strY As String
> strY = "abcdefg"
> lngMaxCounter = 10000000
> lngStart = GetTickCount()
> For lngCounter = 1 To lngMaxCounter
> strX = Left$(strY, 4)
> Next
> lngEnd = GetTickCount()
> Debug.Print "Count = " & lngMaxCounter, ", Ticks = ", lngEnd -
> lngStart
> End Function
>
> With Left$(), run time was about 1780.[/color]

Except the comparison is unfair, since there is no possibility of
passing a Null to Left$().

That is, Left$() must take a string, so you really want to compare:

Left$(varY & vbNullString, 4)

to

Left(varY, 4)

The concatenation operation may or may not even things out.

Now, you may suggest that the concatenation should be done in the
variable assignment, but I don't think so, as then it's not a real
reflection of how you'd use Left$() in a context in which Left()
were the real alternative.

Left() handles the Nulls.

If you use Left$(), you've got to handle the Nulls yourself, and I
think that ought to be in the test *if* you're going to pass a
variant.
[color=blue]
> With Left(), run time was about 3078, a ratio of 1.7.
> With Left() and strY declared as a variant instead, run time was
> 2484, a ratio of 1.4.
>
> So, you can improve the variant version by eliminating the implied
> type cast to a variant argument using Left().[/color]

Put in the concatenation and see if it evens things out.
[color=blue]
> But of course, no real application looks anything like these
> tests.[/color]

Precisely my point about discarding performance as a relevant
consideration in choosing one over the other.
[color=blue]
> Personally, I use string versions whenever I expect the input to
> be a non-null string and not a variant. This serves as a
> precondition assertion of non-nullness, assuming of course that
> error handling is added to make a fuss if the precondition fails.
> If the input comes from something that might be Null, then I use
> the variant versions and handle nulls explicitly as needed.[/color]

In VBA code, you have the ability to choose. But in SQL, you'll
often have only the choice between Left(Field,N) and Left$(Field &
"", N), and my bet is that the former is faster. It's certainly
cleaner.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

re: Difference between Left() and Left$() function

To Everyone that responded,

Thank you all! The discussion has helped me greatly.

[color=blue]
> ... though since you
> did not explain what is broken you can take that last sentence with a grain
> of salt since it may well be responsible for whatever problem you are
> seeing.
>[/color]


If I new what was wrong with the query I would have explained it
better... the query is now fixed, but I don't know what the problem
was. I ended up changing a couple things with the query (I re-wrote
some if statements) and it ended up working.

Thanks again!
--
Nathan




[color=blue]
>
> --
> MichKa [MS]
> NLS Collation/Locale/Keyboard Development
> Globalization Infrastructure and Font Technologies
>
> This posting is provided "AS IS" with
> no warranties, and confers no rights.
>
>
> "Nathan Given" <ngiven@hotmail.com> wrote in message
> news:f749d926.0405040739.6ec5eedb@posting.google.c om...[color=green]
> > Hello All,
> >
> > 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?
> >
> > Thanks!
> > --
> > Nathan
> >
> > Keywords: MS Microsoft Access Functions Function Left Left$ Left()
> > Left$() Query[/color][/color]