Number Datatype Syntax

  • Thread starter Thread starter dp724 via AccessMonster.com
  • Start date Start date
D

dp724 via AccessMonster.com

The following works fine for a text datatype field:

CurrentDb.Execute "UPDATE MyTable SET [MyField] = '' WHERE [MyField] = '0'",
dbFailOnError

What would be the correct syntax if 'MyField' is a number datatype?

Thanks in advance.

Dave
 
dp724 via AccessMonster.com said:
The following works fine for a text datatype field:

CurrentDb.Execute "UPDATE MyTable SET [MyField] = '' WHERE [MyField] =
'0'",
dbFailOnError

What would be the correct syntax if 'MyField' is a number datatype?

Thanks in advance.

Dave
Click to expand...


You can't set a number field to '' (a zero-length string), as you're doing
above, so I'll assume you want to set the field to Null. For that, you
would use this:

CurrentDb.Execute _
"UPDATE MyTable SET [MyField] = Null WHERE [MyField] = 0", _
dbFailOnError
 
CurrentDb.Execute "UPDATE MyTable SET [MyField] = NULL WHERE [MyField] = 0",
dbFailOnError

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
dp724 via AccessMonster.com said:
The following works fine for a text datatype field:

CurrentDb.Execute "UPDATE MyTable SET [MyField] = '' WHERE [MyField] =
'0'",
dbFailOnError

What would be the correct syntax if 'MyField' is a number datatype?

Thanks in advance.

Dave
Click to expand...

"UPDATE MyTable SET [MyField] = Null WHERE [MyField] = 0"
 
What are you trying to set it to? Since it's numeric, it can only accept
numeric values (or Null, if the field's Required property is False).

CurrentDb.Execute "UPDATE MyTable SET [MyField] = -1 WHERE [MyField] = 0",
dbFailOnError

or

CurrentDb.Execute "UPDATE MyTable SET [MyField] = Null WHERE [MyField] = 0",
dbFailOnError
 
Back
Top