• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

Select Statement


i am getting wrong result
result supposed to be 13 instead of 7
my code is as follow
 
Dim RsGDMArt As New ADODB.Recordset
        Set RsGDMArt = Nothing
        If RsGDMArt.State = 1 Then RsGDMArt.Close
        RsGDMArt.Open "Select sum(BD.Article) AS Tart from Bilty_Detail as BD Where BD.Ref_GM_No = '" & Text1(0).text & "'", con, adOpenKeyset, adLockOptimistic
            If RsGDMArt.RecordCount > 0 Then
                Text1(22) = RsGDMArt!Tart
                'Text1(23).text = RsGDMStatus!TWgt
            End If
        ''''''''''''''''''''''''''''''''''''''''''

Open in new window


0
crystal_Tech
Asked:
crystal_Tech
  • 6
  • 6
  • 2
  • +2
2 Solutions
 
Neil RussellTechnical Development LeadCommented:
Well without seeing your data how can we say its right or wrong.
0
 
crystal_TechAuthor Commented:

hi
i am trying to get total of Article where Ref_GM_No = 751
if there is 5 record and each record have Article 2  then result should be 10, but its not,
Result displayed 7 instead of 10
0
 
mlmccCommented:
Need to see more code and the data.

mlmcc
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
HooKooDooKuCommented:
I don't know if it is making a difference, but I see you are using DB in the Sum clause but DB isn't defined until from clause.

So as a first step, try changing the query to something like this:

RsGDMArt.Open "Select sum(Bilty_Detail.Article) AS Tart from  Bilty_Detail Where Bilty_Detail.Ref_GM_No = '" & Text1(0).text & "'", con, adOpenKeyset, adLockOptimistic
       
Of course the other issue might be the data type in Article.  If it is a numeric field, then the Sum() function should work fine.  But if it is a text-type filed, might you need to first convert the text to numbers before you can execute a Sum() against it?
0
 
TommySzalapskiCommented:
HooKoo, that's not an issue since SQL looks at the FROM clause first.

crystal_Tech, I agree that we can't be sure without more info. You could try some debugging. Change the query to
RsGDMArt.Open "Select * from Bilty_Detail as BD Where BD.Ref_GM_No = '" & Text1(0).text & "'", con, adOpenKeyset, adLockOptimistic

That way you can see all the data it is finding and get a better idea of what is wrong.      
0
 
crystal_TechAuthor Commented:


Ref_GM_No  ( data type is text ) not number
0
 
crystal_TechAuthor Commented:

TommySzalapski:, i already tried this query and record i get in RsGDMArt.recordcount >  0
text2(0).text = RsGDMArt.recordcount,
i am getting correct record count in text2(0)

RsGDMArt.Open "Select * from Bilty_Detail as BD Where BD.Ref_GM_No = '" & Text1(0).text & "'", con, adOpenKeyset, adLockOptimistic

0
 
TommySzalapskiCommented:
But what else does that query show? Also, as HooKooDooKu asked, what is the type of BD.Article?
0
 
crystal_TechAuthor Commented:

TommySzalapski:, getting all records belongs to Ref_GM_No.
BD.Article data type is Number
0
 
TommySzalapskiCommented:
Can you take a screen shot of the output of the * query? Does it show all 5 records with a 2 in the Article column for each one?
0
 
crystal_TechAuthor Commented:
please check screen shot of my result
and code

If RsGDMStatus.State = 1 Then RsGDMStatus.Close
        RsGDMStatus.Open "Select BiltyNo, Article from Bilty_Detail Where Ref_GM_No = '" & Text1(0).text & "'", con, adOpenKeyset, adLockOptimistic
            If RsGDMStatus.RecordCount > 0 Then
            Set DataGrid1.DataSource = RsGDMStatus
'            End If
            Exit Sub

Open in new window

result.doc
0
 
TommySzalapskiCommented:
That certainly looks like it should sum to 7. Why are you expecting it to be 10?
0
 
TommySzalapskiCommented:
Are you mixing up the BiltyNo and Ref_GM_No?
0
 
crystal_TechAuthor Commented:

<<Are you mixing up the BiltyNo and Ref_GM_No?>>
what do you mean mixing up
i want record belongs to Ref_GM_No
0
 
mlmccCommented:
How about selecting Ref_GM_No and displaying it to ensure you are getting the data you think you are.

mlmcc
0
 
TommySzalapskiCommented:
The data in your screen shot clearly sums to 7. I see no problem at all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now