Solved

Select Statement

Posted on 2011-09-22
16
178 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
  • 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 100

Expert Comment

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

mlmcc
0
 
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 100

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A short article about problems I had with the new location API and permissions in Marshmallow
This is about my first experience with programming Arduino.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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 …

911 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now