Assign the value from an db.OpenRecordset("Select ... statement to a variable
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]![CollectShippingQt
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
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]
Count is itself a function, so if Count in your SQL is a field name, that could cause a problem.
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.)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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")
Substitute your table and field names.
Instead of a hard-coded number to match, you can use a variable:
DLookup("Sum(tblData.Amount)", "tblData", "[ID] = " & dblNumber)
maybe you mean dsum
dsum("amount","sale","InvN
dsum("amount","sale","InvN
ASKER
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
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
mdlp,
there was an earlier post that your accepted answer, and you ignore it.
there was an earlier post that your accepted answer, and you ignore it.
ASKER
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
mdlp
ASKER
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
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
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.
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.
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
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
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
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
ASKER
Thank you taking the time a to remove the stripe, and give your assesment mbizup.
mdlp
mdlp
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 & "'")