Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Data type mismatch in expression

Discussion in 'Information Technology' started by Kate, Jul 28, 2006.

  1. Kate

    Kate
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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" <Kate@discussions.microsoft.com> wrote in message
    news:4CCC837C-4FD1-4AD6-8F6D-989A8CC12AFE@microsoft.com...
    > 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
     
  4. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    >>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
     
  5. Kate

    Kate
    Expand Collapse
    Guest

    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" <Kate@discussions.microsoft.com> wrote in message
    > news:4CCC837C-4FD1-4AD6-8F6D-989A8CC12AFE@microsoft.com...
    > > 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

    >
    >
    >
     
  6. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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" <Kate@discussions.microsoft.com> wrote in message
    news:DABC4731-B1CB-40D8-A93E-C42A70244CC3@microsoft.com...
    > 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" <Kate@discussions.microsoft.com> wrote in message
    >> news:4CCC837C-4FD1-4AD6-8F6D-989A8CC12AFE@microsoft.com...
    >> > 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.
     
  7. Kate

    Kate
    Expand Collapse
    Guest

    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" <Kate@discussions.microsoft.com> wrote in message
    > news:DABC4731-B1CB-40D8-A93E-C42A70244CC3@microsoft.com...
    > > 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" <Kate@discussions.microsoft.com> wrote in message
    > >> news:4CCC837C-4FD1-4AD6-8F6D-989A8CC12AFE@microsoft.com...
    > >> > 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.

    >
    >
    >
     

Share This Page