By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,204 Members | 1,171 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,204 IT Pros & Developers. It's quick & easy.

Can't set date to Null

P: 3
I have a couple of Global variables called ExportStartDate and ExportEndDate, respectively.

When the user wants to export data to Excel I allow them to specify the date range, but they can also Cancel the action.

If they hit Cancel, I want to set these dates to Null and then test for the Null value. If NULL then I bypass the TransferSpreadsheet command.

The declarations are as follows:

Expand|Select|Wrap|Line Numbers
  1. Global ExportStartDate As Date
  2. Global ExportEndDate As Date
The code is:

Expand|Select|Wrap|Line Numbers
  1. ExportStartDate = Null
  2. ExportEndDate = Null
  3.  
But this is erroring. The error is:

Run-time error '94'
Invalid use of Null
What's wrong?

Thank you.
Jan 29 '08 #1
Share this Question
Share on Google+
6 Replies


nico5038
Expert 2.5K+
P: 3,072
Guess this is "by design". Access expects to find a real date value.
A solution could be to use 0 (zero) instead.
Personally I use often a function to get the value of a global (Public) variable. That way the variable can also be used directly in a query by referring to the function like:
Expand|Select|Wrap|Line Numbers
  1. function fncGetExportStartDate() as Date
  2.  
  3. fncExportStartDate = ExportStartDate
  4.  
  5. end function
  6.  
This code could ofcourse also be changed to handle your initialisation.

Nic;o)
Jan 29 '08 #2

jaxjagfan
Expert 100+
P: 254
I assume you have a command button the user is clicking to kick off the export.
Do you also have textboxes where the user is entering the 2 dates.

Expand|Select|Wrap|Line Numbers
  1. Sub cmdExport_Click
  2.  
  3. If isnull(Me.txtBegDate) then
  4. MsgBox "You must enter a beginning date!"
  5. Me.txtBegDate.SetFocus
  6. Exit Sub
  7. End If
  8. If isnull(Me.txtEndDate) then
  9. MsgBox "You must enter a end date!"
  10. Me.txtEndDate.SetFocus
  11. Exit Sub
  12. End If
  13.  
  14. 'Do your stuff if the user enters the dates
  15.  
  16. End Sub
  17.  
Jan 29 '08 #3

P: 3
Thanks for your reply!

In the end I changed my code to:

Expand|Select|Wrap|Line Numbers
  1. ExportStartDate = 0
  2. ExprtEndDate = 0
And, yes, the suggestion you made regarding using a function is a good one. I didn't show that part of my code in my original post but I am using a function to set the date values and I do call that function in a query:

Expand|Select|Wrap|Line Numbers
  1. Public Function GetGlobalExportParameter(GlobalParameterName As String)
  2.  
  3.      Select Case GlobalParameterName
  4.             Case "Start_Date"
  5.                     GetGlobalExportParameter = ExportStartDate
  6.             Case "End_Date"
  7.                     GetGlobalExportParameter = ExportEndDate
  8.     End Select
  9. End Function
So using 0 instead of NULL was an easy fix but I thought there'd be something more elegant.
Jan 29 '08 #4

ADezii
Expert 5K+
P: 8,638
I have a couple of Global variables called ExportStartDate and ExportEndDate, respectively.

When the user wants to export data to Excel I allow them to specify the date range, but they can also Cancel the action.

If they hit Cancel, I want to set these dates to Null and then test for the Null value. If NULL then I bypass the TransferSpreadsheet command.

The declarations are as follows:

Expand|Select|Wrap|Line Numbers
  1. Global ExportStartDate As Date
  2. Global ExportEndDate As Date
The code is:

Expand|Select|Wrap|Line Numbers
  1. ExportStartDate = Null
  2. ExportEndDate = Null
  3.  
But this is erroring. The error is:



What's wrong?

Thank you.
The answer is a lot simpler than you realize, LadyReader. The 'only' Data Type that can be explicitly assigned a Null Value is Variant. The Declarations should be as follows, and if valid Dates are contained within the Declared Variables, they will be coerced into Date Sub-Types of the Variant Data Type. Sorry for me rambling on, but the following will not produce an Error:
Expand|Select|Wrap|Line Numbers
  1. 'Global is only supported for backward compatibility, I
  2. 'would suggest always using the Public Keyword
  3. Public ExportStartDate As Variant
  4. Public ExportEndDate As Variant
  5.               OR
  6. 'The Default Data Type is Variant
  7. Public ExportStartDate
  8. Public ExportEndDate
  9.  
  10. 'Now, no Error will be produced:
  11. ExportStartDate = Null
  12. ExportEndDate = Null
Jan 29 '08 #5

P: 1
ADezii,

It seems LadyReader may already have her solution. But I was curious.... Declaring ExportStartDate and ExportEndDate as Variants may or may not be more elegant for LadyReader, but won't it cut down on her code's efficiency?
Feb 11 '08 #6

ADezii
Expert 5K+
P: 8,638
ADezii,

It seems LadyReader may already have her solution. But I was curious.... Declaring ExportStartDate and ExportEndDate as Variants may or may not be more elegant for LadyReader, but won't it cut down on her code's efficiency?
Yes it will. The Variant Data Type is convenient, but it is not always the best choice. It's tempting to Declare all your Variables as Variants so you don't have to worry about what is in them but if you think that something will always be an Integer, Declare it as such. If you attempt to assign an invalid value to the Integer Variable, an Error Message will point straight to the problem. Variants must be at least as big and complex as any of the types they contain, which equates with slower, so avoid them. Since they have the overhead of tracking which type of data they are holding at any given time, they are in fact slightly slower than explicitly dimensioned Variables for the same operation. They are larger then almost any other Data Type and so take longer to move around in memory.

In some instances, you have no choice about your Data Types. If you are assigning Variables that might, at some point, need to contain a Null Value, you must use the Variant Data Type since it is the only Data Type that can contain a Null. If a Function might need to return a Null Value, the return value for that Function must be Variant. If you are working with data from Tables in your code, you generally must use Variants because of the distinct possibility of Nulls. You may also find that attempting to use specific Data Types when working with Jet ends up slowing your code. Because Jet uses Variants when it communicates with Access, when you place Jet Data into specific Data Types, you are asking VBA to make a Data Type conversion, and that takes time.

Enough rambling! I advised the OP to use the Variant Data Type because she was wondering why she was receiving an Error when she assigned a Null value to the Dates. Perhaps, I should have posted this article first. (LOL).
Feb 12 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.