nulls and date data type

[Guest]

I have a variable dDOB AS DATE to which I attach a date value from a text box
on a form. This date is then written to a table in SQL Server by passing it
in a parameter @DOB.

If no date in entereed by the user, I would like the field in the table to
remain <NULL>. However, when I try attach a null value to dDOB, I get a type
mismatch error.

I tried changing the variable to varDOB, which can accept NULL but then I
get a conversion error in the stored procedure if the date isn't null.

How can I get round this problem?

many thanks,

Pete

 

[Wayne Morgan]

Check for Null and do a conversion if it isn't Null.

Example:
IIf(Not IsNull(varDOB), CDate(varDOB), varDOB)

If you are doing this in a module, the above won't work because VBA will try
to evaluate both the true and false portions, even though it doesn't need
to. In that case try:

If Not IsNull(varDOB) Then
field = CDate(varDOB)
Else
field = varDOB
End If

Replace "field" with whatever you're trying to assign the value to.

 

[David C. Holley]

Think of the various data types as types of containers with VARIANT
being the largest and progressively getting smaller down to BYTE and
DATE somewhere inbetween. You can always put a smaller value into a
larger data type, but you can't put a larger smaller value into a
smaller data type. A NULL value (to my knowledge) can only go into a
DATA TYPE that is a VARIANT. Either declare dDOB as VARIANT or test the
value in the text box to determine if its NULL before trying to assign
it to the variable.