Run-Time Error

Private Sub cmd_DeleteTables_Click()
MsgBox "Click yes when prompted to delete records"
Dim sqldel As String
sqldel = "DELETE tbl_physical_main.*, tbl_physical_main.[Material Number]
FROM tbl_physical_main WHERE (((tbl_physical_main.[Material Number]) Is Not
Null))"
DoCmd.RunSQL sqldel, no
sqldel = "DELETE tbl_noDataForTag.* FROM tbl_noDataForTag"
DoCmd.RunSQL sqldel, no
sqldel = "DELETE tbl_SAPmaterials.* FROM tbl_SAPmaterials"
DoCmd.RunSQL sqldel, no
MsgBox "All previous inventory data has been deleted and the next inventory
load may continue."
End Sub
--
Run-Time Error '94'
Invalid use of Null
Please help me!

On the first delete, try removing the tbl_physical_main.[Material Number] from the delete section of the query.

BobP

|||sqldel = "DELETE FROM tbl_physical_main WHERE (((tbl_physical_main.[Material Number]) Is Not
Null))"|||

When you come upon these types of problems, prind out the commands that you are trying to execute without executing them. Then format them in a more readable manner, like:

DELETE tbl_physical_main.*,
tbl_physical_main.[Material Number]
FROM tbl_physical_main
WHERE (((tbl_physical_main.[Material Number]) Is Not Null))

DELETE tbl_noDataForTag.*
FROM tbl_noDataForTag

DELETE tbl_SAPmaterials.*
FROM tbl_SAPmaterials

Then try parsing/running them in SSMS or QA. The first problem is the bolded text. You delete from tables, not from columns. So:

DELETE tbl_physical_main
FROM tbl_physical_main
WHERE (((tbl_physical_main.[Material Number]) Is Not Null))

DELETE tbl_noDataForTag
FROM tbl_noDataForTag

DELETE tbl_SAPmaterials
FROM tbl_SAPmaterials

I would lose the superfluous parens too:

DELETE tbl_physical_main
FROM tbl_physical_main
WHERE tbl_physical_main.[Material Number] Is Not Null

I can't see any reason for the message (you should have gotten syntax errors) so you should be good.

Running Value

Hi

In my report I have the total column,under the total i have two sub fields:no , Row%and i have another column Cumulative total sub fields are no,***%

For the Row % under total i write like this:

=Round((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,2)

For the *** % under cumulative total the expression is:

=RunningValue((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,sum,"AgeByGender")

But i am getting this error:

The Value expression for the textbox '*** %' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.

How to get the cm % for the Cumulative total

Please help me

Thanks in advance

You can not 'sum' inside of other aggregate, you need to remove the sum function

=RunningValue((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,sum,"AgeByGender")

Try without "Sum"

(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value/Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100

Ham

|||

Hi,

I want the SUM inside the runningValue,It is necessary.

Thanks

Running Value

Hi

In my report I have the total column,under the total i have two sub fields:no , Row%and i have another column Cumulative total sub fields are no,***%

For the Row % under total i write like this:

=Round((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,2)

For the *** % under cumulative total the "AgeByGender")

But i am getting this error:

The Value

I solved the problem with the following expression.

For Cumulative %:The formula i used is:

Round(RunningValue((fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value),sum,"AgeByGender") /Sum((fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value), "AgeByGender") *100,2)

This expression solved the problem.

Thanks

Running value

Hi

In my report I have the total column,under the total i have two sub fields:no , Row%and i have another column Cumulative total sub fields are no,***%

For the Row % under total i write like this:

=Round((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,2)

For the *** % under cumulative total the expression is:

=RunningValue((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,sum,"AgeByGender")

But i am getting this error:

The Value expression for the textbox '*** %' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.

How to get the cm % for the Cumulative total

Please help me

Thanks in advance

Mahima

You will need to remove the "SUM" inside the running value statement.

=RunningValue((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,sum,"AgeByGender")

Ham

|||

Hi,

I need the cum%(Cumulative %) thatswhy i added SUM inside.Without using Sum inside Running value,How to get the Cumulative %,Any work around .

Thanks in advance

|||

Mahima,

I'm missing a piece of Info to help you. Where are you trying to place your cumulative total - Is this a group footer, table footer, or Matrix?

Thks

Ham

|||

Hi,

This Cumulative Total % is a column in a Table.

|||

Hi,

Any one there,Please help me on this issue.

Thanks

|||

Mahima,

I meant to answer but I'm in the middle of a SSRS production release with my client. I did want to say that you could use the ReportItem!Field.value to replace the SUM(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value) - if you are not using the them in a report column the created a calculated field then use the calculated field in replace the SUM values.

|||

Hi,

Thanks for replying.Can you explain a bit clear.I tried in this way,We have a % column i.e textbox67,In Cum%,i used like that:(ReportItems!TextBox67.Value,Sum,"dataset1").But iam getting error.report items use only in header footer.

Where to calculate that value separately.

Thanks

|||

Okay,

My bad, I now remember why I asked if it was in the header. On the Dataset, on click, Add new calculated field, place your expression, then use in your runningvalue statement.

Ham

|||

Hi,

I did the same before.I created a calculated field and using those calculated field in the RunningValue,But the problem is When i click on View report button,It is displaying the following message,An internal error occured,See ebetlog for details.And closing the application(Visual Studio).Here what is the problem.

The exception is Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogexception occured in devenv.exe.

Thanks

|||

WOW,

It looks like a lot of things are not going right here. Is your data valid?

Ham

|||

Hi,

My data is valid.It is working fine before adding the Calculated field.Any other alternative.

Thanks

|||Please post your expression, I would like to try an verify it.|||

Hi,

In the Dataset named "DataSet1" fields are Male,Female,Unknown.

In the report,i have the following columns

Male Female Unknown Total Total% CumulativeTotal Cumulative%

Total=Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value

Total%=Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value)

CumulativeTotal=RunningValue(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value,Sum,"Dataset1")

CumulativeTotal%=RunningValue(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value),Sum,"DataSet1")

this Cumulativetotal% is giving error,So i created the 'calculated filed' named as "Percent" with the expression:Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value)

and try to use the Percent value like this:

RunningValue(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value/Fields!Percent.Value,Sum,"DataSet1"),This is closing the Visual Studio and giving the exception.

Thanks

|||

In your calculated field,

Only place this Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value in calculated fields

in your expression use RunningValue(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value/Sum(mycalculatedfields),

I hope that this works for you.