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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
Larry said:
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?
Click to expand...

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?
 
Rick Brandt said:
If the field allows Nulls (Required = No), then the following should
work..

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

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.
 
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.
 
Larry said:
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.
Click to expand...

Dates literals should be delimited with # and only #, You have a single quote
as well as the #. Get rid of those and try it.
 
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

Rick Brandt said:
Larry said:
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.
Click to expand...

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