Solved

Select Statement

Posted on 2011-09-22
16
177 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
mapShare challenge 13 70
mapAB Challlenge 35 89
Run code from text file in vb 1 34
Add a task in Outlook from access 11 32
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
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…
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…

743 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

10 Experts available now in Live!

Get 1:1 Help Now