Solved

Select Statement

Posted on 2011-09-22
16
189 Views
Last Modified: 2012-08-13

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
Comment
Question by:crystal_Tech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
  • +2
16 Comments
 
LVL 37

Expert Comment

by:Neil Russell
ID: 36582452
Well without seeing your data how can we say its right or wrong.
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36582643

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
 
LVL 101

Expert Comment

by:mlmcc
ID: 36582776
Need to see more code and the data.

mlmcc
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 36582848
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 36582947
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
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36582969


Ref_GM_No  ( data type is text ) not number
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36583028

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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 36583161
But what else does that query show? Also, as HooKooDooKu asked, what is the type of BD.Article?
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36583259

TommySzalapski:, getting all records belongs to Ref_GM_No.
BD.Article data type is Number
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 36583360
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
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36583487
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
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 36583499
That certainly looks like it should sum to 7. Why are you expecting it to be 10?
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 62 total points
ID: 36583507
Are you mixing up the BiltyNo and Ref_GM_No?
0
 
LVL 1

Author Comment

by:crystal_Tech
ID: 36583776

<<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
 
LVL 101

Accepted Solution

by:
mlmcc earned 63 total points
ID: 36584559
How about selecting Ref_GM_No and displaying it to ensure you are getting the data you think you are.

mlmcc
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 36584721
The data in your screen shot clearly sums to 7. I see no problem at all.
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is about my first experience with programming Arduino.
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question