C
Cheryl Dixon
Hello:
I need help with an ADP Project. First, I'm working on an
MS ADP project with a SQL Server 2000 database backend. I
have a subform and a form that both use the SUM function,
=Sum([field name]) in a text box control on the forms.
The Record Source for both forms (which are bound) is a
view called 'qryAnnex_View'. The SQL for 'qryAnnex_View'
is as follows:
SELECT DISTINCT dbo.TabSupplier.[ID #],
dbo.TabSupplier.SRAN, dbo.TabSupplier.JULIAN, dbo.TabAnnex.
[ID #] AS Expr1, dbo.TabSupplier.[ID #] AS ID,
dbo.TabSupplier.Agreement, dbo.TabSupplier.Supplier,
dbo.TabSupplier.Receiver, dbo.TabSupplier.WorkyearCost,
dbo.TabAnnex.Categories,
dbo.TabAnnex.SubCategory, dbo.TabAnnex.Support,
dbo.TabAnnex.RDescription, dbo.TabAnnex.RUnits,
dbo.TabAnnex.RUnitCost,
dbo.TabAnnex.NRManpower, dbo.TabAnnex.NRDescription,
dbo.TabAnnex.NRUnits, dbo.TabAnnex.NRUnitCost,
COALESCE (dbo.TabAnnex.NRManpower *
dbo.TabSupplier.WorkyearCost, 0) AS NRWorkyearCost,
COALESCE (dbo.TabAnnex.RUnits * dbo.TabAnnex.RUnitCost, 0)
AS RCost,
COALESCE (dbo.TabAnnex.NRUnits * dbo.TabAnnex.NRUnitCost,
0)AS NRCost, dbo.TabAnnex.Document, dbo.TabAnnex.Billed,
dbo.TabAnnex.Coll, dbo.TabSupplier.Annex_Complete,
dbo.TabSupplier.Status
FROM dbo.TabSupplier LEFT OUTER JOIN
dbo.TabAnnex ON dbo.TabSupplier.[ID #] = dbo.TabAnnex.[ID
#]
As you can see, qryAnnex_View contains a number of
calculated fields, particularly called RCost,
NRWorkyearCost, NRCost,and NRManpower (which is not
calculated).
Primarily, the fields are from the table
called 'TabAnnex'or derived from a combination
of 'TabAnnex' and 'TabSupplier' fields. See table
structures below:
TabAnnex Table:
Column Name Data Type Length
s_GUID uniqueidentifier 16
[ID #] nvarchar 50
Receiver nvarchar 50
Categories nvarchar 50
SubCategory nvarchar 50
Support nvarchar 50
RDescription nvarchar 50
RUnits real 4
RUnitCost money 8
NRManpower real 4
NRDescription nvarchar 50
NRUnits real 4
NRUnitCost money 8
Document nvarchar 50
Billed money 8
Coll money 8
TabSupplier Table:
Column Name Data Type Length
s_GUID uniqueidentifier 16
[ID #] nvarchar 6
SRAN nvarchar 8
JULIAN nvarchar 6
Receiver nvarchar 100
Supplier nvarchar 100
OBANOAC nvarchar 8
Receiver_Name nvarchar 100
Receiver_Phone nvarchar 50
Receive_Email nvarchar 50
Location nvarchar 50
ExeComptroller bit 1
Agreement nvarchar 5
Department nvarchar 5
SR nvarchar 5
Officers int 4
Enlisted int 4
Civilians int 4
Contractors int 4
[Analyst POC] nvarchar 50
Reimbursable bit 1
Reimburse money 8
NONrbs money 8
Reimb_Positions int 4
Reimb_Notes nvarchar 250
SAM_POC nvarchar 50
Status nvarchar 20
Revd_Coord smalldatetime 4
Sent_to nvarchar 20
Date_Sent_for_Coord smalldatetime 4
Suspense_for_Coord smalldatetime 4
Coord_Complete smalldatetime 4
Recvd_for_Annex smalldatetime 4
Suspense_for_Annex smalldatetime 4
Annex_Complete smalldatetime 4
Recvd_for_Sign smalldatetime 4
Sig_Complete smalldatetime 4
Link nvarchar 255
Notes nvarchar 255
[Pending Status] bit 1
[Original_ISA_ Start_ Date] smalldatetime 4
TriAnnual_Complete smalldatetime 4
Annual_Review_Complete smalldatetime 4
Terminated_Date smalldatetime 4
WorkyearCost money 8
[Host Base] nvarchar 50
Host_ID nvarchar 1
[Office Symbol] nvarchar 50
r money 8
nr money 8
The data type for most of the fields used in the
calculations for RCost etc, are either money or real.
My problem is that when I try to use Microsoft Access'
built-in SUM function in the text box control source like
so:
=Sum([RCost])
or
=Sum([NRWorkyearCost] + [NRCost])
or
=Sum([NRManpower])
They all produce an #Error in the derived fields on the
form at runtime.
What alternatives do I have to get these calculating and
working on the form at runtime?
If one solution is to let SQL Server do the work instead
of the control source on the form by using a user defined
function (to calculate the sums), then how do I do it?
If the correct alternative is to use a user-defined
function in SQL Server, then how do I get the
view 'qryAnnex_View' to call my user-defined function?
Please help. Any suggestions would be greatly appreciated.
Thanks,
Cheryl
I need help with an ADP Project. First, I'm working on an
MS ADP project with a SQL Server 2000 database backend. I
have a subform and a form that both use the SUM function,
=Sum([field name]) in a text box control on the forms.
The Record Source for both forms (which are bound) is a
view called 'qryAnnex_View'. The SQL for 'qryAnnex_View'
is as follows:
SELECT DISTINCT dbo.TabSupplier.[ID #],
dbo.TabSupplier.SRAN, dbo.TabSupplier.JULIAN, dbo.TabAnnex.
[ID #] AS Expr1, dbo.TabSupplier.[ID #] AS ID,
dbo.TabSupplier.Agreement, dbo.TabSupplier.Supplier,
dbo.TabSupplier.Receiver, dbo.TabSupplier.WorkyearCost,
dbo.TabAnnex.Categories,
dbo.TabAnnex.SubCategory, dbo.TabAnnex.Support,
dbo.TabAnnex.RDescription, dbo.TabAnnex.RUnits,
dbo.TabAnnex.RUnitCost,
dbo.TabAnnex.NRManpower, dbo.TabAnnex.NRDescription,
dbo.TabAnnex.NRUnits, dbo.TabAnnex.NRUnitCost,
COALESCE (dbo.TabAnnex.NRManpower *
dbo.TabSupplier.WorkyearCost, 0) AS NRWorkyearCost,
COALESCE (dbo.TabAnnex.RUnits * dbo.TabAnnex.RUnitCost, 0)
AS RCost,
COALESCE (dbo.TabAnnex.NRUnits * dbo.TabAnnex.NRUnitCost,
0)AS NRCost, dbo.TabAnnex.Document, dbo.TabAnnex.Billed,
dbo.TabAnnex.Coll, dbo.TabSupplier.Annex_Complete,
dbo.TabSupplier.Status
FROM dbo.TabSupplier LEFT OUTER JOIN
dbo.TabAnnex ON dbo.TabSupplier.[ID #] = dbo.TabAnnex.[ID
#]
As you can see, qryAnnex_View contains a number of
calculated fields, particularly called RCost,
NRWorkyearCost, NRCost,and NRManpower (which is not
calculated).
Primarily, the fields are from the table
called 'TabAnnex'or derived from a combination
of 'TabAnnex' and 'TabSupplier' fields. See table
structures below:
TabAnnex Table:
Column Name Data Type Length
s_GUID uniqueidentifier 16
[ID #] nvarchar 50
Receiver nvarchar 50
Categories nvarchar 50
SubCategory nvarchar 50
Support nvarchar 50
RDescription nvarchar 50
RUnits real 4
RUnitCost money 8
NRManpower real 4
NRDescription nvarchar 50
NRUnits real 4
NRUnitCost money 8
Document nvarchar 50
Billed money 8
Coll money 8
TabSupplier Table:
Column Name Data Type Length
s_GUID uniqueidentifier 16
[ID #] nvarchar 6
SRAN nvarchar 8
JULIAN nvarchar 6
Receiver nvarchar 100
Supplier nvarchar 100
OBANOAC nvarchar 8
Receiver_Name nvarchar 100
Receiver_Phone nvarchar 50
Receive_Email nvarchar 50
Location nvarchar 50
ExeComptroller bit 1
Agreement nvarchar 5
Department nvarchar 5
SR nvarchar 5
Officers int 4
Enlisted int 4
Civilians int 4
Contractors int 4
[Analyst POC] nvarchar 50
Reimbursable bit 1
Reimburse money 8
NONrbs money 8
Reimb_Positions int 4
Reimb_Notes nvarchar 250
SAM_POC nvarchar 50
Status nvarchar 20
Revd_Coord smalldatetime 4
Sent_to nvarchar 20
Date_Sent_for_Coord smalldatetime 4
Suspense_for_Coord smalldatetime 4
Coord_Complete smalldatetime 4
Recvd_for_Annex smalldatetime 4
Suspense_for_Annex smalldatetime 4
Annex_Complete smalldatetime 4
Recvd_for_Sign smalldatetime 4
Sig_Complete smalldatetime 4
Link nvarchar 255
Notes nvarchar 255
[Pending Status] bit 1
[Original_ISA_ Start_ Date] smalldatetime 4
TriAnnual_Complete smalldatetime 4
Annual_Review_Complete smalldatetime 4
Terminated_Date smalldatetime 4
WorkyearCost money 8
[Host Base] nvarchar 50
Host_ID nvarchar 1
[Office Symbol] nvarchar 50
r money 8
nr money 8
The data type for most of the fields used in the
calculations for RCost etc, are either money or real.
My problem is that when I try to use Microsoft Access'
built-in SUM function in the text box control source like
so:
=Sum([RCost])
or
=Sum([NRWorkyearCost] + [NRCost])
or
=Sum([NRManpower])
They all produce an #Error in the derived fields on the
form at runtime.
What alternatives do I have to get these calculating and
working on the form at runtime?
If one solution is to let SQL Server do the work instead
of the control source on the form by using a user defined
function (to calculate the sums), then how do I do it?
If the correct alternative is to use a user-defined
function in SQL Server, then how do I get the
view 'qryAnnex_View' to call my user-defined function?
Please help. Any suggestions would be greatly appreciated.
Thanks,
Cheryl