I need help. I am having trouble clearing a field in an Access database.

[Tim]

I don't know what to use to clear a field in an MSAccess database. I
can update fields in the database and delete whole records but I have
not figured out how to clear a single field.

This is what I am using to update the database.

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=DataBase"
SQL = "UPDATE Table SET "
SQL = SQL & "thefield='" & Request.Form("thefield1") & "', "
SQL = SQL & "theDate='" & FormatDateTime(Now,2) & "' "
SQL = SQL & "WHERE theName = '" & Request.Form("theName") & "' "

Set RSUpdate=Conn.Execute(SQL)
Conn.Close
Set RSUpdate=nothing

I get an error if I try to update a field with a value of nothing "".

 

[Ray at]

Either set it to '' or to null, depending on what you'd like.

UPDATE TheTable SET TheColumn='' WHERE Something=AcertainValue
or
UPDATE TheTable SET TheColumn=NULL WHERE Something=AcertainValue

Ray at work

 

[Roy in]

set it to null instead
SQL = SQL & "thefield='" & Request.Form("thefield1") & "', "
SQL = SQL & "theDate=NULL "
SQL = SQL & " WHERE theName = '" & Request.Form("theName") & "' "

 

[Tom B]

As Ray and Roy said set it to null, but ensure that the field allows nulls.

 

[Tim]

Thanks for the info. I'll check things out and try the changes.