insert null date/time

[asdf]

Hello.

Is there any way to insert null into a date/time field? I have a web page
that has a date textbox and I want to insert an empty string or null if the
user did not enter a date. I am using HTML, ASP, access db.

 

[Arvin Meyer [MVP]]

Most date/time fields use a 0 (12/30/1899) for a default value. Go into
table desgn view and clear the default value if you want a null.

 

[Allen Browne]

No problem inserting Null into a Date/Time field. Just omit the field from
the SQL statement, or use Null as the value.

Example:
INSERT INTO tblInvoice ( InvoiceID, InvoiceDate )
SELECT 99 AS InvoiceID, Null AS Expr1;

Zero-length string won't work: suitable only for Text fields, and then not a
good idea in most cases.

 

[asdf]

Hello and Thank you.

Shouldn't something like this work?
if Date_event <> "" Then
sqlstr = sqlstr & "'" & Date_event & "',"
else
sqlstr = sqlstr & "Null,"
End if

It works when I put a date in the textbox, but I get this error when I don't
enter a date:
"Data type mismatch in criteria expression"

 

[Allen Browne]

No. You cannot compare anything to null. See error #5 in:
Common Errors with Null
at:
http://allenbrowne.com/casu-12.html

Additionally, a Null is not the same as a zero-length string.

And finally, comparing the date/time value to a zero-length string it
probably the cause of the data type mismatch.

 

[asdf]

Sorry. I'm using asp to get the text from the textboxes and am trying
something like this

if Datetxtbx = "" Then Datetxtbx = "Null"
and when this variable is put into the sql string and the user didn't enter
anything in the textbox, I want Null to go into the date/time datatype field,
but I get an error. It works when I change the data type to text...but I
wasn't sure if I wanted to do that...

These work...they go into the access db
if txtbxA = "" Then txtbxA = 0 '****these go into number type field
if txtbxB = "" Then txtbxB= "Null"

Eileen

 

[Aaron Kempf]

it's probably as simple as 'needing to use vbNull instead of null'
yeah.. the VBA DATE dataType doesn't support nulls

I've had to extract 100 of these in the past couple of weeks.. I just find
it ridiculous

you need to give us MORE about how you're inserting this code..

it's probably as simple as 'needing to use vbNull instead of null'

-Aaron

 

[Aaron Kempf]

wow
this works

if Datetxtbx = "" Then Datetxtbx = vbNull

 

[Arvin Meyer [MVP]]

As Allen mentioned, you can't compare anything to Null. Nulls are only
accepted in code by the variant datatype (as opposed to a double which is
what a date/time actually is). Try nesting your compare statement inside of
one that tests for a null like:

If Len([Date_event] & vbNullString)> 1 Then
'Your code
End If

or possibly:

If IsDate(Me.[Date_event]) Then
' Your Code
End If

I like the first method better because it tests for both a Null and an empty
string.

 

[asdf]

I got it to insert using vbNull, but the default 12/31/1899 comes up. You
gave me a tip earlier about clearing the default value. Currently the
default value is cleared but the default value still gets entered. Is that
something I just gotta live with?

As Allen mentioned, you can't compare anything to Null. Nulls are only
accepted in code by the variant datatype (as opposed to a double which is
what a date/time actually is). Try nesting your compare statement inside of
one that tests for a null like:

If Len([Date_event] & vbNullString)> 1 Then
'Your code
End If

or possibly:

If IsDate(Me.[Date_event]) Then
' Your Code
End If

I like the first method better because it tests for both a Null and an empty
string.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello and Thank you.

Shouldn't something like this work?
if Date_event <> "" Then
sqlstr = sqlstr & "'" & Date_event & "',"
else
sqlstr = sqlstr & "Null,"
End if

It works when I put a date in the textbox, but I get this error when I
don't
enter a date:
"Data type mismatch in criteria expression"

 

[asdf]

Thank you. That was helpful.

it's probably as simple as 'needing to use vbNull instead of null'
yeah.. the VBA DATE dataType doesn't support nulls

I've had to extract 100 of these in the past couple of weeks.. I just find
it ridiculous

you need to give us MORE about how you're inserting this code..

it's probably as simple as 'needing to use vbNull instead of null'

-Aaron

 

[asdf]

woohoo! ok....thank you! If/then works the best.

As Allen mentioned, you can't compare anything to Null. Nulls are only
accepted in code by the variant datatype (as opposed to a double which is
what a date/time actually is). Try nesting your compare statement inside of
one that tests for a null like:

If Len([Date_event] & vbNullString)> 1 Then
'Your code
End If

or possibly:

If IsDate(Me.[Date_event]) Then
' Your Code
End If

I like the first method better because it tests for both a Null and an empty
string.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hello and Thank you.

Shouldn't something like this work?
if Date_event <> "" Then
sqlstr = sqlstr & "'" & Date_event & "',"
else
sqlstr = sqlstr & "Null,"
End if

It works when I put a date in the textbox, but I get this error when I
don't
enter a date:
"Data type mismatch in criteria expression"