Dsum with date criteria

[syedadnan]

Regards,

I have stuck with this problem;

i have a query named "EXP" in this query i have fields of expamount, expdate and same expdate field is further formated with Format([expDate],"yyyy-mm") and i named it AscMnth

I want in another query to see dsum of expamount from query exp with criteria of AscMnth as in another query i have also formated the date field with Format([SDate],"yyyy-mm") and named it AscMnth

Conclusion: i want dsum of expamount in when AscMnth is same in both queries

 

[sneuberg]

Your post isn't written very clearly but guessing at what you are asking I suggest creating an addition query from "ExP" and this other query such that this addition query returns the records you want summed and has the field expamount. Then your DSum would be simply

DSUM("[examount]", "[the name of the additional query]")

 

[syedadnan]

Your post isn't written very clearly but guessing at what you are asking I suggest creating an addition query from "ExP" and this other query such that this addition query returns the records you want summed and has the field expamount. Then your DSum would be simply

DSUM("[examount]", "[the name of the additional query]")

No this is not the case in fact.. if i makes another query then it returning total sum from all months i need sum of months like as above i said that field mnth is the format of date field in both query and it is producing month name like May-16 so i need Dsum with the criteria of mnth field which is date field format in both queries..

 

[jdraw]

This Format([SDate],"yyyy-mm") will not give you a result such as May-16.

I suggest you show some data, both the before and after(expected) values to avoid any confusion.

 

[sneuberg]

Please post the SQL of the two queries. Maybe I can figure out from that what you are asking for. Also some sample data and the desired result from the DSum would be helpful.

 

[syedadnan]

This Format([SDate],"yyyy-mm") will not give you a result such as May-16.

I suggest you show some data, both the before and after(expected) values to avoid any confusion.

Plz see attached sheet

 

[syedadnan]

Plz see attached sheet

The formula is Format([SDate],"mmmm-yyyy")

 

[sneuberg]

Try

Total Expense: DSum("[ExpAmount]","[EXP]","[ExpM] = '" & [AscMnth] & "'")

 

[syedadnan]

Try

Total Expense: DSum("[ExpAmount]","[EXP]","[ExpM] = '" & [AscMnth] & "'")

PERFECT !!! Thanks a lot working accurate...

 

[sneuberg]

Glad that works.

Please be aware that a DSum within a query can make the query slow. This may not be a problem if you don't have too many records. But if this becomes a problem then I suggest looking at a different way of doing this. I think you could make a query from the EXP query that would group by the AscMnth and sum the ExpAmount. Then make another query than joins this group by query with the Sales query on the ExpM and AscMnth.