Dates in VBA

  • Thread starter Thread starter Simon Guertin
  • Start date Start date
S

Simon Guertin

Hi, I am trying to program with dates. I want to have a
function return a date or nothing if there is no date that
suits me. In vba it does not let me do this


dim mydate as date
....
....
if(not good) then
mydate = null
end if

the function is used in a query, should I use a string and
set it to null instead?
 
A Date variable cannot hold the value Null (though a Date field in a
Table can. If you want a function to be able to return a Null, its
return value must be DIMmed as a Variant. I sometimes also use the
date/time value #00:00# (midnight on December 31, 1899, if my memory
serves) as a pseudo-Null in Functions which normally return Dates,
since it is unlikely to represent real data.
 
Try:

dim mydate as date
.....
.....
if not IsDate(your date value) then
mydate = null
end if

From Access Help File:
IsDate Function

Returns a Boolean value indicating whether an expression
can be converted to a date.

Syntax

IsDate(expression)

The required expression argument is a Variant containing a
date expression or string expression recognizable as a
date or time.

Remarks

IsDate returns True if the expression is a date or is
recognizable as a valid date; otherwise, it returns False.
In Microsoft Windows, the range of valid dates is January
1, 100 A.D. through December 31, 9999 A.D.; the ranges
vary among operating systems.
 
Back
Top