DSum problem, please help!!

[Chissy]

Hi,



I'm stuck with the DSum syntax and can't seem to move forward;

I have a table called RawMeatStock & would like to be able to move different types of stock items (meat) from one location (freezers) to another. In order to do that, I first want to check whether the weight the user wants to move is less than or equal to that in the specified location. I am using a form with combo boxes for the user to select the meat item, old location and new location, and a text box to enter the weight to be moved.

After doing a little bit of research, I decided to use DSum. I'm using the following DSum query to calculate the sum of the weight of the stock item being moved:


WeightSum = DSum("[weight]", "RawMeatStock", "[RawMeatStock.code] = '" & Me.cboRawCode & "' " And [RawMeatStock.location] = " '" & Me.CboOldLoc & "'")

I have also tried using variables to store the selection:

txtTest.Value = DSum("weight", "RawMeatStock", "([code] = '" & [rawCode] & "' " And [location] = " '" & [oldLoc] & "' )")

I have tried moving the " ' " around as suggested in different posts in the forum but none of them seem to work, I get this error every time:

"Run-time error '2456'

Microsoft Access can't find the field '|' referred to in your expression"

Even when I use raw data like this:

txtTest.Value = DSum("weight", "RawMeatStock", "([code] = '" & [B71 Kidney] & "' " And [location] = " '" & [C1 Chiller1] & "' )")

Please can somebody tell me what I'm doing wrong!!


*EDIT*

Alternatively, I could use a query for this, however, i do not know how to save the query result, could someone help with that too?

Query:

Function GetWeightSum() As Integer
'This function gets the total weight of a meat type existing in the specified location
Dim rawCode As String
Dim oldLoc As String
Dim newLoc As String
Dim moveWeight As String
rawCode = cboRawCode.AfterUpdate
oldLoc = CboOldLoc.AfterUpdate
newLoc = CboNewLoc.AfterUpdate
moveWeight = txtWeight.Value

Dim db As Database
Dim myRS As Recordset
Dim WeightSum As String

Set db = CurrentDb()


Set myRS = db.OpenRecordset("SELECT Sum(weight) AS TheSum FROM RawMeatStock WHERE code='" & rawCode & "' And location='" & oldLoc & "';")
If Not myRS.EOF Then
WeightSum = myRS.Fields("RawMeatStock.weight") 'I get an error on this line
Else
WeightSum = ""
End If
myRS.Close
GetWeightSum = WeightSum

Set myRS = Nothing
Set db = Nothing

End Function

[rpeare]

WeightSum = myRS.Fields("TheSum")

You're giving your dsum an alias in your SQL statement, you just need to call that aliased field name when you open your recordset.

[Chissy]

Hi rpeare,

Thanks for the response, I still got an error after changing that, this is what's working now:

WeightSum = myRS.Fields("TheSum").Value

Any idea how to get DSum to work? It would save me a whole lot of coding

[rpeare]

Can you post an example of your database just flush out any sensitive information you have if there is any and post a zipped up file.