Expression to pull this months dollar value.

[vidus]

Hello again friends.
I have attempted to go on my own and try to write this myself but I guess there is something wrong. I am new so dont laugh if its really really wrong.

I am trying to display in a text box the sum of the field "Value $" in table "2009"... but for the current month only. So when you are looking at a record, it will just display the sum of all the records in that particular month. I know there is a totals toggle filter that can be used with the date filter "this month" or "last month" or whatever but im trying to make things simpler because we often need to see out current monthly revenue, it would be nice to have it right on the form for easy review.

Here is what I have... it may be well wrong haha, but at least I tried.

=DSum("[Value $]","[2009]","[Date] = 'date(mm)'")

Any help appreciated as always.

 

[G37Sam]

I think you'll need to use the Month function somewhere in your criteria

month([Date]) = month(now) something like that

 

[jjturner]

Both of you have just about got it. I think this should be what you need:

=DSum("[Value $]","[2009]","Month([myDate]) = Month(Date())")

vidus - notice I changed the name of your field from "Date" to an arbitrary name of "myDate". "Date" is a reserved word in Access so you shouldn't be using it for a field name unless you're wishing trouble for yourself down the road

Also, I would avoid using special characters in your field names as well (i.e., "$") since that also 'means' something to Access...

HTH,
John

 

[vidus]

Both of you have just about got it. I think this should be what you need:

=DSum("[Value $]","[2009]","Month([myDate]) = Month(Date())")

vidus - notice I changed the name of your field from "Date" to an arbitrary name of "myDate". "Date" is a reserved word in Access so you shouldn't be using it for a field name unless you're wishing trouble for yourself down the road

Also, I would avoid using special characters in your field names as well (i.e., "$") since that also 'means' something to Access...

HTH,
John

Thanks for the tips, ive switched the column names and applied the dsum as follows but it doesnt seem to display anything...

=DSum("[JobValue]","[2009]","Month([JobDate]) = Month(Date())")

I guess thats better than the #error that I got... did I miss anything?

 

[jjturner]

my bad - try:

=DSum("[JobValue]","[2009]","Month([JobDate]) = " & Month(Date()))

 

[vidus]

my bad - try:

=DSum("[JobValue]","[2009]","Month([JobDate]) = " & Month(Date()))

nufin... just leaves the text box blank like before. theres something missing i just dont know enough to be able to find out what it is.

 

[jjturner]

I am trying to display in a text box the sum of the field "Value $" in table "2009"... but for the current month only. So when you are looking at a record, it will just display the sum of all the records in that particular month.

Ok, I'm a bit confused. You state above that you want current month, but you also say that the month total should be associated to the month for the current record. So then what if you're on a current record for a month different from current month?

Anyway, as you can see, I'm winging it tonight with my syntax. Try:

=DSum("JobValue","2009","Month([JobDate]) = " & Month(Date()))

(It's been awhile since I used DSum, but I seem to recall that it's pretty fussy when it comes to syntax)

HTH,
John

 

[vidus]

Ok, I'm a bit confused. You state above that you want current month, but you also say that the month total should be associated to the month for the current record. So then what if you're on a current record for a month different from current month?

Anyway, as you can see, I'm winging it tonight with my syntax. Try:

=DSum("JobValue","2009","Month([JobDate]) = " & Month(Date()))

(It's been awhile since I used DSum, but I seem to recall that it's pretty fussy when it comes to syntax)

HTH,
John

still nothing..

to clarify, i want this to display the sum value of "JobValue" for whatever month that record is in.

So if your working with a record from June ("JobDate"), it would be appropriate for this to display the total value ("JobValue") for June... regardless of what month it is at this very moment.

 

[jjturner]

Well, you have me stumped now That should've at least returned you a total for July.

To tailor it to your requirements, change it to:
=DSum("JobValue","2009","Month([JobDate]) = " & Month([JobDate]))

(This is assuming your form is bound to the table [2009] as the RecordSource. You may need to explicitly reference the form control holding [JobDate], but I think it will interpret your field reference)

Q: Is the data type for your [JobDate] field in [2009] table actually Date data type or Text? That's about the only thing I can think of which may be gumming things up at this point...

 

[vidus]

To tailor it to your requirements, change it to:
=DSum("JobValue","2009","Month([JobDate]) = " & Month([JobDate]))

This works flawlessly as far as I can tell. We will be running it from now on and hope it does ok. Thank you so much for your assistance, its been invaluable here.

 

[jjturner]

Excellent! Glad you got things working and all the best with your project

Cheers,
John

 

[vidus]

The only little bug I can see is that it does not update the total after adding a new job unless I restart access... is there an afterupdate i could do here?

 

[jjturner]

Absolutely - In your form's AfterUpdate event in the code module put:

Me.Requery

I think that should do it

 

[vidus]

Absolutely - In your form's AfterUpdate event in the code module put:

Me.Requery

I think that should do it

Yup that does is, I added it in the undo as well. When I update a value and it re-calculates, it brings me to a random record for some reason. I apologize for yet another question , but why would it not stay on the record that was just updated?

 

[jjturner]

No worries -

I was debating whether to suggest a more specific code in the form's AfterUpdate:

Me!myJobValueTextBox.Requery (use actual name of your text box)

Thinking it wouldn't have an effect, but actually it should probably work now that I think about it. This will hopefully save you from mussing up the recordset each time you update . . .

 

[vidus]

No worries -

I was debating whether to suggest a more specific code in the form's AfterUpdate:

Me!myJobValueTextBox.Requery (use actual name of your text box)

Thinking it wouldn't have an effect, but actually it should probably work now that I think about it. This will hopefully save you from mussing up the recordset each time you update . . .

Testing this one for a few minutes now.. so far works great! It was actually funny before, I would update and be transfered to a completely random record, with absolutely no pattern. Very strange.

Thanks again. This will save us so much frustration working with silly totals and date filter toggles.

 

[jjturner]

No probs - always happy to hear when things get sorted

FYI, the Requery on your form's entire recordset is basically like reshuffling a deck of cards. Unless you have indexes on your table, or are using a query to order the records for the form's Recordsource, then it probably will navigate you to a random record, but presumably at the *beginning* of your recordset. I'd have to double-check the authorities on that, but I think I'll call it an evening for now

Cheers,
John