Options - update query

E

Emma

Hi I have an option list which gives the values option1 = 1, option 2 =
2,option3 = 3 However I would like the ouput in the report to be option = 1,
option 2= 2, option 3 = 1 as I'm doing a sum in the report. I tried using
the update query tools but it doesn't replace the value with 1. How can I do
this?
 
E

Emma

I will try to clarify

I have a option box with three choices it's called Marital Status:

It has option 1 Single = 1
option 2 Married = 2
option 3 Common Law =3

I would like to have a count of the number of people, However the count for
Common Law needs to be 2. When I change the value in the option box to 2 it
highlights both Married and Common Law. I don't want this. I just want the
value to be 2 in the report. I tried changing it with an update query however
it's not letting me create a report based on that query, and it changes
everything to the value of 2 instead of just the Common Law. Hope this makes
more sense.
 
J

John Spencer

Use an expression like the following to return 1 or 2 or Null if the field has
no value (null).

IIF([SomeField]=3,1,[SomeField])

If you are trying to sum that you could use

SUM(IIF([Somefield]=3,1,[SomeField]))

It is difficult to tell you where to put this since you don't say where you
are attempting to do this in a report.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
E

Emma

Hi John,

I fooled it by setting common law to -2 then putting Abs([Marital Status])
in the query, I know this sucks, but it does the trick for now.

John Spencer said:
Use an expression like the following to return 1 or 2 or Null if the field has
no value (null).

IIF([SomeField]=3,1,[SomeField])

If you are trying to sum that you could use

SUM(IIF([Somefield]=3,1,[SomeField]))

It is difficult to tell you where to put this since you don't say where you
are attempting to do this in a report.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
Hi I have an option list which gives the values option1 = 1, option 2 =
2,option3 = 3 However I would like the ouput in the report to be option = 1,
option 2= 2, option 3 = 1 as I'm doing a sum in the report. I tried using
the update query tools but it doesn't replace the value with 1. How can I do
this?
Click to expand...
Click to expand...
 
Ç

çÏÒÓËÉÊ

ÙÙÙÙÙÙÙÙÙÙÙÙÙÙÙÙ
Emma said:
I will try to clarify

I have a option box with three choices it's called Marital Status:

It has option 1 Single = 1
option 2 Married = 2
option 3 Common Law =3

I would like to have a count of the number of people, However the count for
Common Law needs to be 2. When I change the value in the option box to 2 it
highlights both Married and Common Law. I don't want this. I just want the
value to be 2 in the report. I tried changing it with an update query however
it's not letting me create a report based on that query, and it changes
everything to the value of 2 instead of just the Common Law. Hope this makes
more sense.
Click to expand...
 
Top