left$ vs. left puzzler

[Gates Is Antichrist]

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.

 

[Andrew Fergus]

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?
Andrew

 

[Gates Is Antichrist]

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

I think your Null point is meaningful here.

 

[Andrew Fergus]

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]

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

 

[Gates Is Antichrist]

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