"Invalid use of Null" message from DSUM

  • Thread starter Thread starter Dorian
  • Start date Start date
D

Dorian

Can anyone see how this could come from executing this DSUM. I set breakpoint
and cmbYear and cmbMonth.column(0) both have values, also no column in the
table contains a Null.

intFPUOpnYTD = DSum("[FPUOpnMon]", "tblMonthlyReport", "[Year] = '" &
Me!cmbYear & "' AND [Month] >= '08' AND [Month] <= '" & Me!cmbMonth.Column(0)
& "'")

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Dorian said:
Can anyone see how this could come from executing this DSUM. I set
breakpoint and cmbYear and cmbMonth.column(0) both have values, also
no column in the table contains a Null.

intFPUOpnYTD = DSum("[FPUOpnMon]", "tblMonthlyReport", "[Year] = '" &
Me!cmbYear & "' AND [Month] >= '08' AND [Month] <= '" &
Me!cmbMonth.Column(0) & "'")

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish
and they eat for a lifetime".
Click to expand...

Probably the result of the DSum is Null - i e - no records found
matching the criteria, so no records to sum -> Null.

Try using the NZ function:

intFPUOpnYTD = NZ(DSum("[FPUOpnMon]", "tblMonthlyReport", "[Year] = '"
&
Me!cmbYear & "' AND [Month] >= '08' AND [Month] <= '" &
Me!cmbMonth.Column(0) & "'"))
 
Can anyone see how this could come from executing this DSUM. I set
breakpoint and cmbYear and cmbMonth.column(0) both have values,
also no column in the table contains a Null.

intFPUOpnYTD = DSum("[FPUOpnMon]", "tblMonthlyReport", "[Year] =
'" & Me!cmbYear & "' AND [Month] >= '08' AND [Month] <= '" &
Me!cmbMonth.Column(0) & "'")

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish
and they eat for a lifetime".
Click to expand...

What is the datatype of month? of year? Of cmbMonth.Column(0)?
Your code implies that they are string types because of the delimiters.

If they are in fact numbers, use
intFPUOpnYTD = DSum("[FPUOpnMon]", _
"tblMonthlyReport", _
"[Year] = " & Me!cmbYear _
& " AND [Month] >= 08 _
& " AND [Month] <= " & Me!cmbMonth.Column(0))
 
Back
Top