Dsum criteria to return current month results based on date in another field (1 Viewer)

[Ray Spackman]

Sorry if I missed this in another post, bit didn't see it when searched for.

I have a form that has a [EntryDate] date/time field formatted as LongDate. This form also contains several currency formatted fields.

I am using DSum to calculate one of these currenct fields but am wanting to show only the results for the month and year that equals that of the currently viewed record based on the [EntryDate] field of the currently viewed record. Is this possible, and how would that criteria look?

Thanks in advance.

 

[vbaInet]

So, let's see what you've written.

 

[Ray Spackman]

Anything that I have tried, I had gotten from research google and this site, and nothing returned the values that I wanted. It would return the calculated dsum info based off my declarations and single text criteria, but could not achieve returning only those results based on the month and year of the [EntryDate] field of the currently viewed record. To answer your question, sicne nothing worked, I have nothing at this point to accomodate what I am looking for, or to give to you.

 

[vbaInet]

I know that your DSum() isn't working as expected but I would like to see it so I can advise you where you are going wrong or what you need to add to it.

 

[Ray Spackman]

I do understand that you are trying to help and I do appreciate it and am not trying to be a trouble, what I need to add to the criteria is the proper date format syntax that will allow it to calculate and show the records for the month and year that equals the month and year of the [EntryDate] field. I should note that I am using the Dsum in the form footer. The dsum that I have is working expectedly with a single text criteria so I just need to add to it (which I know how to add multiple criteria) the date format syntax (which I do not know) to show the calculations based on the month and year of the current record's [EntryDate] field.

 

[vbaInet]

Ray, when I'm doing diagnostics I like to see the full code.

 

[Ray Spackman]

I understand this what I have:

Dsum("[tblDailyInfo]![Cash]","tbleDailyInfo","[Location] = 'Concord'" AND "?????????????????") "??????????" is not actually there, it is the format syntax that I need to make the rest of the Dsum function work to complete my needed task.

 

[vbaInet]

That wasn't so difficult Ray

Here's the amendment to your code:

Dsum("[COLOR=Blue][Cash][/COLOR]","tbleDailyInfo","[Location] = 'Concord' AND Format([[COLOR=Red]DateField[/COLOR]], 'mm/yyyy') = " & Format([EntryDate], "mm/yyyy"))

Amend the date field in red. And notice that the field name part in blue doesn't require a full reference to the table unless you were referring to a query that had that same field name in two separate tables.

By the way, there is a more effecient way of doing this. Are you displaying the whole records from that table on your form?

 

[Ray Spackman]

No I am not swhowing all records on the form. Just the current record being entered at the time. However, the Dsum function is used to show the Dsum calculation of various fields that equal the the month and year of that record being entered. I think I know where you are going when mentioning a different method, I am entertaining the idea of creating a totals query to provide the values needed, however I thought I might the syntax you provided for that query also. Thanks for everything and your understanding.

 

[Ray Spackman]

When talking about amending the [DateField] that is in red, what am I ammending it to? I only have one date/time field and that is the [EntryDate] that I spoke of and that you put in the code.

 

[vbaInet]

I think I know where you are going when mentioning a different method, I am entertaining the idea of creating a totals query to provide the values needed, however I thought I might the syntax you provided for that query also. Thanks for everything and your understanding.

Creating a Totals query and doing a DLookup() to the Sum() field would be a quicker way than DSum(). But what I was thinking was using Count() altogether. However, since you've mentioned that you're not displaying all records in the table, you need not worry about this approach.

If [EntryDate] is also a field within your tbleDailyInfo table, then you should replace it with [EntryDate]

 

[Ray Spackman]

Okay Thanyou.

 

[vbaInet]

Let us know how it goes.

 

[Ray Spackman]

Well that did not work. When ammending the red [DateField] to my field of [EntryDate], the code did not return any values.

 

[vbaInet]

Then you've obviously got a setting wrong in your db. Let me see your db.

By the way, I should point out that we're offering our expert advice for free on here so putting a thumbs down icon or a sad face would deter us from answering your queries.

 

[Ray Spackman]

I sincerely apologize for the thumbs down and the frown face, I didn't stop to realize the affect it would have on others. I will try attach my db later on when I get home form work. As laways thank you for your help.

 

[vbaInet]

A smiley face always has a better effect

Yes, go ahead and I'll have a look at it and advise.

 

[Ray Spackman]

Okay, attached is my db. The unbound text field is labeld Cash, amoung the others, located in the footer. When viewing the form in enrty mode, the sum value for the month and year need to reflect the month and year in the [EntryDate] field at the beginning of the form. If it shows an August 2011 date in the [EntryDate] field, then the sum should reflect the sum of all cash entered in August 2011, when it changes to September 2011, then the result should reflect the sum of those records entered in September 2011. Thank you again.

 

[vbaInet]

Ray, please find attached your db. You forgot to include the code in the textbox so I can't really tell you what went wrong.

 

[Ray Spackman]

Thank you so much !!! It took me a little bit to find the changes, but finally got it working in my db. Keep up the great work and thank you for being understanding and patient with me.