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