IIF with Sum in (Cross Join) Access 2010

[mba_110]

Dear All,

I am trying to carryout some logical calculation and prefer the easiest way.

I have form where i need to calculate two dates fields based on my calendar_table data let me tell you more and deep about it .

Fields for Form is
[StartDate] - Date Field text box
[EndDate] - Date Field text box
[Nett] this is the text box where i want the result.


Fields for Calendar_table is
[CalendarDate] - Date field where all dates from 1991 to 2040 is mentioned date by date.
[NWD] - Number or calculated field


Now i need startdate and endDate of form selection should calculate total of that particular period from Calendar_table for [NWD]

for example
form's [StartDate] is 01 Sep 2015 and [EndDate] is 05 Sep 2015 and
Calendar_table's [CalandarDate] rows mentioned values for above dates is
01 Sep 2015 is 1
02 Sep 2015 is 0
03 Sep 2015 is 2
04 Sep 2015 is 1
05 Sep 2015 is 0

than result on Form's [Nett] should be 4 if not selected any date in form's start or end date than should appear zero in nett field on form.

in order to achieve that i try with following expression on [Nett] field but not succeeded.

=Sum(IIf([StartDate]=[Calendar_table]![CalendarDate] AND [EndDate]=[Calendar_table]![CalendarDate],[Calendar_table]![NWD]))

Can anyone help me with above, if required any VBA coding in easiest way that can be great also.


MA

 

[xenou]

You'll want to use DSUM to sum [NWD] in the calendarDate table with a criteria something like:

"([CalendarDate]>=NZ([StartDate],#12/31/2099#) AND ([CalendarDate]<=NZ([EndDate],#1/1/1900#)"

The nz function would handle cases where fields are null (perhaps at startup of the form) so that the formula returns 0. Clearly this could all be put into after change event on the textfields too, and just use DSUM in the event code (or the results of a query of the same kind) to populate the results. The only tricky part there is that you might need to check that both fields have valid date values in them before you go after a result, and if one (or both) of the fields don't have dates then just put 0 in the results.

Be aware that with DSum, DLookup, and similar aggregate functions, the "criteria" argument in the function is a string literal - just put in the string value just as you would for a WHERE clause in a query, but without the WHERE keyword.

 

[Micron]

If for some reason you can't use xenou's suggestion, try making the unbound NETT's control source
SELECT Sum(Calendar_table.NWD) AS SumOfNWD FROM Calendar_table WHERE (((Calendar_table.CalandarDate) Between [StartDate] And [EndDate]));

In case you need this for reference, my own test sql was
SELECT Sum(Table2.Num) AS SumOfNum FROM Table2 WHERE (((Table2.CalendarDate) Between #9/1/2015# And #9/6/2015#));

NOTES:
- your control names look like field names (usually the result of building a form with a wizard) which can cause issues. You should check out how to name controls if you haven't already and avoid having controls and fields with the same names.
- I've copied your spelling (Calendar and Calander) so watch for this.
- I've never used DSum, so I do not know what to expect if there are any Nulls in field NWD. Maybe you'll be OK if you decide to use it.
- Your form code would set your sum control's rowsource (NWD?) to "" upon opening. The AfterUpdate event for each date control should ensure both date fields contain dates (not just values - what if I enter an invalid date or some text?) and that End > Start before you apply the sum control's rowsource (the sql I wrote) and requery it.
- you can also set the rowsource of the sum control to an expression using DSum, but until there are dates in both controls, you may get undesired effects.

sum [NWD] in the calendarDate table with a criteria something like:

If this is a suggestion to place an expression in a table field to store a calculated value, I believe this is usually frowned upon.

 

[mba_110]

I am sorry all above are not working I did try long time.

Micron in your code it is telling that the syntax of subquery in this expression is incorrect check the subquery's syntax and enclose subquery in parenthesis.

Please tell me easy way and always mention where to record or put the code/expression etc.

If Afterupdate event can solve the issue then please draft it.

thnx

 

[Micron]

I tested it and it worked (giving the sum of 4 as requested), BUT it is not a subquery as the error message indicates, and I did state where to put it

unbound NETT's control source

I think you put this into the criteria row of a query, which is not what I intended.

Open a new query (don't use the wizard), close the "Show table" dialog box, go to sql view and paste your sql into the sql view. Switch to data sheet view and see what happens. If it does not work properly, that has to be fixed first. If you cannot determine the fix, post the sql as well as any error message/number that it generates. If it does work, open the form in design view, go to the control that will hold the sum number and paste this sql into the control's rowsource line of the control's property sheet. You should always work on copies of your database or at least it's objects so you don't mess up what already works.

As for the code that will be needed, the names of all the affected objects have to be known (form name, names of controls that are involved) and the desired trigger action that would produce a result (in this case, a sum) such as a button push, or entering a pair of dates, for example. If you have a place that you can upload a db copy to, it might be the easiest way.

 

[mba_110]

Thank you very much micron.

Finally i did solve the issue with little changes actually in your sql you forgot to mention form address, below is complete SQL now.

SELECT Sum(Calendar_table.NWD) AS SumOfNWD
FROM Calendar_table
WHERE (((Calendar_table.CalendarDate) Between [Forms]![AnnualLeave_Form]![LeaveStart] And [Forms]![AnnualLeave_Form]![LeaveEnd]));

LeaveStart and LeaveEnd not in table its on form so i have to use Dlookup to recall the numbers for selected dates on form.

I will post next issue in my new thread...

MA