Help with Invalid Use of NULL and Dsum function

[StuJol]

i cant get my head round what is happening, please can anyone help?

i have the following 3 functions to work out a sum within a year

Function OurStartDate() As Date
OurStartDate = DLookup("[OurStartDate]", "tblOurCompanyInfo")
End Function

Function OurEndDate() As Date
OurEndDate = DLookup("[OurEndDate]", "tblOurCompanyInfo")
End Function

Function CurrentFiscalYear() As String
CurrentFiscalYear = DSum("[TotalPriceIncludingVAT]", "tblExpenses",
"[ExpensesDate] >= # " & OurStartDate() & " # and [ExpensesDate] <= # " &
OurEndDate() & " #")
End Function

When i type the function ?CurrentFiscalYear into the intermediate window it
returns a value which is correct. All is good

i have the following 3 functions to work out a sum within a week

Function CurrentWeekFromDate() As Date
CurrentWeekFromDate = DateAdd("d", (Weekday(Date) * -1) + 2, Date)
End Function

Function CurrentWeekToDate() As Date
CurrentWeekToDate = DateAdd("d", 6 - Weekday(Date), Date)
End Function

Function CurrentWeek() As String
CurrentWeek = DSum("[TotalPriceIncludingVAT]", "tblExpenses",
"[ExpensesDate] >= # " & CurrentWeekFromDate() & " # and [ExpensesDate] <= #
" & CurrentWeekToDate() & " #")
End Function

both set of functions are the same but the last function CurrentWeek returns
Runtime Error 94 Invalid use of NULL
CurrentWeekFromDate=12/02/2007
CurrentWeekToDate=16/02/2007

The Dsum function should return a value not NULL

 

[Stefan Hoffmann]

hi Stu,

declare your functions as Public or as Private. This increases readability.

Function OurStartDate() As Date
OurStartDate = DLookup("[OurStartDate]", "tblOurCompanyInfo")
End Function

The D-functions can return NULL, which cannot assigend to a normal data
type. There are two solutions:

a) handle the NULL value

Public Function OurStartDate() As Date

Dim Result As Variant

Result = DLookup("[OurStartDate]", "tblOurCompanyInfo")
OurStartDate = Nz(Result, CDate(0))

End Function


b) return the NULL value

Public Function OurStartDate() As Variant

OurStartDate = DLookup("[OurStartDate]", "tblOurCompanyInfo")

End Function

This also applies to all of your functions.

both set of functions are the same but the last function CurrentWeek returns
Runtime Error 94 Invalid use of NULL
The Dsum function should return a value not NULL

Check you conditions and the values in your tables.

mfG
--> stefan <--

 

[StuJol]

Many thanks for your reply.

Going back to basics, ive found the problem but i dont understand it. Can
you explain?

this function uses the date format 02 febuary 2007 and returns the wrong
results

?Format(DSum("[TotalPriceIncludingVAT]", "tblExpenses", "[ExpensesDate] >=
#01/02/07# and [ExpensesDate] <= #28/02/07#"), "Currency")
£1,244.99

where this function uses the date format febuary 02 2007 and returns the
correct format

?Format(DSum("[TotalPriceIncludingVAT]", "tblExpenses", "[ExpensesDate] >=
#02/01/07# and [ExpensesDate] <= #02/28/07#"), "Currency")
£1,044.99

i want to use the uk format 01 febuary 2006

hi Stu,

declare your functions as Public or as Private. This increases readability.

Function OurStartDate() As Date
OurStartDate = DLookup("[OurStartDate]", "tblOurCompanyInfo")
End Function

The D-functions can return NULL, which cannot assigend to a normal data
type. There are two solutions:

a) handle the NULL value

Public Function OurStartDate() As Date

Dim Result As Variant

Result = DLookup("[OurStartDate]", "tblOurCompanyInfo")
OurStartDate = Nz(Result, CDate(0))

End Function


b) return the NULL value

Public Function OurStartDate() As Variant

OurStartDate = DLookup("[OurStartDate]", "tblOurCompanyInfo")

End Function

This also applies to all of your functions.

both set of functions are the same but the last function CurrentWeek returns
Runtime Error 94 Invalid use of NULL
The Dsum function should return a value not NULL

Check you conditions and the values in your tables.

mfG
--> stefan <--

 

[Ken Snell \(MVP\)]

ACCESS Jet expect dates to be in United States format (mm/dd/yyyy), so you
must change the date format to the US format before you concatenate the date
value into your DSum expression. Sorry, but that is how it is....

Else, use DateSerial function to return a valid date and avoid the problem
of date format entirely.

--

Ken Snell
<MS ACCESS MVP>

Many thanks for your reply.

Going back to basics, ive found the problem but i dont understand it. Can
you explain?

this function uses the date format 02 febuary 2007 and returns the wrong
results

?Format(DSum("[TotalPriceIncludingVAT]", "tblExpenses", "[ExpensesDate] >=
#01/02/07# and [ExpensesDate] <= #28/02/07#"), "Currency")
£1,244.99

where this function uses the date format febuary 02 2007 and returns the
correct format

?Format(DSum("[TotalPriceIncludingVAT]", "tblExpenses", "[ExpensesDate] >=
#02/01/07# and [ExpensesDate] <= #02/28/07#"), "Currency")
£1,044.99

i want to use the uk format 01 febuary 2006

hi Stu,

declare your functions as Public or as Private. This increases
readability.

Function OurStartDate() As Date
OurStartDate = DLookup("[OurStartDate]", "tblOurCompanyInfo")
End Function

The D-functions can return NULL, which cannot assigend to a normal data
type. There are two solutions:

a) handle the NULL value

Public Function OurStartDate() As Date

Dim Result As Variant

Result = DLookup("[OurStartDate]", "tblOurCompanyInfo")
OurStartDate = Nz(Result, CDate(0))

End Function


b) return the NULL value

Public Function OurStartDate() As Variant

OurStartDate = DLookup("[OurStartDate]", "tblOurCompanyInfo")

End Function

This also applies to all of your functions.

both set of functions are the same but the last function CurrentWeek
returns
Runtime Error 94 Invalid use of NULL
The Dsum function should return a value not NULL

Check you conditions and the values in your tables.

mfG
--> stefan <--