Problems with Query on form

[WLC]

What is wrong with this query?

=IIf(IsNull([OffPeakDay],IIf(IsNull([OffPeakHour],Sum([Import]-[Export]),0)))

What I want is to sum the field (Import minus Export which are numeric) on the form if the OffPeakDay AND OffPeakHour fields are null.

 

[pr2-eugin]

I think it should be..

= Sum(IsNull([OffPeakDay]) And IsNull([OffPeakHour]), [Import]-[Export], 0)

Air coded.. Not tested

 

[WLC]

It didn't accept that. When I built it through the Expression Builder, it says the expression you entered has a function containing the wrong number of arguments. I entered exactly what you had

=Sum(IsNull([OffPeakDay]) And IsNull([OffPeakHour]), [Import]-[Export],0)

 

[David R]

I think Paul dropped the IIF.

= Sum(IIF((IsNull([OffPeakDay]) And IsNull([OffPeakHour])), ([Import]-[Export]), 0))

Also aircode, doublecheck your parentheses.

 

[WLC]

That didn't work either, only this time, I didn't get any kind of error message. When I entered that into the Control Source for the field on the form, it just goes away.

 

[David R]

I would enter it into the query behind the form, and then tie the field to display it to SumImportExport or whatever you call the calculated field. That also allows you to debug it a bit.

However, is this form a summary form or data entry? I.e. is the sum field in the header/footer? Otherwise it's going to give you some problems... we may need a bit more information, or to see a stripped down database uploaded.

 

[WLC]

The Sum field is in the footer. The table that feeds this has 24 rows each time based on some previous data entry/selection criteria. It has hours 1-24. The OffPeakDay may or may not be populated based on what day of the week it is. The OffPeakHour may or may not be populated based on what hours 1-24 equal.

I have 3 total boxes in my footer. I was trying to populate the On Peak box with the query I previously gave you.

So I thought I would try to go about it a different way and populate the Off Peak total (which the two together give the third box).

So here's my query for trying to populate the Off Peak total (I have the overall total so if I could get the Off Peak total I was going to subtract that from the overall total to arrive at the On Peak total.

=IIf([OffPeakDay]="OffPeakDay" Or [OffPeakHour]="OffPeakHour",Sum([Import]-[export]),0)

However, this does not work and returns the full total.

Not sure what I am doing wrong.

 

[David R]

Can you give some example data, along with the results you expect?

 

[WLC]

This is what the table that feeds my form looks like (attached).

As you can see, I expect the On Peak total to equal 1271 (based on null values in the OffPeakDay and OffPeakHour columns). I expect the Off Peak total to equal 318 (based on the non null values in the OffPeakDay and OffPeakHour columns). And then the Total is the two added together.

 

[JHB]

The below should work.
OfPeak:

=Sum(IIf([OffPeakHour]="OffPeakHour" Or [OffPeakDay]="OffPeakDay",([Import]-[Export])))

OnPeak:

=Sum(IIf(IsNull([OffPeakHour]) And IsNull([OffPeakDay]),([Import]-[Export])))

Total:

=Sum([Import]-[export])

 

[WLC]

Thank you, thank you, thank you!

This solved my problem!

 

[JHB]

You're welcome - good you got it solved.