Hi

Have been through all other posts trying to find the answer so apologise if
it's in here somewhere.
I havea database table based on various forms. Some of the fields in the
forms are check boxes. However, when users run reports based on their
entries, the field with the check box shows -1. I want this to show '1'.
Have tried writing a query to base the report on where all -1 are converted
to 1. Using this IIF statement:
IIF([Q3_HEALTH]=-1,"1","0") But I get the above error message.
Please excuse a new user if I'm going about this the wrong way!!
Any help greatly appreciated.
Kate

Try:
=Abs([Q3_HEALTH])

Your original idea would probably work without the quotes:
=IIF([Q3_HEALTH]=-1,1,0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kate" <(E-Mail Removed)> wrote in message
news:4CCC837C-4FD1-4AD6-8F6D-(E-Mail Removed)...
> Hi
>
> Have been through all other posts trying to find the answer so apologise
> if
> it's in here somewhere.
> I havea database table based on various forms. Some of the fields in the
> forms are check boxes. However, when users run reports based on their
> entries, the field with the check box shows -1. I want this to show '1'.
> Have tried writing a query to base the report on where all -1 are
> converted
> to 1. Using this IIF statement:
> IIF([Q3_HEALTH]=-1,"1","0") But I get the above error message.
> Please excuse a new user if I'm going about this the wrong way!!
> Any help greatly appreciated.
> Kate

>>I have a database table based on various forms.
A little lesson in terms - a table is not based on forms. A table stores
data but a form is used to input or display data.

Try this --
ABS([Q3_HEALTH])


"Kate" wrote:

> Hi
>
> Have been through all other posts trying to find the answer so apologise if
> it's in here somewhere.
> I havea database table based on various forms. Some of the fields in the
> forms are check boxes. However, when users run reports based on their
> entries, the field with the check box shows -1. I want this to show '1'.
> Have tried writing a query to base the report on where all -1 are converted
> to 1. Using this IIF statement:
> IIF([Q3_HEALTH]=-1,"1","0") But I get the above error message.
> Please excuse a new user if I'm going about this the wrong way!!
> Any help greatly appreciated.
> Kate

Thanks for the speedy response. However, now when I run the query, no
customers show up!

Just incase I've messed something up. I've done a simple query where I've
got a 'Customer' field and 'Q3_HEALTH' field. I've put you Abs formula under
criteria.
Please let me know if I'm doing something else wrong!!

"Allen Browne" wrote:

> Try:
> =Abs([Q3_HEALTH])
>
> Your original idea would probably work without the quotes:
> =IIF([Q3_HEALTH]=-1,1,0)
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Kate" <(E-Mail Removed)> wrote in message
> news:4CCC837C-4FD1-4AD6-8F6D-(E-Mail Removed)...
> > Hi
> >
> > Have been through all other posts trying to find the answer so apologise
> > if
> > it's in here somewhere.
> > I havea database table based on various forms. Some of the fields in the
> > forms are check boxes. However, when users run reports based on their
> > entries, the field with the check box shows -1. I want this to show '1'.
> > Have tried writing a query to base the report on where all -1 are
> > converted
> > to 1. Using this IIF statement:
> > IIF([Q3_HEALTH]=-1,"1","0") But I get the above error message.
> > Please excuse a new user if I'm going about this the wrong way!!
> > Any help greatly appreciated.
> > Kate
>
>
>

Type the expression:
Abs([Q3_HEALTH])
into a fresh column in the Field row of the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kate" <(E-Mail Removed)> wrote in message
newsABC4731-B1CB-40D8-A93E-(E-Mail Removed)...
> Thanks for the speedy response. However, now when I run the query, no
> customers show up!
>
> Just incase I've messed something up. I've done a simple query where I've
> got a 'Customer' field and 'Q3_HEALTH' field. I've put you Abs formula
> under
> criteria.
> Please let me know if I'm doing something else wrong!!
>
> "Allen Browne" wrote:
>
>> Try:
>> =Abs([Q3_HEALTH])
>>
>> Your original idea would probably work without the quotes:
>> =IIF([Q3_HEALTH]=-1,1,0)
>>
>> "Kate" <(E-Mail Removed)> wrote in message
>> news:4CCC837C-4FD1-4AD6-8F6D-(E-Mail Removed)...
>> > Hi
>> >
>> > Have been through all other posts trying to find the answer so
>> > apologise
>> > if
>> > it's in here somewhere.
>> > I havea database table based on various forms. Some of the fields in
>> > the
>> > forms are check boxes. However, when users run reports based on their
>> > entries, the field with the check box shows -1. I want this to show
>> > '1'.
>> > Have tried writing a query to base the report on where all -1 are
>> > converted
>> > to 1. Using this IIF statement:
>> > IIF([Q3_HEALTH]=-1,"1","0") But I get the above error message.
>> > Please excuse a new user if I'm going about this the wrong way!!
>> > Any help greatly appreciated.

Thanks - that worked a treat!

"Allen Browne" wrote:

> Type the expression:
> Abs([Q3_HEALTH])
> into a fresh column in the Field row of the query.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Kate" <(E-Mail Removed)> wrote in message
> newsABC4731-B1CB-40D8-A93E-(E-Mail Removed)...
> > Thanks for the speedy response. However, now when I run the query, no
> > customers show up!
> >
> > Just incase I've messed something up. I've done a simple query where I've
> > got a 'Customer' field and 'Q3_HEALTH' field. I've put you Abs formula
> > under
> > criteria.
> > Please let me know if I'm doing something else wrong!!
> >
> > "Allen Browne" wrote:
> >
> >> Try:
> >> =Abs([Q3_HEALTH])
> >>
> >> Your original idea would probably work without the quotes:
> >> =IIF([Q3_HEALTH]=-1,1,0)
> >>
> >> "Kate" <(E-Mail Removed)> wrote in message
> >> news:4CCC837C-4FD1-4AD6-8F6D-(E-Mail Removed)...
> >> > Hi
> >> >
> >> > Have been through all other posts trying to find the answer so
> >> > apologise
> >> > if
> >> > it's in here somewhere.
> >> > I havea database table based on various forms. Some of the fields in
> >> > the
> >> > forms are check boxes. However, when users run reports based on their
> >> > entries, the field with the check box shows -1. I want this to show
> >> > '1'.
> >> > Have tried writing a query to base the report on where all -1 are
> >> > converted
> >> > to 1. Using this IIF statement:
> >> > IIF([Q3_HEALTH]=-1,"1","0") But I get the above error message.
> >> > Please excuse a new user if I'm going about this the wrong way!!
> >> > Any help greatly appreciated.
>
>
>