DSum with Multiple Queries (1 Viewer)

[unclefink]

Subject should say DSUM with multiple criteria, sorry.

I'm having a hard time getting this criteria to work for me.

I have a table called Extractsales that contains the following Fields:

RecordID
SalesDate
ItemSold - This is referenced to the id of an item in another table
NumberSold
TotalAmount

I'm trying to create a query giving me the total number of a specific item that was sold during current year.

I'm using Dsum syntax as followed:

Test: DSum("[Numbersold]","ExtractSales","[itemsold]=1" And "Year( [ExtractSales]![SalesDate]=Year(Date()")

When I run the query, its giving me a count of 19 which is actually the total items sold which includes item number 2. Of the 19 only 15 of those are for "item 1".

Can anyone help me. I just started figuring out Dsum syntax last night and now i'm obsessed with getting this right!

Thanks a bunch in advance.

 

[plog]

You're on the wrong track: DSums shouldn't be in queries.

Instead you should build a query to generate it:

https://support.office.com/en-us/ar...-a-query-430a669b-e7fd-4c4b-b154-8c8dbbe41c8a

Syntactically you have parts of the criteria that exist in no man's land:

"[itemsold]=1" And "...

That And is not in the string. And your missing a few closing parenthesises.

 

[unclefink]

Re: DSum with Multiple criteria

The query seems to be working, its just not adding both criteria.

As for the reference to multiple queries, i'm only using 1 query NOT multiple, I overlooked that reference on my initial creating this string.

 

[unclefink]

if it helps at all, here is a snippet of the data table i'm trying to "dsum".

See attached.

 

[plog]

Again, DSums shouldn't be in queries. Your methodology (nevermind your syntax) is incorrect.

You need to build an aggregate query to get this data using the SUM keyword of SQL. Not using the DSum function of Access.