Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Error message "formula is too long"
Hello all,
I have an Excel spreadsheet that, when opened, executes some SQL queries against a SQL Server 2000 DB. This function works correctly. This spreadsheet is part of an automated process. This process starts Excel up in the background, the data is pulled since the spreadsheet is opened, and the results are saved to an HTML file. The thing is, my users have requested I setup the spreadsheet such that if someone opens up the spreadsheet, they can easily manually modify something such that it will pull data from a date/time of their choice. I can think of multiple ways of doing this. The preferrable way of doing this is to have a cell where they could type the date/time they wish to look at. All the queries on the spreadsheet would grab the datetime from this cell and use this value within their queries. I did some testing on a blank Excel spreadsheet with some small queries and figured out a way to do it: make use of the CONCATENATE function. Like I said, on small queries I can make this work. Using a copy of the production Excel spreadsheet, I modified one of the queries to see if I it would work on one of them. The result: I get an error messages saying the Formula is too long. I could use some help with this. Consider the following query: DECLARE @startdate datetime DECLARE @enddate datetime DECLARE @currenttime DATETIME SET @currenttime = GETDATE() SET @currenttime = DATEADD(ss,-1 * DATEPART(ss, @currenttime), @currenttime) SET @currenttime = DATEADD(ms,-1 * DATEPART(ms, @currenttime), @currenttime) SET @StartDate = DateAdd(hh, -2, @currenttime) SET @EndDate = @currenttime SELECT Top 1 DateTime, Convert(decimal(38,3), Value) FROM AnalogHistory WHERE TagName in ('hucTurbidity_001.PV') AND wwRetrievalMode = 'Cyclic' AND value IS NOT NULL AND wwVersion = 'Latest' AND wwResolution = 1000 AND DateTime @StartDate AND DateTime <= @EndDate AND DateTime in (SELECT Datetime FROM AnalogHistory WHERE TagName in ('hucFilterHours_001.PV') AND value 0.0 AND wwResolution = 1000 AND wwRetrievalMode = 'Cyclic' AND wwVersion = 'Latest' AND DateTime @StartDate AND DateTime <= @EndDate) ORDER BY Value DESC I would like to change the above query to the following (A7 below is the reference to teh cell with the date/time in it): =CONCATENATE("DECLARE @startdate datetime DECLARE @enddate datetime DECLARE @currenttime DATETIME SET @currenttime = '", A7, "' SET @currenttime = DATEADD(ss,-1 * DATEPART(ss, @currenttime), @currenttime) SET @currenttime = DATEADD(ms,-1 * DATEPART(ms, @currenttime), @currenttime) SET @StartDate = DateAdd(hh, -2, @currenttime) SET @EndDate = @currenttime SELECT Top 1 DateTime, Convert(decimal(38,3), Value) FROM AnalogHistory WHERE TagName in ('hucTurbidity_001.PV') AND wwRetrievalMode = 'Cyclic' AND value IS NOT NULL AND wwVersion = 'Latest' AND wwResolution = 1000 AND DateTime @StartDate AND DateTime <= @EndDate AND DateTime in (SELECT Datetime FROM AnalogHistory WHERE TagName in ('hucFilterHours_001.PV') AND value 0.0 AND wwResolution = 1000 AND wwRetrievalMode = 'Cyclic' AND wwVersion = 'Latest' AND DateTime @StartDate AND DateTime <= @EndDate) ORDER BY Value DESC") This produces the error message. Is there a way I can make it work? How long can text be before this function starts to report this error message? Any suggestions and thoughts on this are greatly appreciated. Chris Smith |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Error message "formula is too long"
Chris -
Your life would be greatly simplified if you made your query a Stored Procedure, along the lines of what's shown below, and you could execute it by simply using exec QueryForChris A7 If you are using ADO, "exec QueryForChris" is your command text, A7 is the value for the parameter, and you need to set the command type to "adCmdStoredProc" ================================================== CREATE PROCEDURE QueryForChris @CurrentTime smalldatetime AS DECLARE @startdate datetime DECLARE @enddate datetime SET @StartDate = DateAdd(hh, -2, @currenttime) SET @EndDate = @currenttime SELECT Top 1 DateTime, Convert(decimal(38,3), Value) FROM AnalogHistory WHERE TagName in ('hucTurbidity_001.PV') AND wwRetrievalMode = 'Cyclic' AND value IS NOT NULL AND wwVersion = 'Latest' AND wwResolution = 1000 AND DateTime @StartDate AND DateTime <= @EndDate AND DateTime in (SELECT Datetime FROM AnalogHistory WHERE TagName in ('hucFilterHours_001.PV') AND value 0.0 AND wwResolution = 1000 AND wwRetrievalMode = 'Cyclic' AND wwVersion = 'Latest' AND DateTime @StartDate AND DateTime <= @EndDate) ORDER BY Value DESC GO |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Error message "formula is too long"
Thanks Duke!! Excellent suggestion. I'll give it a shot.
Chris Smith "Duke Carey" wrote in message ... Chris - Your life would be greatly simplified if you made your query a Stored Procedure, along the lines of what's shown below, and you could execute it by simply using exec QueryForChris A7 If you are using ADO, "exec QueryForChris" is your command text, A7 is the value for the parameter, and you need to set the command type to "adCmdStoredProc" ================================================== CREATE PROCEDURE QueryForChris @CurrentTime smalldatetime AS DECLARE @startdate datetime DECLARE @enddate datetime SET @StartDate = DateAdd(hh, -2, @currenttime) SET @EndDate = @currenttime SELECT Top 1 DateTime, Convert(decimal(38,3), Value) FROM AnalogHistory WHERE TagName in ('hucTurbidity_001.PV') AND wwRetrievalMode = 'Cyclic' AND value IS NOT NULL AND wwVersion = 'Latest' AND wwResolution = 1000 AND DateTime @StartDate AND DateTime <= @EndDate AND DateTime in (SELECT Datetime FROM AnalogHistory WHERE TagName in ('hucFilterHours_001.PV') AND value 0.0 AND wwResolution = 1000 AND wwRetrievalMode = 'Cyclic' AND wwVersion = 'Latest' AND DateTime @StartDate AND DateTime <= @EndDate) ORDER BY Value DESC GO |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Overcoming "Formula too long" | Excel Worksheet Functions | |||
"formula for tracking days off " | Excel Worksheet Functions | |||
"string too long" error message | Excel Discussion (Misc queries) |