how do I clear an access database date field with visual basic?

[Guest]

I'm trying to use visual basic code to blank out a date in a database in
Access 2003. None of the help programs and books I've looked at have helped.
When I try to run an SQL command to update the field with '', I get an
invalid type message. Use of NULL does not work either. Any ideas?

 

[Rick Brandt]

I'm trying to use visual basic code to blank out a date in a database
in Access 2003. None of the help programs and books I've looked at
have helped. When I try to run an SQL command to update the field
with '', I get an invalid type message. Use of NULL does not work
either. Any ideas?

If the field allows Nulls (Required = No), then the following should work..

CurrentDB.EXECUTE "UPDATE TableName SET DateFieldName = Null", dbFailOnError

What was the SQL you tried?

 

[Douglas J. Steele]

If the field allows Nulls (Required = No), then the following should
work..

CurrentDB.EXECUTE "UPDATE TableName SET DateFieldName = Null",
dbFailOnError

And if the field doesn't allow Nulls, then you have no choice but to set the
date to some arbitrary date.

Setting it the date field to 0 will actually end up setting it to 30 Dec,
1899.

 

[Guest]

I get: Runtime error '3464': Data type mismatch in criteria expression
when I use the commands:
tmpDate = Date - (6 * 30.5)
CurrentDb.Execute "UPDATE Controller_Data SET OE = Null WHERE
Controller_Data![OE]< '#" & tmpDate & "#'", dbFailOnError
The Required variable for OE is set to NO.
I need to blank out the date the same way you can manually hit the delete
key to delete the date, only I want to do it by calculation. Putting in 1899
does not do it, because managers will then see the invalid dates and I'm
going to write reports based on dates older than a specifed date, so all
these records will show.

 

[Rick Brandt]

I get: Runtime error '3464': Data type mismatch in criteria expression
when I use the commands:
tmpDate = Date - (6 * 30.5)
CurrentDb.Execute "UPDATE Controller_Data SET OE = Null WHERE
Controller_Data![OE]< '#" & tmpDate & "#'", dbFailOnError
The Required variable for OE is set to NO.

Dates literals should be delimited with # and only #, You have a single quote
as well as the #. Get rid of those and try it.

 

[Guest]

Thank you Rick
I took out the single quotes and the execute command now works. I did the
same thing with the DoCmd.runSQL command and it failed saying that the field
was not updatable. I don't care anymore. Now I can move on with adding SET
statements. Thanks again. Larry

I get: Runtime error '3464': Data type mismatch in criteria expression
when I use the commands:
tmpDate = Date - (6 * 30.5)
CurrentDb.Execute "UPDATE Controller_Data SET OE = Null WHERE
Controller_Data![OE]< '#" & tmpDate & "#'", dbFailOnError
The Required variable for OE is set to NO.

Dates literals should be delimited with # and only #, You have a single quote
as well as the #. Get rid of those and try it.