Can't set date to Null

[LadyReader]

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 TransferSpreads heet command.

The declarations are as follows:

Global ExportStartDate As Date
Global ExportEndDate As Date

The code is:

ExportStartDate = Null
ExportEndDate = Null

But this is erroring. The error is:

Run-time error '94'
Invalid use of Null

What's wrong?

Thank you.

[nico5038]

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:
[code=vb]
function fncGetExportSta rtDate() as Date

fncExportStartD ate = ExportStartDate

end function
[/code]

This code could ofcourse also be changed to handle your initialisation.

Nic;o)

[jaxjagfan]

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.

Sub cmdExport_Click

If isnull(Me.txtBegDate) then
MsgBox "You must enter a beginning date!"
Me.txtBegDate.SetFocus
Exit Sub
End If
If isnull(Me.txtEndDate) then
MsgBox "You must enter a end date!"
Me.txtEndDate.SetFocus
Exit Sub
End If

'Do your stuff if the user enters the dates

End Sub

[LadyReader]

Thanks for your reply!

In the end I changed my code to:

ExportStartDate = 0
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:

Public Function GetGlobalExportParameter(GlobalParameterName As String)

     Select Case GlobalParameterName
            Case "Start_Date"
                    GetGlobalExportParameter = ExportStartDate
            Case "End_Date"
                    GetGlobalExportParameter = ExportEndDate
    End Select
End Function

So using 0 instead of NULL was an easy fix but I thought there'd be something more elegant.

[ADezii]

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 TransferSpreads heet command.

The declarations are as follows:

Global ExportStartDate As Date
Global ExportEndDate As Date

The code is:

ExportStartDate = Null
ExportEndDate = Null

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:
[code=vb]
'Global is only supported for backward compatibility, I
'would suggest always using the Public Keyword
Public ExportStartDate As Variant
Public ExportEndDate As Variant
OR
'The Default Data Type is Variant
Public ExportStartDate
Public ExportEndDate

'Now, no Error will be produced:
ExportStartDate = Null
ExportEndDate = Null[/CODE]

[iambenjamin234]

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?

[ADezii]

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).