Insert >> null into Date/Time (Access2000)

[jason]

I need to deposit and empty dummy value into a date/time field within Access
using INSERT. However, I pick up a data type error if I attempt to insert
a NULL value for this particular date time field....

What are my options here.....I need the date to come up empty.....what can I
do?

Many thanks
Jason

 

[Aaron Bertrand - MVP]

I need to deposit and empty dummy value into a date/time field within
Access

using INSERT. However, I pick up a data type error if I attempt to insert
a NULL value for this particular date time field....

Can you show the syntax and the actual error message? Narrative is very
difficult to debug.

Did you try

UPDATE table SET dateCOLUMN = NULL WHERE [...]

Notice that null is not in quotes or # delimiters, it's just a lone
keyword...

 

[jason]

Sure. I am actually attempting an insert - not an update. I realise the use
of my delimiters is probably incorrect, however besides date time I usually
have no problem using these delimiters. My other update statements are
working fine but this particular insert is causing problems....my sql
statement falls apart if I remove the single delimiters (txt only)....

ActualClosingDate=Request.Form.Item("ActualClosingDate")

If len(trim(ActualClosingDate)) > 0 then

ActualClosingDate=ActualClosingDate

Else

Response.Write "******"

ActualClosingDate = Null

'ActualClosingDate="00/00/00"

End If

SQL=SQL & "'" & ActualClosingDate & "'," '#32 - date/time


SQL=SQL & "'" & SellingCommission & "'," '#33 - number


SQL=SQL & "'" & ListingCommission & "')" '#34 - number


Response.Write "<br>" & SQL & "<br>" & Active

'response.end

cnn.Execute(SQL)

I need to deposit and empty dummy value into a date/time field within Access
using INSERT. However, I pick up a data type error if I attempt to insert
a NULL value for this particular date time field....

Can you show the syntax and the actual error message? Narrative is very
difficult to debug.

Did you try

UPDATE table SET dateCOLUMN = NULL WHERE [...]

Notice that null is not in quotes or # delimiters, it's just a lone
keyword...

 

[Aaron Bertrand - MVP]

ActualClosingDate=Request.Form.Item("ActualClosingDate")

If len(trim(ActualClosingDate)) > 0 then

ActualClosingDate=ActualClosingDate

Try: ActualClosingDate = "#" & ActualClosingDate & "#"

Else

Response.Write "******"

ActualClosingDate = Null

Try:
ActualClosingDate = "NULL"

'ActualClosingDate="00/00/00"

Please tell me you are not using mm/dd/yy or dd/mm/yy!!! There is no date
format more ambiguous and prone to errors and confusion! Try using
YYYY-MM-DD.

End If

SQL=SQL & "'" & ActualClosingDate & "'," '#32 - date/time

Try:
SQL=SQL & ActualClosingDate & ","