multi criteria DSum (1 Viewer)

[Freddie b]

Can I write a DSum function in query, with two criteria?
that is, I want to collect quantities according to products from the beginning of the year to a certain date which is the date field in a form. I am trying to do a thing like this: =DSum("[qty]","salestbl","[PRODUCT H NAME] = “ field in form”[PRODUCT D NAME]") AND DATE =between “01.01.2021”, “A DATE IN FORM”.

 

[Minty]

Look at the examples in my signature, and the similar threads at the bottom of the page.

=DSum("[qty]","salestbl","[PRODUCT H NAME] = '“ & Me.YourProductFormControlName & "' AND [DATE] = #“ & Format(Me.YourDateControl , "yyyy-mm-dd") & "#"

You need to concatenate the form values and use the correct string or date delimiters.

Also, rename your Field called [Date] to something else (SalesDates or InvDate) - Date is a reserved word and will give you a load of problems further down the line.

 

[Gasman]

I tend to put the criteria into a string variable for domain functions if not a single simple criteria.
Then I can debug.print them to see if I have the syntax correct.
The when correct, I can use them in the function.
Plus, if you cannot see the error, you can copy and paste here or eslewhere, so someone can point out the error.?

 

[Freddie b]

Look at the examples in my signature, and the similar threads at the bottom of the page.

=DSum("[qty]","salestbl","[PRODUCT H NAME] = '“ & Me.YourProductFormControlName & "' AND [DATE] = #“ & Format(Me.YourDateControl , "yyyy-mm-dd") & "#"

You need to concatenate the form values and use the correct string or date delimiters.

Also, rename your Field called [Date] to something else (SalesDates or InvDate) - Date is a reserved word and will give you a load of problems further down the line.

First of all thank you for your help,

I wrote this =Nz(DSum("[SASIA]","hyrje per log cmim mes","[PRODUCT H NAME]=" & [PRODUCT D NAME] & " and[DATA]<=#" & Format([ forms!FATURE SHITJE!DAT SH],"mm/dd/yyyy") & "#"),0) where [PRODUCT D NAME] is in sub form where i am writing and forms!FATURE SHITJE!DAT SH is in main form but it shows "#name?"

 

[arnelgp]

try:

=Nz(DSum("[SASIA]","hyrje per log cmim mes","[PRODUCT H NAME]='" & [Forms]![FATURE SHITJE]![subFormName].Form![PRODUCT D NAME] & "' and [DATA]<=#" & Format(Nz([Forms]![FATURE SHITJE]![DAT SH], 0), "\#mm\/dd\/yyyy\#")

 

[Freddie b]

try:

=Nz(DSum("[SASIA]","hyrje per log cmim mes","[PRODUCT H NAME]='" & [Forms]![FATURE SHITJE]![subFormName].Form![PRODUCT D NAME] & "' and [DATA]<=#" & Format(Nz([Forms]![FATURE SHITJE]![DAT SH], 0), "\#mm\/dd\/yyyy\#")

Nothing again!

 

[CarlettoFed]

Maybe if you attach a sample file it would be easier to help you.

 

[Gasman]

That is because you do not use the subform name, but the subformcontrol name.
Sometimes they are the same, but not always.
You are meant to change subformname to your actual subformcontrol name.
AGAIN!! if you put the criteria into a string variable, you can debug.print it, and if you get it correct, then use in the function.
You can also post the output here for review.

 

[Freddie b]

Maybe if you attach a sample file it would be easier to help you.

Hi, have you a e mail? i tray to send you the file

 

[CarlettoFed]

You have a message in your private mail.

 

[Cronk]

Also the table name hyrje per log cmim mes should be in square brackets because it has spaces.

Experienced users would call the table HyrjePerLogCmiMmes