Q: Is there a "this expression" option in a query?

  • Thread starter Thread starter MarkD
  • Start date Start date
M

MarkD

Using Access 2000.

Hey,

I need to update 100+ fields in a table as follows:
iif(isnull([thisfieldname]),0,1)

The only way I know how to do this is to bring each field
in and on the update clause, change each and every field,
ie...
iif(isnull([field1]),0,1)
iif(isnull([field2]),0,1)
....
iif(isnull([fielen]),0,1)

Is there a general statement that is equivalent to "the
expression returned for this field"?

Something like the equivalent of me.activecontrol for
forms.

Please, tell me there is!

Thanks!
-Mark
 
This is a WAG but is it possible that a normalized table structure would
allow you to update just a single field? 100+ fields with numeric values
suggests you are "committing spreadsheet". I could be way off base since I
don't know much about your table.
 
Hey Duane,

What's a WAG?

And yes, I am committing spreadsheet, but that can't be
helped -- that's how we get the data. So, the finalized
table is normalized, but it's getting there that's proving
to be a pain.

I take it from you answer there is no
such "[thisexpression]" type field variable...

Thanks!
-Mark

-----Original Message-----
This is a WAG but is it possible that a normalized table structure would
allow you to update just a single field? 100+ fields with numeric values
suggests you are "committing spreadsheet". I could be way off base since I
don't know much about your table.

--
Duane Hookom
MS Access MVP
--

Using Access 2000.

Hey,

I need to update 100+ fields in a table as follows:
iif(isnull([thisfieldname]),0,1)

The only way I know how to do this is to bring each field
in and on the update clause, change each and every field,
ie...
iif(isnull([field1]),0,1)
iif(isnull([field2]),0,1)
...
iif(isnull([fielen]),0,1)

Is there a general statement that is equivalent to "the
expression returned for this field"?

Something like the equivalent of me.activecontrol for
forms.

Please, tell me there is!

Thanks!
-Mark
Click to expand...


.
Click to expand...
 
You could probably write code that would loop through your field collection
of the table and perform update queries.

--
Duane Hookom
MS Access MVP
--

MarkD said:
Hey Duane,

What's a WAG?

And yes, I am committing spreadsheet, but that can't be
helped -- that's how we get the data. So, the finalized
table is normalized, but it's getting there that's proving
to be a pain.

I take it from you answer there is no
such "[thisexpression]" type field variable...

Thanks!
-Mark

-----Original Message-----
This is a WAG but is it possible that a normalized table structure would
allow you to update just a single field? 100+ fields with numeric values
suggests you are "committing spreadsheet". I could be way off base since I
don't know much about your table.

--
Duane Hookom
MS Access MVP
--

Using Access 2000.

Hey,

I need to update 100+ fields in a table as follows:
iif(isnull([thisfieldname]),0,1)

The only way I know how to do this is to bring each field
in and on the update clause, change each and every field,
ie...
iif(isnull([field1]),0,1)
iif(isnull([field2]),0,1)
...
iif(isnull([fielen]),0,1)

Is there a general statement that is equivalent to "the
expression returned for this field"?

Something like the equivalent of me.activecontrol for
forms.

Please, tell me there is!

Thanks!
-Mark
Click to expand...


.
Click to expand...
Click to expand...
 
Yeah, I was thinking about that too. This process, once
I'm done, will be handled by an operations person who is
just used to running queries. So, I decided to bite the
bullet and run a big honking update query. Took close to
an hour, partly due to my attention deficit disorder...

Thanks again Duane,
-Mark
 
Back
Top