Dim, Set, ???

[CharlieMike73]

Hi, I am trying yo take two values out of a D-Base and do a calc on them...

<%
Dim Unit_Cost

Unit_Cost = (rsCountDWRs.Fields.Item(&quot;DWR_TOTAL_COST&quot.Value)/(rsCountDWRs.Fields.Item(&quot;ACCOMP_QTY&quot.Value)
%>

But is it not working, I have also tried

<%
Set Unit_Cost = ...
%>

and also tried the statement without Dim or Set.

I just need to take a value from one field divide it by another and put the result into a HTML table

Any ideas???

 

[FesterSXS]

assuming your recordset, rsCountDWRs, is setup correctly - use this:

<%
Dim Unit_Cost

Unit_Cost = rsCountDWRs(&quot;DWR_TOTAL_COST&quot;)/rsCountDWRs(&quot;ACCOMP_QTY&quot;)
%>

Tony

http://www.phoenix-paints.co.uk/portfolio

 

[onpnt]

you need to enclose the division in quotes also
Unit_Cost = ((rsCountDWRs.Fields(&quot;DWR_TOTAL_COST&quot)/(rsCountDWRs.Fields(&quot;ACCOMP_QTY&quot))
I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

 

[onpnt]

did you test the rs values to see if you have a value before making the calc.

not sure about the () idea but it may be it
I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

 

[CharlieMike73]

Yes, the RSet is set up correctly, I am getting lots of other data fine from the Oracle Database.

Here is the full connection and SQL String:

<%
Dim rsCountDWRs
Dim rsCountDWRs_numRows

Set rsCountDWRs = Server.CreateObject(&quot;ADODB.Recordset&quot
rsCountDWRs.ActiveConnection = MM_DevMATS2_Instance_STRING
rsCountDWRs.Source = &quot;SELECT DWR_SYS_ID, REPORTING_UNIT, ACT_ID, DWR_DATE, ACCOMP_QTY, LABOR_TOTAL_COST+EQUIP_TOTAL_COST+MATL_TOTAL_COST DWR_TOTAL_COST FROM DAILY_WORK_REPORT WHERE DWR_DATE BETWEEN TO_DATE('01-JAN-02') and TO_DATE('31-Jan-02') ORDER BY REPORTING_UNIT, DWR_DATE DESC, DWR_SYS_ID;&quot;
rsCountDWRs.CursorType = 0
rsCountDWRs.CursorLocation = 2
rsCountDWRs.LockType = 1
rsCountDWRs.Open()

rsCountDWRs_numRows = 0
%>
<%
Dim Unit_Cost

Unit_Cost = rsCountDWRs(&quot;DWR_TOTAL_COST&quot/rsCountDWRs(&quot;ACCOMP_QTY&quot
%>

and this gives me the following error:

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch
/m_o/mats/reports/TMP8wdwyzi6zo.asp, line 22

Line 22 is the Dim Unit_Cost line

 

[onpnt]

alright try this let me know the outcome
Dim DWR, QTY
response.write(DWR)
response.write(QTY)
DWR = rsCountDWRs.Fields(&quot;DWR_TOTAL_COST&quot.Value
QTY = rsCountDWRs.Fields(&quot;ACCOMP_QTY&quot.Value
Unit_Cost = (DWR/QTY)
response.write(Unit_Cost)
I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.
admin@onpntwebdesigns.com

 

[FesterSXS]

In your database, are the two fields, DWR_TOTAL_COST and ACCOMP_QTY, both set up as number fields??

YOu could adjust your code to force the two fields to the same type:

Unit_Cost = CInt(rsCountDWRs("DWR_TOTAL_COST"))/CInt(rsCountDWRs("ACCOMP_QTY"))

Tony

http://www.phoenix-paints.co.uk/portfolio

 

[CharlieMike73]

ok, if you look at the SQL you will see that DWR_TOTAL_COST does not really exist, it is just the name assigned to a calculation.

LABOR_TOTAL_COST+EQUIP_TOTAL_COST+MATL_TOTAL_COST DWR_TOTAL_COST

Note: the above code is all on one line!

This works to give me the combined value of the three fields in each RS (RecordSet), all fields are decimal ($ amounts).

I tried to make a simular one like so in the SQL statement:

(LABOR_TOTAL_COST+EQUIP_TOTAL_COST+MATL_TOTAL_COST)/ACCOMP_QTY UNIT_TOTAL_COST

Had this worked, it would have given me two virtual columns (DWR_TOTAL_COST, and UNIT_TOTAL_COST) that I would referrence in the ASP pages


If it makes any difference, I am connecting to an Oracle 8i Database Instance

 

[CharlieMike73]

found my answer - in a very small number of cases, I was trying to divide by 0 (zero)

fixed by...

SELECT
DWR_SYS_ID,
REPORTING_UNIT,
ACT_ID,
DWR_DATE,
ACCOMP_QTY,
LABOR_TOTAL_COST+EQUIP_TOTAL_COST+MATL_TOTAL_COST DWR_TOTAL_COST,
(LABOR_TOTAL_COST+EQUIP_TOTAL_COST+MATL_TOTAL_COST)/ACCOMP_QTY UNIT_TOTAL_COST
FROM
DAILY_WORK_REPORT
WHERE
DWR_DATE BETWEEN TO_DATE('01-JAN-02') and TO_DATE('31-Jan-02')
and ACCOMP_QTY > 0
ORDER BY
REPORTING_UNIT,
DWR_DATE DESC,
DWR_SYS_ID
;