left$ vs. left puzzler

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
I thought there was no difference. The following sounds like a lot, but it's really a quick way to show what I mean.

Create 2 tables, all text:
table:T1
T1Field1

table:T2
T2Field1
T2Field2

Put 3 records in T1 - "0" in each is fine
Put 3 pairs of values in T2, where *NONE* of them match T1
1 2
1 2
1 2

Then this will fail ("Data type mismatch in criteria expression"):
SELECT Left$([t2field2],1) AS Expr2
FROM T1 LEFT JOIN T2 ON T1.T1Field1 = T2.T2Field1
GROUP BY Left$([t2field2],1);

Additionally strange is that you can change Left$ to left, and it works.
OR you can change LEFT JOIN to INNER JOIN, and it doesn't get any joins, but there are no errors. You get an empty return set, which is correct.

Removing the "GROUP BY" also provides interestingly different results (while using the LEFT JOIN).

What's up here? In particular, what is the difference between Left$ and Left ?? Remember, all fields are text - if that's what you're thinking.
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
Hi GIA
I'm struggling to find the reason for the difference but the only thing I can add is that the Left$ function returns an error if there is no data, whereas I believe the Left function returns a null value in the same circumstances. As for why? :unsure:
Andrew
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
Thanks for looking at this. After the long cold silence following the post, I self-contemplated that it might simply be insane - which wouldn't be the first time. After hearing Andrew, I feel that I have now been blessed non-crazy :rolleyes:

I think your Null point is meaningful here.
 

Andrew Fergus

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
I tried trapping the error in a select query using IsError() with the intention of substituting a zero length string, but without success.....I figured it's just easier to use the Left() function instead.

You're not crazy - the long cold silence may in fact have been a stunned reaction to your lipstick-wearing sheep avatar......

Cheers, A
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
It's a Kiwi sheep, but you don't want to hear the story. Trust me.

I recommend that you just go with it being something on the Gates theme instead :devilish:
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
Yes....

IsNull(Left(Null,1))
is TRUE, while

IsError(Left$(Null,1))
is too elusive.

I agree with your conclusions. I'll stick to Left unless I want an error to "jump out at me" instead of permitting a null value to silent slipp by.

In the past I've seen chatter about "disambiguating" via prefixes like VBA. and VBS. when using these in VBA. I don't see that that matters in a standard installation. It doesn't seem to matter for me, anyway :)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,549
Messages
5,529,470
Members
409,884
Latest member
Msinmath
Top