Results 1 to 5 of 5
  1. #1
    dano-wpg is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    3

    Display text of categorical variable (combo box) in report

    Hello,



    I've seen variations of this, and I'm not sure exactly what I am doing wrong.
    (Such as this topic).

    I have a categorical variable in a form that pulls values from a table via its own query.

    This query results in a table like this:

    DDText - DDValue - DDVariable - Question
    None - 0 - VariableName - SurveyQuestion
    Low - 1 - VariableName - SurveyQuestion
    Moderate - 2 - VariableName - SurveyQuestion
    High - 3 - VariableName - SurveyQuestion

    If I look at a record via the form, it will show for example "Moderate", and the field in the database will have a value of 2.

    I have a different combo box and different query that displays a list of client ID's, and once I select the client I want to see, it passes that info along to the report which pulls up their information. In this report the variable will show "2", when I want it to show "Moderate".

    How do I do this?

    I have tried creating a copy of my query that gets client information that my report is based on, and adding an inner join via the GUI, and I get no errors, but then I don't get any data. Remove the join and I get data (but obviously not the proper text).

    Is it better to try and put all the info into the original query, or somehow do it after the fact?

    Thanks,

    (More info):

    My query the report is based on looks like this:
    Code:
    SELECT Last(Contact.ID) AS LastOfID, Contact.ClientCode, Last(Contact.ContactName) AS LastOfContactName, Last(Contact.ContactAddress) AS LastOfContactAddress, Last(Contact.ContactCity) AS LastOfContactCity, Last(Contact.ContactPostal) AS LastOfContactPostal, Last(Contact.ContactPhoneH) AS LastOfContactPhoneH, Last(Contact.ContactPhoneHLM) AS LastOfContactPhoneHLM, Last(Contact.ContactPhoneHAN) AS LastOfContactPhoneHAN, Last(Contact.ContactPhoneW) AS LastOfContactPhoneW, Last(Contact.ContactPhoneWLM) AS LastOfContactPhoneWLM, Last(Contact.ContactPhoneWAN) AS LastOfContactPhoneWAN, Last(Contact.ContactPhoneC) AS LastOfContactPhoneC, Last(Contact.ContactPhoneCLM) AS LastOfContactPhoneCLM, Last(Contact.ContactPhoneCAN) AS LastOfContactPhoneCAN, Last(Contact.ContactEmail) AS LastOfContactEmail, Last(Contact.ContactEmailCC) AS LastOfContactEmailCC, Last(Contact.ResidencePerm) AS LastOfResidencePerm, Last(Contact.ResidencePermNo) AS LastOfResidencePermNo, Last(Contact.Homeless) AS LastOfHomeless, Last(Contact.Pregnant) AS LastOfPregnant, Last(Contact.PregnantMonths) AS LastOfPregnantMonths, Last(Contact.EmplStatus) AS LastOfEmplStatus, Last(Contact.EmplFulltime) AS LastOfEmplFulltime, Last(Contact.EmplParttime) AS LastOfEmplParttime, Last(Contact.EmplUnemployed) AS LastOfEmplUnemployed, Last(Contact.EmplRetired) AS LastOfEmplRetired, Last(Contact.EmplStudent) AS LastOfEmplStudent, Last(Contact.EmplVolunteer) AS LastOfEmplVolunteer, Last(Contact.EmplHome) AS LastOfEmplHome, Last(Contact.EmplEIA) AS LastOfEmplEIA, Last(Contact.EmplEIARec) AS LastOfEmplEIARec, Last(Contact.EmplEIAApp) AS LastOfEmplEIAApp, Last(Contact.EmplEI) AS LastOfEmplEI, Last(Contact.EmplEIRec) AS LastOfEmplEIRec, Last(Contact.EmplEIApp) AS LastOfEmplEIApp, Last(Contact.EmplIncSrc) AS LastOfEmplIncSrc, Last(Contact.EmplIncSrcOthDesc) AS LastOfEmplIncSrcOthDesc, Last(Contact.OthSysInvCFS) AS LastOfOthSysInvCFS, Last(Contact.OthSysInvCLS) AS LastOfOthSysInvCLS, Last(Contact.Income) AS LastOfIncome, Last(Contact.EduHighest) AS LastOfEduHighest, Last(Contact.EduFormal) AS LastOfEduFormal, Last(Contact.EduHighestPost) AS LastOfEduHighestPost, Last(Contact.EduHighestPostOther) AS LastOfEduHighestPostOther, Last(Contact.FamilyRlshp) AS LastOfFamilyRlshp, Last(Contact.FamilyRlshpOther) AS LastOfFamilyRlshpOther, Last(Contact.FamilyChildren) AS LastOfFamilyChildren, Last(Contact.FamilyChildResp) AS LastOfFamilyChildResp, Last(Contact.FamilyChildResp06) AS LastOfFamilyChildResp06, Last(Contact.FamilyChildResp712) AS LastOfFamilyChildResp712, Last(Contact.FamilyChildResp1317) AS LastOfFamilyChildResp1317, Last(Contact.FamilySituation) AS LastOfFamilySituation, Last(Contact.FamilySituationOther) AS LastOfFamilySituationOther, Last(Contact.FamilyNumInd) AS LastOfFamilyNumInd, Last(Contact.FamilyNumIndChgKid) AS LastOfFamilyNumIndChgKid, Last(Contact.FamilyNumKids) AS LastOfFamilyNumKids, Last(Contact.FamilyNumChgKidWhy) AS LastOfFamilyNumChgKidWhy, Last(Contact.Suicidal) AS LastOfSuicidal, Last(Contact.SuicidalWhy) AS LastOfSuicidalWhy, Last(Contact.RiskFactorsSubstance) AS LastOfRiskFactorsSubstance, Last(Contact.RiskFactorsGambling) AS LastOfRiskFactorsGambling, Last(Contact.RiskFactorsEating) AS LastOfRiskFactorsEating, Last(Contact.RiskFactorsSpending) AS LastOfRiskFactorsSpending, Last(Contact.RiskFactorsHarming) AS LastOfRiskFactorsHarming, Last(Contact.RiskFactorsMental) AS LastOfRiskFactorsMental, Last(Contact.SupportsEducational) AS LastOfSupportsEducational, Last(Contact.SupportsEmplFT) AS LastOfSupportsEmplFT, Last(Contact.SupportsEmplPT) AS LastOfSupportsEmplPT, Last(Contact.SupportsVolunteering) AS LastOfSupportsVolunteering, Last(Contact.SupportsHousingSecure) AS LastOfSupportsHousingSecure, Last(Contact.SupportsHousingTrans) AS LastOfSupportsHousingTrans, Last(Contact.SupportsAfterCare) AS LastOfSupportsAfterCare, Last(Contact.SupportsReferHouseTrans) AS LastOfSupportsReferHouseTrans, Last(Contact.SupportsReferOthProg) AS LastOfSupportsReferOthProg, Last(Contact.SupportsRefusePlan) AS LastOfSupportsRefusePlan
    FROM Contact
    GROUP BY Contact.ClientCode
    ORDER BY Last(Contact.ID) DESC;
    And if I join the query it changes the sql to this:
    Code:
    FROM DDContactRiskFactors INNER JOIN Contact ON DDContactRiskFactors.DDValue = Contact.RiskFactorsSubstance
    GROUP BY Contact.ClientCode
    ORDER BY Last(Contact.ID) DESC;
    And just a bit more info: I have several fields that use the same categorical variable (all those that start with RiskFactors). It turns out if I joined the tables, only those that actually had a value will get displayed (hence why none were being displayed); if I add "DDText" to those variables shown, it shows the text, but as there are multiple variables I'm not sure how that will work. I still want my query to show the records that don't have a value set.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    its the bound column.
    if the cbo bound col = 2 then it will show the DDVal#
    if the cbo bound col = 1 then it will show the DDtext

    but if you CANT change the bound col from2, add an extra box to the form
    when user updates the combo , set the text box to the other column.

    Code:
    sub cboBox_afterupdate()
       txtBox = cboBox.column(0)   '(note in code, the columns  start with zero, so col 2 = .column(1), etc)
    end sub
    now you can query off the txtBox value (Moderate), or the cboBox value (2)
    because you cant query off cboBox.column(0)

  3. #3
    dano-wpg is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    3
    I should probably clarify: Right now in my report I only have text boxes; The combo boxes are in the form to enter the data.

    I will try extracting from what you've said and change a value to a combo box and see if that makes the difference.

  4. #4
    dano-wpg is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    3
    Ok so quick update. ranman's suggestion did get me thinking. I didn't even need to modify the query that the report is based on.

    The solution was to change the variable in the report from a text box to a combo box, and have the record source be from the query that has the text. Voila.

    Thanks for making me think just a bit differently about it. Didn't even need to write any code

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    No, reports don't need combo boxes. The form/query controls the report.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-27-2016, 07:36 PM
  2. Replies: 1
    Last Post: 02-06-2016, 05:33 AM
  3. Replies: 11
    Last Post: 02-20-2015, 10:45 AM
  4. Replies: 10
    Last Post: 03-22-2012, 07:00 PM
  5. Replies: 2
    Last Post: 10-09-2009, 07:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums