Solved

Select Statement

Posted on 2011-09-22
16
181 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Computer science students often experience many of the same frustrations when going through their engineering courses. This article presents seven tips I found useful when completing a bachelors and masters degree in computing which I believe may he…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

776 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