VBA ADO SQL Question RRS feed

  • Question

  • I hope I'm asking this in the right place.

    I have this VBA ADO SQL that I've written...

    SELECT TABLE_1.Column_Key_1,
                 CDate(IIf(TABLE_1.[Usage Start Date] = "", "0001-01-01",TABLE_1.[Usage Start Date]))      AS  [Bill Period Start Date],
                 CDate(IIf(TABLE_1.[Usage End Date] = "", "0001-01-01",TABLE_1.[Usage End Date]))      AS  [Bill Period End Date],
    FROM     TABLE_1
           ON  ([TABLE_2].[Usage Start Date]      =       CDate(TABLE_1.[Usage Start Date])
           AND ([TABLE_2].[Usage End Date]        =       CDate(TABLE_1.[Usage End Date])

    Now TABLE_1.[Usage Start Date] and [Usage End Date] may be empty...why I'm IIfing them. And if there is a date there, it's Text...why I'm CDateing.

    How do I handle the Join??? I know the above doesn't work. Do I actually have to Sub-Select and alias the columns before I attempt the Join???

    ANy help is GREATLY appreciated.

    Thanks for your review and am hopeful for a reply.


    Wednesday, September 26, 2012 11:49 PM

All replies

  • Any particular reson you want to pick 1st of January 0001 as the default date?  The usual default date is 31st of December 1899 (for JET/ACE, i.e. Access) or 1st of January 1900 for Microsoft SQL Server both of which correspond to the numeric zero).  Also, for the Null [Usage End Date], don't you want to make some value far in the future so that, if it is used as a selection criterion, you can cover a range as far as possible:

    Assuming your SQL is for JET, ACE, the SELECT clause should be something like:

    SELECT TABLE_1.Column_Key_1,
      Nz(TABLE_1.[Usage Start Date], #1899-12-31#)  AS  [Bill Period Start Date],
      Nz(TABLE_1.[Usage End Date],#9999-12-31#) AS  [Bill Period End Date],

    For the FROM clause, you need to descibe some details regarding these 2 Tables, how the data beetween the 2 matches and what you want the Query to return.

    Van Dinh

    • Edited by Van Dinh Friday, September 28, 2012 7:00 AM
    Thursday, September 27, 2012 5:54 PM