Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Posts
    38

    Exclamation Unanswered: changeing values on a report!

    I have areport that opens a query based on a table in that table I have numberic fields called Question_1 Question_2 ext... when I open this query in the report it displays the numeric values of these fields as either 1 0 or -1 being that 1 = yes 0 = N/A and -1 = No now I was wondering is there a way to have the query display the Yes No N/A on the report instead of the numbers???

  2. #2
    Join Date
    Jan 2004
    Location
    Slovakia
    Posts
    32
    I recommend you to use the function switch, for example in following way:

    Switch([Question_1]=1;"yes";[Question_1]=0;"N/A";[Question_1]=-1;"no")

    You should use this function in the query.

  3. #3
    Join Date
    Jan 2004
    Posts
    38
    Ok I am not quite sure where do I put that in the query?

  4. #4
    Join Date
    Jan 2004
    Location
    Slovakia
    Posts
    32
    OK, please post in your query...

  5. #5
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59
    You create an expression in one of your query fields. If you haven't done that before, go to a blank column in your QBE grid (the bottom of the query design window). Type a new column name (not one of your fields) and follow it with a colon. Something like this: "Response: ". Then add the code that he provided after the colon. The name you entered appears to the form as though it were one of the fields. That will be the control source of the field on your report.

    If his syntax is right (I'm not near a PC so I can't check), then it should evaluate the contents of [Question_1] and display the appropriate text.

    Magee
    Last edited by mageem; 01-31-04 at 21:52.

  6. #6
    Join Date
    Jan 2004
    Posts
    38
    Here is the Query

    SELECT Employees_Inbound_Query.Initials, Employees_Inbound_Query.Emp_First_Name, Employees_Inbound_Query.Emp_Last_Name, Employees_Inbound_Query.Inbound_ID, Employees_Inbound_Query.Account_Number1, Employees_Inbound_Query.Date1, Employees_Inbound_Query.Time1, Employees_Inbound_Query.Account_Number2, Employees_Inbound_Query.Date2, Employees_Inbound_Query.Time2, Employees_Inbound_Query.Account_Number3, Employees_Inbound_Query.Date3, Employees_Inbound_Query.Time3, Employees_Inbound_Query.Question_1, Employees_Inbound_Query.Question_2, Employees_Inbound_Query.Question_3, Employees_Inbound_Query.Question_4, Employees_Inbound_Query.Question_5, Employees_Inbound_Query.Question_6, Employees_Inbound_Query.Question_7, Employees_Inbound_Query.Question_8, Employees_Inbound_Query.Question_9, Employees_Inbound_Query.Question_10, Employees_Inbound_Query.Question_11, Employees_Inbound_Query.Total_Score, Employees_Inbound_Query.TTotal_Score, Employees_Inbound_Query.Comments, Employees_Inbound_Query.Date_Review, Employees_Inbound_Query.Time_Review, Employees_Soft_Skills_Inbound_Query.Question_1, Employees_Soft_Skills_Inbound_Query.Question_2, Employees_Soft_Skills_Inbound_Query.Question_3, Employees_Soft_Skills_Inbound_Query.Question_4, Employees_Soft_Skills_Inbound_Query.Question_5, Employees_Soft_Skills_Inbound_Query.Question_6, Employees_Soft_Skills_Inbound_Query.Question_7, Employees_Soft_Skills_Inbound_Query.Question_8, Employees_Soft_Skills_Inbound_Query.Total_Score, Employees_Soft_Skills_Inbound_Query.TTotal_Score, Employees_Soft_Skills_Inbound_Query.Comments, Employees_Soft_Skills_Inbound_Query.Date_Review, Employees_Soft_Skills_Inbound_Query.Time_Review
    FROM Employees_Inbound_Query LEFT JOIN Employees_Soft_Skills_Inbound_Query ON Employees_Inbound_Query.Inbound_ID = Employees_Soft_Skills_Inbound_Query.Inbound_ID
    WHERE ((([Employees_Inbound_Query.Inbound_ID])=[Forms]![Inbound]![Inbound_ID]));


    Just curious you said that you are not near a pc to check! I was just wondering how are you replying to the post if you not near a PC? just odd?

  7. #7
    Join Date
    Jan 2004
    Location
    Slovakia
    Posts
    32
    Originally posted by Crash1hd
    Here is the Query

    SELECT Employees_Inbound_Query.Initials, Employees_Inbound_Query.Emp_First_Name, Employees_Inbound_Query.Emp_Last_Name, Employees_Inbound_Query.Inbound_ID, Employees_Inbound_Query.Account_Number1, Employees_Inbound_Query.Date1, Employees_Inbound_Query.Time1, Employees_Inbound_Query.Account_Number2, Employees_Inbound_Query.Date2, Employees_Inbound_Query.Time2, Employees_Inbound_Query.Account_Number3, Employees_Inbound_Query.Date3, Employees_Inbound_Query.Time3, Employees_Inbound_Query.Question_1, Employees_Inbound_Query.Question_2, Employees_Inbound_Query.Question_3, Employees_Inbound_Query.Question_4, Employees_Inbound_Query.Question_5, Employees_Inbound_Query.Question_6, Employees_Inbound_Query.Question_7, Employees_Inbound_Query.Question_8, Employees_Inbound_Query.Question_9, Employees_Inbound_Query.Question_10, Employees_Inbound_Query.Question_11, Employees_Inbound_Query.Total_Score, Employees_Inbound_Query.TTotal_Score, Employees_Inbound_Query.Comments, Employees_Inbound_Query.Date_Review, Employees_Inbound_Query.Time_Review, Employees_Soft_Skills_Inbound_Query.Question_1, Employees_Soft_Skills_Inbound_Query.Question_2, Employees_Soft_Skills_Inbound_Query.Question_3, Employees_Soft_Skills_Inbound_Query.Question_4, Employees_Soft_Skills_Inbound_Query.Question_5, Employees_Soft_Skills_Inbound_Query.Question_6, Employees_Soft_Skills_Inbound_Query.Question_7, Employees_Soft_Skills_Inbound_Query.Question_8, Employees_Soft_Skills_Inbound_Query.Total_Score, Employees_Soft_Skills_Inbound_Query.TTotal_Score, Employees_Soft_Skills_Inbound_Query.Comments, Employees_Soft_Skills_Inbound_Query.Date_Review, Employees_Soft_Skills_Inbound_Query.Time_Review
    FROM Employees_Inbound_Query LEFT JOIN Employees_Soft_Skills_Inbound_Query ON Employees_Inbound_Query.Inbound_ID = Employees_Soft_Skills_Inbound_Query.Inbound_ID
    WHERE ((([Employees_Inbound_Query.Inbound_ID])=[Forms]![Inbound]![Inbound_ID]));


    Just curious you said that you are not near a pc to check! I was just wondering how are you replying to the post if you not near a PC? just odd?
    Try replace every *.Question_* with the above function.
    For example instead of "Employees_Inbound_Query.Question_1" try "
    Switch(Employees_Inbound_Query.Question_1=1;"yes"; Employees_Inbound_Query.Question_1=0;"N/A";Employees_Inbound_Query.Question_1=-1;"no")" or instead of "Employees_Soft_Skills_Inbound_Query.Question_ 8" try "Switch(Employees_Soft_Skills_Inbound_Query.Questi on_8=1;"yes";Employees_Soft_Skills_Inbound_Query.Q uestion_8=0;"N/A";Employees_Soft_Skills_Inbound_Query.Question _8=-1;"no")"

    You can, of course, use the switch function on your report too, not only in the query - maybe it will be more simple to implement....

  8. #8
    Join Date
    Jan 2004
    Posts
    38
    Id rather in the query but Im getting the following error

    Syntax error in query expression 'Switch(Employees_Inbound_Query.Question_1=1;"yes" ;Employees_Inbound_Query.Question_1=0;"N/A";Employees_Inbound_Query.Question_1=-1;"no")'.

    when I use the code like so

    SELECT Employees_Inbound_Query.Initials, Employees_Inbound_Query.Emp_First_Name, Employees_Inbound_Query.Emp_Last_Name, Employees_Inbound_Query.Inbound_ID, Employees_Inbound_Query.Account_Number1, Employees_Inbound_Query.Date1, Employees_Inbound_Query.Time1, Employees_Inbound_Query.Account_Number2, Employees_Inbound_Query.Date2, Employees_Inbound_Query.Time2, Employees_Inbound_Query.Account_Number3, Employees_Inbound_Query.Date3, Employees_Inbound_Query.Time3, Switch(Employees_Inbound_Query.Question_1=1;"yes"; Employees_Inbound_Query.Question_1=0;"N/A";Employees_Inbound_Query.Question_1=-1;"no"), Employees_Inbound_Query.Question_2, Employees_Inbound_Query.Question_3, Employees_Inbound_Query.Question_4, Employees_Inbound_Query.Question_5, Employees_Inbound_Query.Question_6, Employees_Inbound_Query.Question_7, Employees_Inbound_Query.Question_8, Employees_Inbound_Query.Question_9, Employees_Inbound_Query.Question_10, Employees_Inbound_Query.Question_11, Employees_Inbound_Query.Total_Score, Employees_Inbound_Query.TTotal_Score, Employees_Inbound_Query.Comments, Employees_Inbound_Query.Date_Review, Employees_Inbound_Query.Time_Review, Employees_Soft_Skills_Inbound_Query.Question_1, Employees_Soft_Skills_Inbound_Query.Question_2, Employees_Soft_Skills_Inbound_Query.Question_3, Employees_Soft_Skills_Inbound_Query.Question_4, Employees_Soft_Skills_Inbound_Query.Question_5, Employees_Soft_Skills_Inbound_Query.Question_6, Employees_Soft_Skills_Inbound_Query.Question_7, Employees_Soft_Skills_Inbound_Query.Question_8, Employees_Soft_Skills_Inbound_Query.Total_Score, Employees_Soft_Skills_Inbound_Query.TTotal_Score, Employees_Soft_Skills_Inbound_Query.Comments, Employees_Soft_Skills_Inbound_Query.Date_Review, Employees_Soft_Skills_Inbound_Query.Time_Review
    FROM Employees_Inbound_Query LEFT JOIN Employees_Soft_Skills_Inbound_Query ON Employees_Inbound_Query.Inbound_ID = Employees_Soft_Skills_Inbound_Query.Inbound_ID
    WHERE ((([Employees_Inbound_Query.Inbound_ID])=[Forms]![Inbound]![Inbound_ID]));

  9. #9
    Join Date
    Jan 2004
    Location
    Slovakia
    Posts
    32
    Excuse me, my mistake... Replace the ";'"-s to simple commas:

    Switch(Employees_Inbound_Query.Question_1=1,"yes", Employees_Inbound_Query.Question_1=0,"N/A",Employees_Inbound_Query.Question_1=-1,"no")
    (sz)Tomi

  10. #10
    Join Date
    Jan 2004
    Posts
    38
    Thankyou very much it seems to change it from Employees_Inbound_Query.Question_1 to Expr1013 how can I have it go back to being Employees_Inbound_Query.Question_1 in the Query as well?

  11. #11
    Join Date
    Jan 2004
    Location
    Slovakia
    Posts
    32
    Originally posted by Crash1hd
    Thankyou very much it seems to change it from Employees_Inbound_Query.Question_1 to Expr1013 how can I have it go back to being Employees_Inbound_Query.Question_1 in the Query as well?
    change

    Expr1013: Switch(Employees_Inbound_Query.Question_1=1,"yes", Employees_Inbound_Query.Question_1=0,"N/A",Employees_Inbound_Query.Question_1=-1,"no")

    to

    fieldname: Switch(Employees_Inbound_Query.Question_1=1,"yes", Employees_Inbound_Query.Question_1=0,"N/A",Employees_Inbound_Query.Question_1=-1,"no")

    I think, the field name in this case can't be same as s variable name used inside the function switch, thus it can'tbe Employees_Inbound_Query.Question_1. But I'm not sure...
    (sz)Tomi

  12. #12
    Join Date
    Jan 2004
    Posts
    38
    Ok Cool Thankyou again

  13. #13
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59

    Not on a PC

    I'm on my Mac. So I don't have Access locally.

    Also, sztomi's right. You can't give an existing fieldname so you have to create a new one for the derived column. Change "Expr1013: " to something else but not one of your field names.
    Last edited by mageem; 02-02-04 at 00:29.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •