Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel PivotTable dropping date format in SQL (Excel 2003)

    Hi everybody:

    This makes NO sense to me! I'm using an ODBC connection to my Access .mdb tables to get the data to create a pivottable report using VBA. I have successfully created the command text for my pivot cache, using variables based on user selections in the form. The SQL string is perfect. But MS is messing it up when the PivotTable is created by dropping the { in the date format.

    My code:
    strSQL = "SELECT " & strQDef & ".* " & Chr(13) & "" & Chr(10) & " FROM " & strQDef & Chr(13) & "" & Chr(10) & strWhereDate & strWhereStrat & strWHERE & ";"

    results in:

    strSQL = "SELECT qODBCStyleQuery.*
    FROM qODBCStyleQuery
    WHERE (qODBCStyleQuery.AsOfDate >= {ts '2004-1-31 00:00:00'}) AND (qODBCStyleQuery.'Initial Investment date' IS NULL OR qODBCStyleQuery.'Initial Investment Date' <= {ts '2004-1-31 00:00:00'}) AND qODBCStyleQuery.Strategy = 'Convertible Arbitrage';"

    Note the pretty curly braces around the date parameters!

    When I try set PT = PC.CreatePivotTable, I get error 1004:

    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression '(qODBCStyleQuery.AsOfDate >= ts '2004-1-31 00:00:00'}) AND (qODBCStyleQuery.'Initial Investment date' IS NULL OR qODBCStyleQuery.'Initial Investment Date' <= t

    Note the LACK of my { before ts in both places! MS is removing this character for some reason and then erroring out. I have tried, in vain, putting it in using ascii, using a single quote for a literal in front of it - nothing is working.

    Please help!

    Thank you,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel PivotTable dropping date format in SQL (Excel 2003)

    What type of field is AsOfDate?

    Please forgive my ignorance, but what does {ts '...'} mean and why do you use it?

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel PivotTable dropping date format in SQL (Excel 2003)

    Hi Hans:

    Thanks for responding. AsOfDate is a field name in my Access table that contains a date data type, in short date format. I'm using the {ts date format, because when I recorded an Excel macro using the pivottable wizard, that was the format it created, and it worked. I just couldn't make it work myself in code using date parameter variables. I had already tried using the Access ## date delimeters without success, which is why I tried doing it Microsoft's way (that may have been my biggest mistake!)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel PivotTable dropping date format in SQL (Excel 2003)

    I apologize, after posting my previous reply, I found that {ts 'date/time'} is ODBC timestamp format, and that Excel indeed generates it when recording a macro.

    Your code works for me (in Excel 2002 SP3), but the standard SQL way of using a date value works for me too:

    ... WHERE (qODBCStyleQuery.AsOfDate >= #01/31/2004#) ...

    I'm afraid I have no idea why it fails for you. Does the problem persist after quitting and restarting Excel? After rebooting your PC?

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    OK, I fixed it

    Yes to both. We have Excel 2003 SP 2 running on XP here at the office; I doubt I can convince the systems guy to install SP3.

    I tried creating a new workbook and importing all my sheets and modules, thinking there might be some damage to the Excel file, but no improvement.

    Any other ideas? Am I running up against some stupid character limit in my sql string?

    Apparently, yes to the above. I re-wrote my SQL string in Access format, got it under 200 characters, and it no longer errors out. Final SQL:

    SELECT Q.* FROM qODBCStyleQuery As Q WHERE (Q.AsOfDate >= #4/1/2004# AND (Q.[Initial Investment date] IS NULL OR Q.[Initial Investment Date] <= #4/1/2004#) AND Q.Strategy = 'Convertible Arbitrage');

    Thanks for your help.

Posting Permissions

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