Dsum with date

[syedadnan]

Regards,

I shall be very much obliged if someone really pull me out from this situation, i am having a query showing customer as client, paid amount as pamount, billingdate, payment date as pdate.

I want to get dsum of pamount specific for each client with date criteria for eaxample if i open query through form mentioning start and end date then the sum must vary as per the date given. the date is "billingdate" as mentioned above

Please help here...

 

[pbaldy]

This should get you started:

http://access.mvps.org/access/general/gen0018.htm

 

[syedadnan]

Regards,

Thanks for your reply, i seen the link you sent but i am wondering to know how to use this formula as my fields like, client, pamount and billingdate i am pasting the copy of one formula from the link..

DLookup("FieldName", "TableName", "Criteria = #" & forms!FormName!ControlName & "#")

second one is ...

DLookup("FieldName" , "TableName" , "Criteria= #date#")

i am new to access not knowing how to manage this field with my fields please help..

 

[pr2-eugin]

Why not use TOTAL Query instead of using DSum? Check out : Creating Totals in Queries.

 

[syedadnan]

Why not use TOTAL Query instead of using DSum? Check out : Creating Totals in Queries.

Absolutely not getting the correct solution, i want to get sum of paid amount with the date mentioned in the query how it will solve kindly do it with example

 

[mikejaytlabustro]

Regards,

I shall be very much obliged if someone really pull me out from this situation, i am having a query showing customer as client, paid amount as pamount, billingdate, payment date as pdate.

I want to get dsum of pamount specific for each client with date criteria for eaxample if i open query through form mentioning start and end date then the sum must vary as per the date given. the date is "billingdate" as mentioned above

Please help here...

Try this one View attachment Sample.pdf

 

[pr2-eugin]

Something along the lines of..

SELECT client, Sum(pamount) As SumOfPayAmount 
FROM [COLOR=Red][B]theTableName[/B][/COLOR]
WHERE billingdate BETWEEN Forms!FormName!ControlName AND Date()
GROUP BY client;

Make sure that the Form is kept open when you run this Query.

 

[syedadnan]

Something along the lines of..

SELECT client, Sum(pamount) As SumOfPayAmount 
FROM [COLOR=Red][B]theTableName[/B][/COLOR]
WHERE billingdate BETWEEN Forms!FormName!ControlName AND Date()
GROUP BY client;

Make sure that the Form is kept open when you run this Query.

Thanks for the reply.. please let me know how to set form controlname as i am not understanding this ?

WHERE billingdate BETWEEN Forms!FormName!ControlName AND Date()

 

[syedadnan]

Try this one View attachment 50678

Very much thankfull for the effort you made for my case..

please let me know what to do in that situation if the fields of query are some more and in that case the sum is not coming in one

 

[pr2-eugin]

Thanks for the reply.. please let me know how to set form controlname as i am not understanding this ?

Bob explains this with pictures.. http://www.btabdevelopment.com/ts/frmrefinqry

 

[mikejaytlabustro]

Very much thankfull for the effort you made for my case..

please let me know what to do in that situation if the fields of query are some more and in that case the sum is not coming in one

Can you illustrate an example in excel regarding on what kind of output you want to achieve?