We help IT Professionals succeed at work.

Assign the value from an db.OpenRecordset("Select ... statement to a variable

828 Views
Last Modified: 2013-12-20
Experts, I have to get a value that is a sum() of a column in a seperate mdb, I would usaly
open with dao and findfirst, then loop for each and keep a running total.  But as I have been reading that a select should be much faster, but I can not get it to work.

This code exist in a different database and is accessed via a network on a WAN, that is why  I am using DAO, and that is why I am not using the loop, is was to slow. Don't know if you need this information.
This is the error; "Too few Parameters expected 1
This is where it crashes;  Set rs = db.OpenRecordset("select sum(Count) from sale where InvNo = dblInvNo")

What I am doing might not be the best way so in other words What is the fastest way to get the following answer from the attached MDB;
 if dblInvNo = 2401 then dblCount = 325

Thank you very much Experts
mdlp

 
****************   THIS IS THE CODE THAT I HAVE UP TO THIS POINT
***************  It is currently attache to a Command button

Private Sub cbColShpQnty_Click()
On Error GoTo Err_cbColShpQnty_Click

Dim strDBname As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim stDocName As String
Dim dblInvNo As Double
Dim dblCount As Double

dblInvNo = [Forms]![CollectShippingQty]![tbInvNo]

strDBname = "W:\ActCost.mdb"
Set db = OpenDatabase(strDBname)
Set rs = db.OpenRecordset("Sale", dbOpenDynaset)
   
    'ERRROR = "Too few Parameters expected 1
    Set rs = db.OpenRecordset("select sum(Count) from sale where InvNo = dblInvNo")
 
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

Need to;
'Calc box size and weight
'Print pick label


Exit_cbColShpQnty_Click:
    Exit Sub

Err_cbColShpQnty_Click:
    MsgBox Err.Description
    Resume Exit_cbColShpQnty_Click
   
End Sub

ActCost.mdb
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
try this

Set rs = db.OpenRecordset("select sum([Count]) from sale where InvNo =" & dblInvNo)


if  InvNo  is text use this

Set rs = db.OpenRecordset("select sum([Count]) from sale where InvNo ='" & dblInvNo & "'")
CERTIFIED EXPERT
Top Expert 2009

Commented:
Count is itself a function, so if Count in your SQL is a field name, that could cause a problem.
CERTIFIED EXPERT
Top Expert 2009

Commented:
It is always problematic to give a field the same name as a built-in property, function, or attribute (such as Date, Time, Name, Sum, etc.)
shorvath
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2009

Commented:
Apart from that, I think you can simplify your code by just using a Dlookup expression, like this:

DLookup("Sum(tblData.Amount)", "tblData", "[ID] = 2")

Open in new window

CERTIFIED EXPERT
Top Expert 2009

Commented:
Substitute your table and field names.
CERTIFIED EXPERT
Top Expert 2009

Commented:
Instead of a hard-coded number to match, you can use a variable:
DLookup("Sum(tblData.Amount)", "tblData", "[ID] = " & dblNumber)

Open in new window

CERTIFIED EXPERT
Top Expert 2016

Commented:
maybe you mean dsum

   dsum("amount","sale","InvNo =" & dblInvNo)
 
 

Author

Commented:
Shorvath, thank you, Experts,  I am sorry but I walked away and you flooded me.

I need that way to assign the data to a the variable and This was the first that did that.

Helen_Feddema  I will change the field from count, thank you.

GREAT JOB, I have not seen all of the posts as they are flying into my mail box as I type this.

Thank you again
mdlp
CERTIFIED EXPERT
Top Expert 2016

Commented:
mdlp,

there was an earlier post that your accepted answer, and you ignore it.

Author

Commented:
I am sorry I just got back in the office and I saw all of the post and I started from the last if their is an issue, i will look over them.  I am sorry but I got called out on a support call.
mdlp

Author

Commented:
mbizup:  
Sorry for the delay, but the weekend was on me and the family had to come first.

I guess I am at a loss,  since I am a very basic programmer, I may not have asked the question correctly, and I will admit that capricorn1 did answer the issue with the Select statement but as i was testing his post, Helen_Feddema comments came in then Sorvath's post came in and I saw that Sorvaths answer addressed the issue with the select and the assignment of the variable, that is why I awarded the points to Sorvath.  

capricorn1 had you added to you post with a way to assign the value after Sorvaths answer, then I would have split the points, you have always been very gracious and abundant with your answers.  So I if I have shorted you I have to apologize.  And Helen I do appreciate you concern for my labeling the field a system name but I still do not understand your answer to my question.  
 
Now for my real question, mbizup, what would you have done, because I do not want this type of issue to come up again if I can help it;-(   Should I have split the points, because all the experts did help me some, or are the points to go the expert that answers the question fully?

Thank you all again and I am truly sorry for this!
mdlp
shorvathshorvath
CERTIFIED EXPERT

Commented:
capricorn1 & Helen_Feddema

Sorry if you think I took some points.   This is not the way I work.  I use this site to
learn as well as to help others.   When I opened this question, there were no responses.
I actually took the time to download   mdlp's  database and test the solution, before I posted it.
Many times I have done this, only to find the solution as already been accepted (usually by a link)
or the author has withdraw the question (arghh).  This is way if you look a my past posts
you will see that I provide full code solutions (when required), but don't usually get any points.
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
mdlp,

I'm dropping the administrative 'stripe'  to post this here, because it is a grey area and wide open to the Authors' individual assessments of what helped most... :-)

Had this been my own question, I might have awarded a small share of the points (50-100 points) to http:#a33174612 with the lion's share of the points going to http:#a33174689, closing the question with a clear explanation of what I found useful in each comment.  

Others may have allocated the points differently.  There is nothing wrong with splitting points, but on the other side of the coin you want to take care not to short-change someone who has completely 'nailed' your question with a thorough understanding of what was asked and a tested solution (which is what Sorvath's response did).

From my standpoint, even though I may have awarded the points slightly differently, there is nothing wrong with how you chose to close the question.

The real issue in capricorn1's Community Support request from my own reading seemed to be not about the point split, but that he felt that the accepted answer was the same as his own (which it was not).  It would have indeed been unfair if the responses were identical and a later comment had been awarded full points, with nothing going to the first.

These requests/misunderstandings do come up, and the Zone Advisors and Moderators do what we can to sort them out both in terms of fairness and technical accuracy.  The only reason we ever overturn disputed accepted answers is if they seem grossly unfair or incorrect (which is not the case here).

As an Expert who has been active here for years myself (personal perspective), these things come out in the wash.  Most of the Experts who have been around the block a few times have more points than we know what to do with, and at the end of the day they don't put dinner on the table.   The goal here is to help.  

mbizup

Author

Commented:
Thank you taking the time a to remove the stripe, and give your assesment mbizup.  
mdlp

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.