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.

RunningTotal (for the birds)

How do I solve this RDL chart problem?

A bird watcher records the date each time he spots a new type of bird ('NewBird'). Likewise, he records the date of each time he spots a previously known type of bird ('OldBird'). Like this:

SpotDate Event
01/01/07 NewBird
01/01/07 NewBird
01/01/07 NewBird
01/02/07 OldBird
01/02/07 NewBird
01/02/07 OldBird
01/06/07 NewBird
01/06/07 OldBird
01/06/07 OldBird

I want to make an RDL chart that shows a running total of 'NewBird' to 'OldBird' along the date timeline. I am using SQL suite 2K5. I figure my "Category" field should be 'SpotDate', my series field should be 'Event,' and my "Data" field should be some kind of "=RunningTotal(Event, ?)". Is this correct?

And for that matter, how do I convince the RDL chart to include the elapsed time (the missing days between Jan 1, and Jan 6 in the plot)?

Ok, it seems the answer (mostly) to my own question is this:

1) Create a select statement that provides the daily total (e.g. called "Tally") grouped by SpotDate and Event.

2) In the chart, add the SpotDate to the category, the Event to the series, and the "Tally" as the value

3) Edit the "Tally" properties so that its 'value' field shows '=RunningValue(Fields!Tally.Value, Sum, "chart1_SeriesGroup1")

Thats it! The name "chart1_SeriesGroup1" was chosen simply to match the auto-generated name Visual Studio gave to the "Event" series (you can find or edit this name by selecting the 'data' tab on chart properties. One final piece of advice: do not "add" a new dataset field that contains a "RunningValue" calculated value...it causes Visual Studio to crash every time you view a chart (whether or not you used it on the chart).

So now my only remaining issue, is the need to convince the RDL chart to include the elapsed time (the missing days between Jan 1, and Jan 6 in the plot) that I showed as example data earlier.

|||

> need to convince the RDL chart to include the elapsed time (the missing days between Jan 1, and Jan 6 in the plot)

Set the x-axis to use scalar mode and make sure the category grouping expression returns DateTime objects (not data values as strings).

For more information, you may want to read the following section of a technical article on SSRS charts: http://msdn2.microsoft.com/en-us/library/aa964128.aspx#moressrscharts_topic3

-- Robert

Running Total - Count Only Displayed Records

I am working on a Crystal Report right now that I am having trouble trying to figure out how to only count the records that have been displayed in the report. I do not have a whole lot of experience with Crystal. I have supressed the details section with the following formula:

//This formula suppresses the Details line
//when there are duplicate values contained in the fields.
{view_CR_Vehicle_Sales_Deals.CUS_Name} = Next({view_CR_Vehicle_Sales_Deals.CUS_Name}) And
{MajorUnitOrder.VehicleIdentificationNum} = Next({MajorUnitOrder.VehicleIdentificationNum}) And
{MajorUnitOrder.SoldDate} = Next({MajorUnitOrder.SoldDate})

The suppressed data is getting included in the running total count. Is there anyway that I can make Crystal Reports only count what is displayed on the screen?

Thanks,

JamesRight click on the column;select summary;select distinct count;place it in the report

Running Stored Proc inside of Access and getting error

Running Stored Proc inside of Access and getting "The stored procedure
executed successfully but did not return records". When I run in Query
Analyzer it runs just fine. The Stored Proc is as follows:
CREATE PROCEDURE MAINT_EditorActivitybyTime (@.Enter_Start_Date_
smalldatetime, @.Enter_End_Date_ smalldatetime)
AS
DROP TABLE TEMP_MA_Node
--stored proc to create one table from MA_Node and MA_Node_Status10
DECLARE @.Temp_Node_Status10 table(NodeID bigint, NodeName nvarchar(100),
TypeID int, Status tinyint, OwnerID int, Url nvarchar(2048), Path
nvarchar(1000))
INSERT INTO @.Temp_Node_Status10
SELECT NodeID, NodeName, TypeID, Status, OwnerID, Url, Path
FROM MA_Node_Status10
SELECT *
INTO TEMP_MA_Node
FROM @.Temp_Node_Status10
INSERT INTO TEMP_MA_Node
SELECT NodeID, NodeName, TypeID, Status, OwnerID, Url, 'Live'
FROM MA_Node
SELECT dbo.MA_Change.CreateTime, dbo.MA_User.Email,
dbo.MA_Change.ChangeNodeID, dbo.MA_Change.DraftAction, TEMP_MA_Node.NodeName
,
TEMP_MA_Node.TypeID, TEMP_MA_Node.Status
FROM dbo.MA_Change INNER JOIN
dbo.MA_User ON dbo.MA_Change.ModifyUser =
dbo.MA_User.UserID LEFT OUTER JOIN
TEMP_MA_Node ON dbo.MA_Change.ChangeNodeID =
TEMP_MA_Node.NodeID
WHERE (dbo.MA_Change.CreateTime BETWEEN @.Enter_Start_Date_ AND
@.Enter_End_Date_)
ORDER BY dbo.MA_User.Email, dbo.MA_Change.CreateTime
GO
It also returns data in Query Analyzer like this:
2006-01-10 11:40:00.927 andy1221 198643 2 asdfasdf 4 0
2006-01-10 11:41:35.553 andy1221 198644 2 Lycos
Directory>Reference>Education>Distance Learning 4 0
2006-01-10 11:44:39.963 andy1221 198644 4 Lycos
Directory>Reference>Education>Distance Learning 4 0
2006-01-10 11:45:02.637 andy1221 30064 4 Reference Education Distance
Learning 4 0Hi Andy,
use
SET NOCOUNT ON
at the beginning of the SP
and
SET NOCOUNT OFF
as the last statetement of your proc.
If you use the sp like yours you will recieve multiple recordsets in Access.
HTH ;-)
Gru, Uwe Ricken
MCP for SQL Server 2000 Database Implementation
GNS GmbH, Frankfurt am Main
http://www.gns-online.de
http://www.memberadmin.de
http://www.conferenceadmin.de
________________________________________
____________
dbdev: http://www.dbdev.org
APP: http://www.AccessProfiPool.de
FAQ: http://www.donkarl.com/AccessFAQ.htm

Running SQL query in HUGE database

hi All, I am currently dealing with a number of tables each with over 40,000 records. I have these tables in Access and i'm planning to do some SQL query on them.

The problem is that due to then large table size, the query is running extremely slow and I'm not sure if using Access SQL is the most viable option.

I have something like:
there are three tables, T1 , T2 and T3.

T1: T2 T3

ID Value ID Value ID Value
1 100 2 5 3 1
2 200 3 5 4 1
3 300 4 5
4 400

My job is to add up all the corresponding values in the three tables and come out with something like this :

Results
ID Value
1 100
2 205
3 306
4 406

So you see the three tables have different number of records and If a record in T1 is not found in T2 or T3, I still want to keep the original value in T1. And for each table there are some 10,000 records!!!

Any advice on how to go about doing this? Some other alternatives I cuold think of is to copy the three tables to Excel and use formula, but in reality I have large number of such files so doing it manually is very time consuming.

Thanks!Your query would be:

select id, sum(value)
from
( select id, value from t1
union all
select id, value from t2
union all
select id, value from t3
)
group by id
order by id;

Whether this is too much data for Access to handle, I don't know. It is certainly a pretty small amount of data for a DBMS such as SQL Server or Oracle.|||thanks I just tried it and it works very well.

however I forgot to add a point that T2 and T3 might contain records that do not exist in T1 (say Id=5)
but I ONLY want records that exist in T1.

What should I do?
Thanks!|||In that case, perhaps an outer join is more appropriate?

select t1.id, t1.value+coalesce(t2.value,0)+coalesce(t3.value,0)
from t1
left outer join t2 on t2.id = t1.id
left outer join t3 on t3.id = t1.id;