Dsum - Please Help (1 Viewer)

[Harry Taylor]

this is probably a basic questions but i've been struggling for hours.

I have a table RA
in this table i have columns - VisitDate, Week (week number) and SC (services done)

In a Form i have a subform and use a drop down menu ([combo6]) using the Week. i.e. Week 26 lists the SC's done that week in date order (typically Mon-Fri)

I want a text box Dsum which will will show Year To Date services from the week selected - so Week26

I'm miles off but i have tried these;
=DSum([RA]![SC],[RA]![Week],"[RA]![Week] =>[combo6]")
&
=DSum("SC","RA","RA=""combo6")

 

[theDBguy]

Hi. Not sure I understand the question. Are you trying to "total" a numeric value? If not, then maybe DSum() is not the right tool for it.

 

[Harry Taylor]

Yes,
SC and Week in the table RA are numerical
I want to add the SC (in RA table) where the Week (in RA table) is less that the form [combo6] box on a form.

 

[theDBguy]

Yes,
SC and Week in the table RA are numerical
I want to add the SC (in RA table) where the Week (in RA table) is less that the form [combo6] box on a form.

Hi. If you want "less than," then the expression you posted earlier probably should have used the <= sign instead. For example:

=DSum("SC","RA","Week<=" & [combo6])

Hope that helps...

 

[pbaldy]

Here's a good reference on syntax:

General: DLookup Usage Samples

 

[Harry Taylor]

Hi. If you want "less than," then the expression you posted earlier probably should have used the <= sign instead. For example:

=DSum("SC","RA","Week<=" & [combo6])

Hope that helps...

Excellent !!!
thank you, now working perfectly

 

[theDBguy]

Excellent !!!
thank you, now working perfectly

Hi. Glad to hear you got it to work. Good luck with your project.

 

[Harry Taylor]

Hmmm,
I ran into a slight problem. Because the financial year is 1 April, it starts in week 14. So if i Dsum <= week 8 (i.e. end Feb) it only shows me figures from this year, not last years (week 14-52)

I created a table [WeekNos] for weeks 1 to 52 and a second column with week commencing dates - i.e WeekNo 14, WeekNoDate 1-Apr-19

My Drop down [Combo30] looks at this new table and shows the week and date.

The following code returns value #Name?
=DSum("SC","RA","Week<=" & [combo30])

Any ideas?
Thank you in advance

 

[theDBguy]

Hi. You could try joining the new table to your RA table using a query to add the WeekNo or WeekNoDate column (depending on how you have it set up). You can then try doing the DSum() on that query using the new column from your dropdown.

 

[plog]

I created a table [WeekNos] for weeks 1 to 52 ...

What about Week 53 and sometimes Week 54?

No seriously, every year spans at least 53 weeks. If your fiscal year starts April 1st; March 31st will not be in Week 52.

 

[Harry Taylor]

Hi Plog,
Good call, but for me Week 52 is the end of December, so if there is a week 53, the company would always be closed for Christmas/New Year.

For anyone else trying something siomilar, I managed to get the dsum working by using the following;
=DSum("[SC]","RA","[VisitDate]<=[Form]![Text27]+6")

([Form Column to count], [on Form], [where VisitDate] <= [current Form date in [Text27] box.
The date was w/c date, i.e. Monday so the +6 includes Mon to Sat.

Hope this helps someone else

thanks to the DBguy for putting me on the right path

 

[theDBguy]

Hi Harry. Congratulations! Glad to hear you got it sorted out. Good luck with your project.