any way to replace values with null

#11

"sparks" wrote in message
news:347la755oi3adko7enl4uc93rsabtjs931 (AT) 4ax (DOT) com...

Quote:

maybe I am doing it wrong.
they want a query incase they find more of this stuff.

I did this in an update query
IIf([q1]>0,[q1],Null)

I will do it like you did in sql and try that

For a number column there is not "allow null" setting. However there is the
required setting.

If the required setting is no for a number column, then you can go:

update tblCustomers set MyNumberColumn = null where MyNumberColumn < 0

However, if the column is set to required, then you get a violation/error
message if you attempt to run the above query.
(and if in code or with error trapping, then you not see a message, but the
updates will be ignored).

So, you can update a number column to null, but only if the column in
question is not set to "required"

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com

#12

I went back and asked them to clear up their statements.
I guess I knew they were talking about a table no matter what they
called them.
They got a table with no primary key but all the variables in the
table were set to required yes.
So to use this they made up the -9 missing and -3 unknown and stuff
and put them in the table before they imported it.
After they imported it they went to the table and set all of the
required to no. But didn't know how to remove their numbers so just
left it.

When I went in and ran the update to set these values to null I got
the error.

copied all the data to a new table and the update worked fine.
I can only assume that even setting required to no did not remove the
constraints on the variables or something like that.

On Sat, 29 Oct 2011 09:29:57 -0500, "ron paii" <none (AT) nospam (DOT) com>
wrote:

Quote:

Is the field in the original table a primary key, or set to not allow Null?

"sparks" <sparks (AT) home (DOT) com> wrote in message
news:fhhla7l0jkde9n51doe0f9tke7lv81pbkk (AT) 4ax (DOT) com...
this is just too weird.
I can not update the values in the table.
BUT
I can copy the table into a new table and then run the same update
query and it works.

What, or Why would it do this?

On Fri, 28 Oct 2011 07:18:49 -0500, sparks <sparks (AT) home (DOT) com> wrote:

just tried this

UPDATE table1 SET q1 = null WHERE [q1]<=0;

you tried to assign the Null value to a variable tht is not a Variant
data type.

On Thu, 27 Oct 2011 09:56:09 -0400, "Bob Barrows"
reb01501 (AT) NOyahooSPAM (DOT) com> wrote:

sparks wrote:
We had a guy here make determinations on a table(s) and replaced
missing values with numbers.
something like if missing -9, unsure -3, etc

so now they have several tables with junk in them.
how can I replace all of these new values he put in with null in
number fields.

I know if a query it will say you cant replace with null because
variable is not a variant.

I just need to replace all values <0 with null in the tables.

what are you talking about?

update tablename set fieldname = null where fieldname<0

I just tested it to be sure and it works fine.

#13

sparks wrote:

Quote:

... with no primary key but all the _variables_ ...
... on the _variables_ or something like that. ...

:-) Please! Stop saying "variables"! Especially when you mean "fields" or
"columns". ;-)

Seriously, that will avoid a lot of confusion.

If the _fields_ were text fields, there is an additional setting called
"Allow empty strings" that might be the culprit. That setting should not
prevent setting a field to null, but perhaps the error message was
misquoted.

More likely, they left the Required setting at "Yes" for one of the fields.
Wait! This isn't a linked table is it?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

any way to replace values with null

comp.databases.ms-access comp.databases.ms-access

Re: any way to replace values with null - 10-29-2011 , 08:39 PM

Re: any way to replace values with null - 11-01-2011 , 06:12 AM

Re: any way to replace values with null - 11-01-2011 , 06:51 AM