Hello all!
I have a problem with suiting data in ASP. When I generate a query with a
parameter (WEEK between 200402 and 200407) in MS Access 97 I will get such
results:
GROUP WEEK VALUE
GR_1 200402 1
GR_1 200403 2
GR_1 200404 3
GR_1 200405 4
GR_1 200406 5
GR_1 200407 6
GR_2 200402 11
GR_2 200403 12
GR_2 200404 13
GR_2 200405 14
GR_2 200406 15
GR_2 200407 16
When I use the following code:
----------------------------------------------------------------------------
-----------------------
Set objRS = CreateObject("ADODB.Recordset")
strQuery="SELECT IIf([GRUPA] Is Not Null,[GRUPA],'MISC') AS [GROUP],
IIf(DatePart('ww',[Bill_date],0,2) Between 0 And 9,Year([Bill_date]) & '0' &
DatePart('ww',[Bill_date],0,2),Year([Bill_date]) &
DatePart('ww',[Bill_date],0,2)) AS BD, Sum(IIf([BillT] Is Not
Null,CLng(IIf([BillT]='IVS',[Net
value]*(-1),IIf([BillT]='RE',0,IIf([BillT]='S2',0,[Net value])))),0)) AS
Total FROM SalesInvoices AS SalesInvoices_1 LEFT JOIN Gpl_Gpc2 ON
SalesInvoices_1.Prod_hier = Gpl_Gpc2.GPC GROUP BY IIf([GRUPA] Is Not
Null,[GRUPA],'MISC'), IIf(DatePart('ww',[Bill_date],0,2) Between 0 And
9,Year([Bill_date]) & '0' & DatePart('ww',[Bill_date],0,2),Year([Bill_date])
& DatePart('ww',[Bill_date],0,2)) HAVING (((IIf([GRUPA] Is Not
Null,[GRUPA],'MISC'))<>'Cable') AND ((IIf(DatePart('ww',[Bill_date],0,2)
Between 0 And 9,Year([Bill_date]) & '0' &
DatePart('ww',[Bill_date],0,2),Year([Bill_date]) &
DatePart('ww',[Bill_date],0,2)))>="& request.form("data_od") & " And
(IIf(DatePart('ww',[Bill_date],0,2) Between 0 And 9,Year([Bill_date]) & '0'
& DatePart('ww',[Bill_date],0,2),Year([Bill_date]) &
DatePart('ww',[Bill_date],0,2)))<= " & request.form("data_do") & ")) ORDER
BY IIf([GRUPA] Is Not Null,[GRUPA],'MISC'),
IIf(DatePart('ww',[Bill_date],0,2) Between 0 And 9,Year([Bill_date]) & '0' &
DatePart('ww',[Bill_date],0,2),Year([Bill_date]) &
DatePart('ww',[Bill_date],0,2));"
objRS.open strQuery, "Data Source=Panel; PWD=XXXX!; UID=User1; Data
Source=TransOut; PWD=XXXX!; UID=User1;"
response.ContentType = "Text/HTML"
Response.write "<table align=center>"
While Not objRS.EOF
Response.Write ("<tr><td>" & objRS("GROUP") & "</td><td>" & objRS ("BD") &
"</td><td>" & objRS ("Total") & "</td></tr>")
objRS.MoveNext
Wend
Response.write "</table>"
objRS.Close
objConn.Close
Set objRS = Nothing
Set objConn = Nothing
----------------------------------------------------------------------------
------------
I will get such data from the same query but generated by ASP language call:
GROUP WEEK VALUE
GR_1 200401 1
GR_1 200402 2
GR_1 200403 3
GR_1 200404 4
GR_1 200405 5
GR_1 200406 6
GR_2 200401 11
GR_2 200402 12
GR_2 200403 13
GR_2 200404 14
GR_2 200405 15
GR_2 200406 16
As you can see the value of VALUE field for 200401 generated in MS Access is
different than during the ASP call.
Can you tell me why? I do not change a query while putting a SQL code from
MS Access SQL view to ASP code.
I only add request("data_od") and request("data_do") which is a parameter
taking from a form.
I do not know where I make a mistake, because everything is good, but I
don`t know why there is a value of VALUE
field for 200402 (MS Access) put in VALUE field for 200401 (ASP). Maybe I
make a mistake with calling.
Can you help me?
I would be very grateful for any posts
Kind regards
Marcin from Poland