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.

sparks wrote:
what are you talking about?

update tablename set fieldname = null where fieldname<0

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

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


On Thu, 27 Oct 2011 09:56:09 -0400, "Bob Barrows"
<reb01501 (AT) NOyahooSPAM (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:

If "q1" is really a field in your table, I cannot explain the error you are
getting. It's not a variable ... or is it? Is there a global variable or
other object in your database with that name?

Let's get explicit here:
UPDATE table1 SET table1.[q1] = null WHERE table1.[q1]<=0;

Does that work?


sparks wrote:

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 a guess: were reserved keywords used for any of the table or field
names? Here is a list:

http://support.microsoft.com/kb/321266


sparks wrote:

no they used ID and Q1 thru Q97
I asked the guy more about what and why this was done and did get one
bit of information.
He decided to identify thse fields when they could not do anything
with the data.
I asked what he was saying He said all variables were set to required
YES
so they put a value in all the data so it could be imported.
But they are not set to required...yea we changed that later.

I don't know maybe that is what is happening.
the variables were set to required at some point.

overall I gave them 2 querys ...one is a make table query to create
and copy the data and the other sets the negative values to null.

can that be done in one query...dont' know much about make table
queries




On Fri, 28 Oct 2011 12:09:39 -0400, "Bob Barrows"
<reb01501 (AT) NOSPAMyahoo (DOT) com> wrote:

"Variables"?
What are you talking about when you refer to "variables"? At first I thought
you were talking about the fields in the table, but then I noticed you said
" ... He decided to identify thse fields ... ", so that can't be right.

When I talk about "variables" I am referring to elements in programming
languages such as VBA. They cannot be "set to required". This is getting
very confusing, please help clear things up.

sparks wrote:

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

"sparks" <sparks (AT) home (DOT) com> wrote