Results 1 to 6 of 6

left$ vs. left puzzler

This is a discussion on left$ vs. left puzzler within the Microsoft Access forums, part of the Question Forums category; I thought there was no difference. The following sounds like a lot, but it's really a quick way to show ...

  1. #1
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default left$ vs. left puzzler

    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.
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

  2. #2
    MrExcel MVP Andrew Fergus's Avatar
    Join Date
    Sep 2004
    Location
    New Zealand
    Posts
    5,240

    Default

    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
    ~ >*()))><(


    I'd rather have a full bottle in front of me than a full frontal lobotomy.

  3. #3
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default

    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.
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

  4. #4
    MrExcel MVP Andrew Fergus's Avatar
    Join Date
    Sep 2004
    Location
    New Zealand
    Posts
    5,240

    Default

    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
    ~ >*()))><(


    I'd rather have a full bottle in front of me than a full frontal lobotomy.

  5. #5
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default

    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
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

  6. #6
    Board Regular Gates Is Antichrist's Avatar
    Join Date
    Aug 2002
    Location
    Earth (on working assignment from Hell)
    Posts
    1,961

    Default

    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
    Outlook 2007: Try to find undo and redo now in the menus and icons. I'm not kidding. Microsoft, you CLUELESS ***TARDS.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com