string limit with sql statement

  • Thread starter Thread starter zeta
  • Start date Start date
Z

zeta

I am trying to run the following Sub that builds a sql
statement and opens a report based on the argument "Num"
fed to it when the user clicks a button. The string can
only hold 250 characters, not enough for the sql
statement. Is there a way around this?

Thanks
zeta


Sub EFactor(Num)

Dim SQLstatement As String

SQLstatement1 = "SELECT DailyAvg" & Num & ".Date,
FormatNumber([AvgOf"
SQLstatement2 = SQLstatement1 & Num & "_CO],2) AS CO,
FormatNumber([AvgOf"
SQLstatement3 = SQLstatement2 & Num & "_O2],2) AS O2,
FormatNumber([AvgOf"
SQLstatement4 = SQLstatement3 & Num & "_StackTemp],2)
AS StackT, FormatNumber([CountOfDate],0) AS Minutes,
FormatNumber([PercentCapture],2) AS DataCapture, E_Calc
([Avgof"
SQLstatement5 = SQLstatement4 & Num & "_StackTemp],
[Avgof"
SQLstatement6 = SQLstatement5 & Num & "_CO],[Avgof"
SQLstatement7 = SQLstatement6 & Num & "_O2]) AS E,
FormatNumber(([E]*15.308),2) AS ER FROM DailyAvg"
SQLstatement = SQLstatement7 & Num & ";"

DoCmd.OpenReport "E_report",
acViewPreview, , "SQLstatement"

End Sub
 
Maybe create a stored query based on your SQL string, then base the report
on that query.

Check-out the CreateQuerydef method in online help.

HTH,
TC
 
Why is the string limited to 250 characters?
That is not "normal". Did you set it that way?

I think SQL commands may have a limit of 2048 characters.
 
SQL String can be up to approx. 64K characters so I am not sure where you
got 250 characters from. However, there are a few problems with your
OpenReport statement:

DoCmd.OpenReport "E_report", acViewPreview, , "SQLstatement"

1. My guess is that you want the (SQL) String that is stored in the
Variable SQLStatement. In this case, you should use just SQLStatement
WITHOUT the double-quotes. But wait ...

2. The argument where you have "SQLStatement" is the "wherecondition"
argument and you cannot use a (full) SQLString for the "wherecondition". It
is generally a WHERE Clause without the word "where". In your case, you
don't even have a WHERE clause in the SQL String.

I am not sure what you are trying to achieve with the "wherecondition"
argument but the usage is incorrect. Check Access VB Help for proper use of
the OpenReport Method paying attention to the arguments.

3. It strange that you declare the Variable SQLStatement but not the
Variables for the components. If your code compiles OK, you are not using
the Option Explicit method which IS RECOMMENDED by most Access VB books. It
is easy to make typing mistakes and if you make a typing mistake for a
Variable name without Option Explicit, you code will just assume the
mis-typed Variable Name as a new Variable with default value depends on the
type and this will create accuracy problems in your database. Personally, I
use Option Explicit ALL the time.

4. OTOH, you don't need to use the component Variables SQLStatementX and
you can simply create the whole SQLString construction in one statement
using the continuation symbol (Space +Underscore) to extend the statement
line.

--
HTH
Van T. Dinh
MVP (Access)



zeta said:
I am trying to run the following Sub that builds a sql
statement and opens a report based on the argument "Num"
fed to it when the user clicks a button. The string can
only hold 250 characters, not enough for the sql
statement. Is there a way around this?

Thanks
zeta


Sub EFactor(Num)

Dim SQLstatement As String

SQLstatement1 = "SELECT DailyAvg" & Num & ".Date,
FormatNumber([AvgOf"
SQLstatement2 = SQLstatement1 & Num & "_CO],2) AS CO,
FormatNumber([AvgOf"
SQLstatement3 = SQLstatement2 & Num & "_O2],2) AS O2,
FormatNumber([AvgOf"
SQLstatement4 = SQLstatement3 & Num & "_StackTemp],2)
AS StackT, FormatNumber([CountOfDate],0) AS Minutes,
FormatNumber([PercentCapture],2) AS DataCapture, E_Calc
([Avgof"
SQLstatement5 = SQLstatement4 & Num & "_StackTemp],
[Avgof"
SQLstatement6 = SQLstatement5 & Num & "_CO],[Avgof"
SQLstatement7 = SQLstatement6 & Num & "_O2]) AS E,
FormatNumber(([E]*15.308),2) AS ER FROM DailyAvg"
SQLstatement = SQLstatement7 & Num & ";"

DoCmd.OpenReport "E_report",
acViewPreview, , "SQLstatement"

End Sub
Click to expand...
 
Van,

Points taken about poor programming practices.

I know the variable is in the "Where" argument slot, I was
fooling around and put it there before pasting the text to
the post. It was in the correct spot before and did not
work.

I swear I read somewhere that 250 char is the max for sql
statements using .openreport, must have been
halucinating. HOwever, when i watch the statement being
built, it is truncated at 250 characters when I copy from
the Watched variable pane. Could be a limitation there.

Maybe you can clear up another confusing issue I have with
opening a report from code. What should I set
the "Recordset" property of the report to when I am
designing it? LEave it blank?

Thanks
Zeta
-----Original Message-----
SQL String can be up to approx. 64K characters so I am not sure where you
got 250 characters from. However, there are a few problems with your
OpenReport statement:

DoCmd.OpenReport "E_report",
Click to expand...
acViewPreview, , "SQLstatement"
1. My guess is that you want the (SQL) String that is stored in the
Variable SQLStatement. In this case, you should use just SQLStatement
WITHOUT the double-quotes. But wait ...

2. The argument where you have "SQLStatement" is the "wherecondition"
argument and you cannot use a (full) SQLString for the "wherecondition". It
is generally a WHERE Clause without the word "where". In your case, you
don't even have a WHERE clause in the SQL String.

I am not sure what you are trying to achieve with the "wherecondition"
argument but the usage is incorrect. Check Access VB Help for proper use of
the OpenReport Method paying attention to the arguments.

3. It strange that you declare the Variable SQLStatement but not the
Variables for the components. If your code compiles OK, you are not using
the Option Explicit method which IS RECOMMENDED by most Access VB books. It
is easy to make typing mistakes and if you make a typing mistake for a
Variable name without Option Explicit, you code will just assume the
mis-typed Variable Name as a new Variable with default value depends on the
type and this will create accuracy problems in your database. Personally, I
use Option Explicit ALL the time.

4. OTOH, you don't need to use the component Variables SQLStatementX and
you can simply create the whole SQLString construction in one statement
using the continuation symbol (Space +Underscore) to extend the statement
line.

--
HTH
Van T. Dinh
MVP (Access)



I am trying to run the following Sub that builds a sql
statement and opens a report based on the argument "Num"
fed to it when the user clicks a button. The string can
only hold 250 characters, not enough for the sql
statement. Is there a way around this?

Thanks
zeta


Sub EFactor(Num)

Dim SQLstatement As String

SQLstatement1 = "SELECT DailyAvg" & Num & ".Date,
FormatNumber([AvgOf"
SQLstatement2 = SQLstatement1 & Num & "_CO],2) AS CO,
FormatNumber([AvgOf"
SQLstatement3 = SQLstatement2 & Num & "_O2],2) AS O2,
FormatNumber([AvgOf"
SQLstatement4 = SQLstatement3 & Num & "_StackTemp],2)
AS StackT, FormatNumber([CountOfDate],0) AS Minutes,
FormatNumber([PercentCapture],2) AS DataCapture, E_Calc
([Avgof"
SQLstatement5 = SQLstatement4 & Num & "_StackTemp],
[Avgof"
SQLstatement6 = SQLstatement5 & Num & "_CO],[Avgof"
SQLstatement7 = SQLstatement6 & Num & "_O2]) AS E,
FormatNumber(([E]*15.308),2) AS ER FROM DailyAvg"
SQLstatement = SQLstatement7 & Num & ";"

DoCmd.OpenReport "E_report",
acViewPreview, , "SQLstatement"

End Sub
Click to expand...


.
Click to expand...
 
The "WhereCondition" argument can be up to 32K character in OpenReport /
OpenForm Method. However, the "Where" argument in Macro action can only be
256 characters. You might have confused between the 2.

I mainly used Recordset in code and Recordset is not a Property you can set
in the Design of the Report. It may be different in AXP.
 
To be clear, you can leave the recordsouce of the report bound to a table.

You can also create a query in code, and have the report bound to that
query.

However, generally you can get away with having the report bound to table.
You then set the sort order in the reports sorting and grouping.

Your "where" clause in the OpenReport command can then be used to restrict
the records you want to appear in the report. The "where" clause is simply a
standard sql where clause without the word where. (you can't use a whole sql
statement in the "where", but JUST conditions to restrict the data).
 
Back
Top