Excel PivotTable dropping date format in SQL (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

What type of field is AsOfDate?

Please forgive my ignorance, but what does {ts ‘…’} mean and why do you use it?

Reply | Quote

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!)

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote