Number Datatype Syntax

  • Thread starter dp724 via AccessMonster.com
  • 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
 
D

Dirk Goldgar

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
 
J

John Spencer

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
..
 
S

Stuart McCall

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"
 
D

Douglas J. Steele

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
 
Top