DSum w/Between = Invalid use of Null

[Robin]

I hope someone can help me. By inserting a MsgBox at various points in the
code, I have determined the "Invalid use of Null" occurs at the DSum line. I
originally did not have the Nz() function and got the same message. The
relevant portion of my code is as follows:

Case "3 Day Interval"
Dim dToday As Date
Dim dEDate1 As Date
Dim dEDate2 As Date
Dim dEDate3 As Date
Dim vCA1SHrs As Single
Dim vCA1Hrs As Single
Dim vCA2SHrs As Single
Dim vCA2Hrs As Single
Dim vCA3SHrs As Single
Dim vCA3Hrs As Single

Me.txtDate1SubSs = DateAdd("d", 2, Date)
Me.txtDate2SubSs = DateAdd("d", 3, Me.txtDate1SubSs)
Me.txtDate3SubSs = Me.txtDate2SubSs
Me.lstCA1SubSs.Requery
Me.lstCA2SubSs.Requery
Me.lstCA3SubSs.Requery
dToday = Date
dEDate1 = Me.txtDate1SubSs
vCA1SHrs = DSum("Nz([StaffAvail]![Hours],0)", "StaffAvail", _
"[Date] Between " & dToday & " And " & dEDate1 & "")
Me.txtCA1SHrs = vCA1SHrs
Case Else
End Select

The extra variables are because when I get this working, I have to use it
for two more date ranges.

Thank you for any help,
Robin

 

[Robin]

Oh, just as a PS, the txtDate1SubSs, et al are text boxes formated as Short
Date and do have values (dates) in them before the code gets to the DSum.

Thanks,
Robin

 

[Ken Snell \(MVP\)]

You don't need the last two " characters in the step that contains the DSum
function. Try this:

vCA1SHrs = DSum("Nz([StaffAvail]![Hours],0)", "StaffAvail", _
"[Date] Between " & dToday & " And " & dEDate1)

Also, note that you should not name a field "Date". It and many other words
are reserved words in ACCESS and should not be used for control names, field
names, etc. Allen Browne (MVP) has a very comprehensive list of reserved
words at his website:

Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html


See these Knowledge Base articles for more information about reserved words
and characters that should not be used (these articles are applicable to
ACCESS 2007 as well):

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

 

[Robin]

Ken,

Thank you for the response. I replaced my function with yours and still get
"Invalid use of Null." While the code is running to that point, is it
possible that a variable is not being set properly. I used to dToday = Date
to set that variable to today's date and the value in txtDate1SubSs is
currently 1/26/2009.

And thanks for the info about Reserved words, I forgot! I have renamed that
field WkDate.

Any other ideas? I really appreciate this,
Robin

You don't need the last two " characters in the step that contains the DSum
function. Try this:

vCA1SHrs = DSum("Nz([StaffAvail]![Hours],0)", "StaffAvail", _
"[Date] Between " & dToday & " And " & dEDate1)

Also, note that you should not name a field "Date". It and many other words
are reserved words in ACCESS and should not be used for control names, field
names, etc. Allen Browne (MVP) has a very comprehensive list of reserved
words at his website:

Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html


See these Knowledge Base articles for more information about reserved words
and characters that should not be used (these articles are applicable to
ACCESS 2007 as well):

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

I hope someone can help me. By inserting a MsgBox at various points in the
code, I have determined the "Invalid use of Null" occurs at the DSum line.
I
originally did not have the Nz() function and got the same message. The
relevant portion of my code is as follows:

Case "3 Day Interval"
Dim dToday As Date
Dim dEDate1 As Date
Dim dEDate2 As Date
Dim dEDate3 As Date
Dim vCA1SHrs As Single
Dim vCA1Hrs As Single
Dim vCA2SHrs As Single
Dim vCA2Hrs As Single
Dim vCA3SHrs As Single
Dim vCA3Hrs As Single

Me.txtDate1SubSs = DateAdd("d", 2, Date)
Me.txtDate2SubSs = DateAdd("d", 3, Me.txtDate1SubSs)
Me.txtDate3SubSs = Me.txtDate2SubSs
Me.lstCA1SubSs.Requery
Me.lstCA2SubSs.Requery
Me.lstCA3SubSs.Requery
dToday = Date
dEDate1 = Me.txtDate1SubSs
vCA1SHrs = DSum("Nz([StaffAvail]![Hours],0)", "StaffAvail", _
"[Date] Between " & dToday & " And " & dEDate1 & "")
Me.txtCA1SHrs = vCA1SHrs
Case Else
End Select

The extra variables are because when I get this working, I have to use it
for two more date ranges.

Thank you for any help,
Robin

 

[Robin]

A little more info. I took out the dToday and dEdate1 and put in #1/24/2009#
and #1/27/2009# (tried with and without the #'s) and still get the "Invalid
use of Null." The table "StaffAvail" has three fields: WkDate, StaffID,
Hours. The WkDate populated with all dates from 1/1/2009 thru 12/31/2009 and
appropriate Hours the staff is available to work FOR EACH STAFF MEMBER.
Therefore, the range 1/24/2009 - 1/27/2009 occurs 5 times in the table.
Could this have anything to do with it. If so, I will need to add an
individual StaffID to the criteria with the date range. Just a thought. (I
would think the DSum would sum ALL records between the date range.)

You don't need the last two " characters in the step that contains the DSum
function. Try this:

vCA1SHrs = DSum("Nz([StaffAvail]![Hours],0)", "StaffAvail", _
"[Date] Between " & dToday & " And " & dEDate1)

Also, note that you should not name a field "Date". It and many other words
are reserved words in ACCESS and should not be used for control names, field
names, etc. Allen Browne (MVP) has a very comprehensive list of reserved
words at his website:

Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html


See these Knowledge Base articles for more information about reserved words
and characters that should not be used (these articles are applicable to
ACCESS 2007 as well):

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18

--

Ken Snell
<MS ACCESS MVP>

I hope someone can help me. By inserting a MsgBox at various points in the
code, I have determined the "Invalid use of Null" occurs at the DSum line.
I
originally did not have the Nz() function and got the same message. The
relevant portion of my code is as follows:

Case "3 Day Interval"
Dim dToday As Date
Dim dEDate1 As Date
Dim dEDate2 As Date
Dim dEDate3 As Date
Dim vCA1SHrs As Single
Dim vCA1Hrs As Single
Dim vCA2SHrs As Single
Dim vCA2Hrs As Single
Dim vCA3SHrs As Single
Dim vCA3Hrs As Single

Me.txtDate1SubSs = DateAdd("d", 2, Date)
Me.txtDate2SubSs = DateAdd("d", 3, Me.txtDate1SubSs)
Me.txtDate3SubSs = Me.txtDate2SubSs
Me.lstCA1SubSs.Requery
Me.lstCA2SubSs.Requery
Me.lstCA3SubSs.Requery
dToday = Date
dEDate1 = Me.txtDate1SubSs
vCA1SHrs = DSum("Nz([StaffAvail]![Hours],0)", "StaffAvail", _
"[Date] Between " & dToday & " And " & dEDate1 & "")
Me.txtCA1SHrs = vCA1SHrs
Case Else
End Select

The extra variables are because when I get this working, I have to use it
for two more date ranges.

Thank you for any help,
Robin

 

[Tom Wickerath]

Hi Robin,

First, you have a table or query named "StaffAvail", correct?
Dates need to be delimited with the # sign. Try this version:

vCA1SHrs = DSum("Nz([StaffAvail]![Hours],0)", "StaffAvail", _
"[WkDate] Between #" & dToday & "# And #" & dEDate1 & "#")

If you still have no joy, then try printing portions of your expression to
the Immediate Window. For example, this expression should return something:

Debug.Print Nz([StaffAvail]![Hours],0)

This one should print a string that includes two valid dates:
Debug.Print "[WkDate] Between #" & dToday & "# And #" & dEDate1 & "#")


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Try this:

vCA1SHrs = DSum("Nz([Hours],0)", "StaffAvail", _
"[WkDate] Between #" & dToday & "# And #" & dEDate1 & "#")

I just ran a quick test, by creating a table named StaffAvail, with fields
WkDate and Hours. I populated it with the following test data:

WkDate StaffID Hours
1/20/2009 4
1/21/2009 2
1/22/2009 1
1/23/2009 3
1/24/2009
1/25/2009 2
1/26/2009 2
1/27/2009 4


and created this subroutine in a new module:

Sub Robin()
Dim dToday As Date
Dim dEDate1 As Date

dToday = #1/20/2009#
dEDate1 = #1/25/2009#

Debug.Print DSum("Nz([Hours],0)", "StaffAvail", _
"[WkDate] Between #" & dToday & "# And #" & dEDate1 & "#")

End Sub


The correct value, 12, is printed to the Immediate Window, and the null
value for 1/24 is handled correctly.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Robin]

Tom,

You are correct that I have a table StaffAvail. (See my "Additional info"
post that I was probably posting while you were replying.) As it states, I
even hard keyed two dates (with the #'s) and stilll got "Invalid use of Null".

I've never used the Debug.Print before but tried inserting the two
Debug.Print suggestions in my coding. Nothing - but it's probably because I
don't know how to use them!!! (I did find the Immediate Window though!) How
do you use Debug.Print? (The VBA help was not...helpful)

Thank you very much for your input,
Robin

Hi Robin,

First, you have a table or query named "StaffAvail", correct?
Dates need to be delimited with the # sign. Try this version:

vCA1SHrs = DSum("Nz([StaffAvail]![Hours],0)", "StaffAvail", _
"[WkDate] Between #" & dToday & "# And #" & dEDate1 & "#")

If you still have no joy, then try printing portions of your expression to
the Immediate Window. For example, this expression should return something:

Debug.Print Nz([StaffAvail]![Hours],0)

This one should print a string that includes two valid dates:
Debug.Print "[WkDate] Between #" & dToday & "# And #" & dEDate1 & "#")


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Ken,

Thank you for the response. I replaced my function with yours and still get
"Invalid use of Null." While the code is running to that point, is it
possible that a variable is not being set properly. I used to dToday = Date
to set that variable to today's date and the value in txtDate1SubSs is
currently 1/26/2009.

And thanks for the info about Reserved words, I forgot! I have renamed that
field WkDate.

Any other ideas? I really appreciate this,
Robin

 

[Robin]

Tom,

WOW! Perfect!!!

It seems the only change from your first suggestion was omitting the
reference to the table in the "expression" part of DSum. Things like that
make me wonder if I'll ever get this. It would seem that being more specific
in one's references would be ok, but I've been frustrated before using form
names in Query criteria (that the Expression Builder automatically inserts)
when just the control name was needed.

THANK YOU AGAIN!!! I have a lot of these date range computations to do and
this "clears the way".

Robin

Try this:

vCA1SHrs = DSum("Nz([Hours],0)", "StaffAvail", _
"[WkDate] Between #" & dToday & "# And #" & dEDate1 & "#")

I just ran a quick test, by creating a table named StaffAvail, with fields
WkDate and Hours. I populated it with the following test data:

WkDate StaffID Hours
1/20/2009 4
1/21/2009 2
1/22/2009 1
1/23/2009 3
1/24/2009
1/25/2009 2
1/26/2009 2
1/27/2009 4


and created this subroutine in a new module:

Sub Robin()
Dim dToday As Date
Dim dEDate1 As Date

dToday = #1/20/2009#
dEDate1 = #1/25/2009#

Debug.Print DSum("Nz([Hours],0)", "StaffAvail", _
"[WkDate] Between #" & dToday & "# And #" & dEDate1 & "#")

End Sub


The correct value, 12, is printed to the Immediate Window, and the null
value for 1/24 is handled correctly.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

A little more info. I took out the dToday and dEdate1 and put in #1/24/2009#
and #1/27/2009# (tried with and without the #'s) and still get the "Invalid
use of Null." The table "StaffAvail" has three fields: WkDate, StaffID,
Hours. The WkDate populated with all dates from 1/1/2009 thru 12/31/2009 and
appropriate Hours the staff is available to work FOR EACH STAFF MEMBER.
Therefore, the range 1/24/2009 - 1/27/2009 occurs 5 times in the table.
Could this have anything to do with it. If so, I will need to add an
individual StaffID to the criteria with the date range. Just a thought. (I
would think the DSum would sum ALL records between the date range.)

 

[Tom Wickerath]

Hi Robin,

I was just starting to type out a reply about 3 1/2 hours ago, but then my
wife was calling me that it was time to leave, to go see a movie we had
planned on seeing. Glad to hear that you got it working.

Were you able to see how using Debug.Print statements works?

I just tried this version, without the Nz function, and it works as well:

Debug.Print DSum("[Hours]", "StaffAvail", _
"[WkDate] Between #" & dToday & "# And #" & dEDate1 & "#")

I used the same subroutine as before, except for this change, so the one
date for 1/24 which had nothing entered for Hours did not cause it to fail.

In a previous reply you wrote:
"(I would think the DSum would sum ALL records between the date range.)"

Correct. If you need the sum for a particular staff member, then you will
need to include a compound criteria that includes the date range and the
StaffID. Here is a useful reference for the DLookup function:

http://www.mvps.org/access/general/gen0018.htm

All of the other domain aggregrate functions, such as DCount, DSum, DMin,
DMax, etc. work in a similar manner.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

It seems the only change from your first suggestion was omitting the

reference to the table in the "expression" part of DSum.

PS. In addition, this version works too:

Debug.Print DSum("[StaffAvail]![Hours]", "StaffAvail", _
"[WkDate] Between #" & dToday & "# And #" & dEDate1 & "#")


So, I think the major issue was not having the # delimiters as a part of the
expression. When I remove those, I get the same null result that you were
getting.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html

 

[Robin]

Tom,

Everything is woking like a charm! I believe I may have had the #
delimiteres outside of the quotes when I tried that solution earlier. What
you gave me corrects that. I did need to limit by StaffID so I ended up with
this which works:
vCA1SHrs = DSum("Nz([Hours],0)", "StaffAvail", _
"[StaffID] = '" & stStaffID & "' AND [WkDate] Between #" & dToday & "# And
#" & dEDate1 & "#")

I seem to have a mental thing about the single/double quotes. At first I
couldn't figure out why the above worked with the second single quote
FOLLOWING the double quote until I "picked it apart" and saw what was
happening. I believe the quote usage throughout Access is one of the more
frustrating things for a novice. (Who would think you would end an
expression in Expression Builder with """")!

Thank you for all your help. I really learn a lot from this discussion
group and appreciate the help provided by the experts for the confused among
us.

Robin

It seems the only change from your first suggestion was omitting the
reference to the table in the "expression" part of DSum.

PS. In addition, this version works too:

Debug.Print DSum("[StaffAvail]![Hours]", "StaffAvail", _
"[WkDate] Between #" & dToday & "# And #" & dEDate1 & "#")


So, I think the major issue was not having the # delimiters as a part of the
expression. When I remove those, I get the same null result that you were
getting.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html

 

[Tom Wickerath]

You're welcome.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________