Problem using Ciriteria for Dsum in Query Expression

[russ1985]

I am creating a basic stock control query.

I have

1 table which is stockitems.
1 table which is orders
1 table which is stockdelivery.

I have created a query based on the stock items table and have created an expression to show the amount of stock in total that has been ordered. but I cannot seem to get it correct.

I have tried two different code lines

TotalPurchased: DSum("[NumberOfItems]","[StockDelivery]"," [ITEMDescription] = """ & [Description] & """")

Does not display anything in the field when the query is viewed.

TotalPurchased: DSum("[NumberOfItems]","[StockDelivery]"," [ITEMDescription] = " & [Description])

Displayer error in the field(syntax error) missing operator.

Both [ITEMdescription] and [description] are text fields and [numberofitems] is a number field(double).

Can anyone see what I am doing wrong.

 

[rainman89]

Have you tried something along the lines of

TotalPurchased: DSum("[NumberOfItems]","[StockDelivery]"," [ITEMDescription] = '" & [Description] & "'")

 

[russ1985]

Thanks for supplying that but it gives the same problem as first line. no error but it just displays no number in the field.

i have double checked the content of the fields and they do match the criteria so they should show. but is is acting as if nothing matches the criteria.

 

[rainman89]

Ah my bad. Are you doing this from a form or directly in the query?


If from a form you need to reference the form that you are pulling the description field from

If its a textbox you would need
TotalPurchased: DSum("[NumberOfItems]","[StockDelivery]"," [ITEMDescription] = '" & [Forms]![yourformname].[Description] & "'")

If its a combobox you would need something else.

How is it set up?

 

[rainman89]

If its in a query, the answer I supplied earlier will work.

I have just tested this with a table matching your table
stockid
numberofitems
itemdescription

TotalPurchased: DSum("[NumberOfItems]","[StockDelivery]"," [ITEMDescription] = '" & [Description] & "'")

Returned the correct results

 

[russ1985]

Thanks for the reply.

After your confirm that it should work in principal i looked through absolutely everything and found that in my table one of the fields it was using as criteria was a list box and it was bound to the wrong column.

thanks again for all your help.

 

[highandwild]

Would it be easier to use the stock or product code as the link data as descriptions can change and should not be held in more than one table unless one wants to maintain a record of description changes.