Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Minneapolis, MN
    Posts
    12

    Unanswered: Populating query paramaters from a form

    Good evening,

    I've run into a very frustrating problem. I have several different queries that pull values from unbound text boxes in forms. They all work like a charm - except one.

    I have been hammering at this one all day, and I can't get it to work. When I replace by form paramaters with actual values, I get data, so I know the query works. When I try to run it off the form, though, I get nothing. I've checked the names of the text boxes on the form and in the query countless times.

    I even went so far as to populate the form, then copy the appropriate paramater from the query into an immediate window and do the "Debug.print..." option - the values print just fine. So, it MUST be a problem with the query, correct?

    Here's the query. Any help is greatly appreciated!



    SELECT DBA_agent.phone AS PCAExt, [Forms]![frmNewScorecard]![txtEmpID] AS PCAEmpID, DBA_EvalHeadSummData116.EvalDate AS EvaluationDate, DBA_EvalQuesAnswerData116.Eval_id, IIf(DBA_EvalQuesAnswerData116.QA_Proper_grammer_et iq2235="Positive","Pos",IIf(DBA_EvalQuesAnswerData 116.QA_Proper_grammer_etiq2235="Neutral","Neu","Ne g")) AS PatExp, IIf(DBA_EvalQuesAnswerData116.QA_Appropriate_tone_ of_2236="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Appropriate_tone_of_2236="No","N","N/A")) AS PCAPromotePE, IIf(DBA_EvalQuesAnswerData116.QA_Empathetic_and_Un der2237="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Empathetic_and_Under2237="No","N","N/A")) AS GratitudeState, IIf(DBA_EvalQuesAnswerData116.QA_ToneInflection390 7="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA_ToneI nflection3907="No","N","N/A")) AS ToneInflection, IIf(DBA_EvalQuesAnswerData116.QA_Volume3908="Yes", "Y",IIf(DBA_EvalQuesAnswerData116.QA_Volume3908="N o","N","N/A")) AS Volume, IIf(DBA_EvalQuesAnswerData116.QA_Pace3909="Yes","Y ",IIf(DBA_EvalQuesAnswerData116.QA_Pace3909="No"," N","N/A")) AS Pace, IIf(DBA_EvalQuesAnswerData116.QA_Clarity3910="Yes" ,"Y",IIf(DBA_EvalQuesAnswerData116.QA_Clarity3910= "No","N","N/A")) AS Clarity, IIf(DBA_EvalQuesAnswerData116.QA_Voice_presentatio n_c3911="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Voice_presentation_c3911="No","N","N/A")) AS ConfidentVoice, IIf(DBA_EvalQuesAnswerData116.QA_Verbal_acknowledg eme2960="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Verbal_acknowledgeme2960="No","N","N/A")) AS VerbalAcknowledgement, IIf(DBA_EvalQuesAnswerData116.QA_Appropriately_use d_t2961="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Appropriately_used_t2961="No","N","N/A")) AS TeamStatements, IIf(DBA_EvalQuesAnswerData116.QA_Apology_or_empath y_c2962="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Apology_or_empathy_c2962="No","N","N/A")) AS ApologyEmpathy, IIf(DBA_EvalQuesAnswerData116.QA_Actively_engaged_ and2963="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Actively_engaged_and2963="No","N","N/A")) AS ActivelyEngaged, IIf(DBA_EvalQuesAnswerData116.QA_Desire_to_help_ca lle2964="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Desire_to_help_calle2964="No","N","N/A")) AS DesireToHelp, IIf(DBA_EvalQuesAnswerData116.QA_Allowed_caller_to _sp2965="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Allowed_caller_to_sp2965="No","N","N/A")) AS NoInterruption, IIf(DBA_EvalQuesAnswerData116.QA_Managed_call_in_o rga3912="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Managed_call_in_orga3912="No","N","N/A")) AS CallManagement, IIf(DBA_EvalQuesAnswerData116.QA_Managed_callers_q ue3913="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA_ Managed_callers_que3913="No","N","N/A")) AS DetailedAnswer, IIf(DBA_EvalQuesAnswerData116.QA_Ensured_callers_u nd3914="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA_ Ensured_callers_und3914="No","N","N/A")) AS CallerUnderstanding, IIf(DBA_EvalQuesAnswerData116.QA_Took_appropriate_ mea3915="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Took_appropriate_mea3915="No","N","N/A")) AS AppropriateMeasures, IIf(DBA_EvalQuesAnswerData116.QA_Educated_caller_o n_p3916="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Educated_caller_on_p3916="No","N","N/A")) AS EducatedCaller, IIf(DBA_EvalQuesAnswerData116.QA_Consistently_and_ app3917="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Consistently_and_app3917="No","N","N/A")) AS CourteousPhrasing, IIf(DBA_EvalQuesAnswerData116.QA_Used_appropriate_ for3918="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Used_appropriate_for3918="No","N","N/A")) AS FormalHold, IIf(DBA_EvalQuesAnswerData116.QA_Used_appropriate_ Cal3919="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Used_appropriate_Cal3919="No","N","N/A")) AS CallSilence, IIf(DBA_EvalQuesAnswerData116.QA_Gave_caller_an_ov era3920="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Gave_caller_an_overa3920="No","N","N/A")) AS ProfessionalCommunication, IIf(DBA_EvalQuesAnswerData116.QA_Verified_informat ion4008="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Verified_information4008="No","N","N/A")) AS VerifiedInformation, IIf(DBA_EvalQuesAnswerData116.QA_Documented_call_a ppr4009="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Documented_call_appr4009="No","N","N/A")) AS CallDocumented, IIf(DBA_EvalQuesAnswerData116.QA_Call_tracked_with _pe4010="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Call_tracked_with_pe4010="No","N","N/A")) AS CallReasons, IIf(DBA_EvalQuesAnswerData116.QA_Call_tracked_with _pr4011="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Call_tracked_with_pr4011="No","N","N/A")) AS CallStatus, IIf(DBA_EvalQuesAnswerData116.QA_Used_all_systems_ and2872="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Used_all_systems_and2872="No","N","N/A")) AS UsedResources, IIf(DBA_EvalQuesAnswerData116.QA_Used_other_depart men2873="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Used_other_departmen2873="No","N","N/A")) AS UsedDepartments, IIf(DBA_EvalQuesAnswerData116.QA_Initiated_appropr iat2874="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Initiated_appropriat2874="No","N","N/A")) AS FollowUp, IIf(DBA_EvalQuesAnswerData116.QA_Used_proper_compa ny_4059="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Used_proper_company_4059="No","N","N/A")) AS Greeting, IIf(DBA_EvalQuesAnswerData116.QA_Offered_additiona l_a4060="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Offered_additional_a4060="No","N","N/A")) AS AdditionalAssistance, IIf(DBA_EvalQuesAnswerData116.QA_Used_proper_compa ny_4061="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Used_proper_company_4061="No","N","N/A")) AS Closing, IIf(DBA_EvalQuesAnswerData116.QA_Utilized_appropri ate4062="Yes","Y",IIf(DBA_EvalQuesAnswerData116.QA _Utilized_appropriate4062="No","N","N/A")) AS Verbiage, IIf(DBA_EvalQuesAnswerData116.QA_Telling_the_custo mer2375="Yes","1","0") AS SB1, IIf(DBA_EvalQuesAnswerData116.QA_Negative_attitude _or2376="Yes","1","0") AS SB2, IIf(DBA_EvalQuesAnswerData116.QA_Use_of_vulgar_lan gua2377="Yes","1","0") AS SB3, IIf(DBA_EvalQuesAnswerData116.QA_Spoken_or_documen ted2378="Yes","1","0") AS SB4, IIf(DBA_EvalQuesAnswerData116.QA_Placing_a_caller_ on_2379="Yes","1","0") AS SB5, IIf(DBA_EvalQuesAnswerData116.QA_Disconnecting_a_c all2380="Yes","1","0") AS SB6, IIf(DBA_EvalQuesAnswerData116.QA_Not_answering_a_c all2381="Yes","1","0") AS SB7, IIf(DBA_EvalQuesAnswerData116.QA_Eating_including_ gu2405="Yes","1","0") AS SB8, IIf(DBA_EvalQuesAnswerData116.QA_Misleading_or_giv ing2406="Yes","1","0") AS SB9, IIf(DBA_EvalQuesAnswerData116.QA_Placing_a_caller_ on_2407="Yes","1","0") AS SB10, IIf(DBA_EvalQuesAnswerData116.QA_Unrelated_Email_o r_i2789="Yes","1","0") AS SB11, IIf(DBA_EvalQuesAnswerData116.QA_Did_not_obtain_th e_m2790="Yes","1","0") AS SB12, IIf(DBA_EvalQuesAnswerData116.QA_HIPPA_NonComplian ce4306="Yes","1","0") AS SB13, ([SB1]+[SB2]+[SB3]+[SB4]+[SB5]+[SB6]+[SB7]+[SB8]+[SB9]+[SB10]+[SB11]+[SB12]+[SB13]) AS SumOfServiceBreakdowns, IIf([SumOfServiceBreakdowns]>0,"Y","N") AS ServiceBreakdown
    FROM DBA_userid INNER JOIN (DBA_agent INNER JOIN (DBA_EvalQuesAnswerData116 INNER JOIN DBA_EvalHeadSummData116 ON DBA_EvalQuesAnswerData116.Eval_id = DBA_EvalHeadSummData116.Eval_id) ON DBA_agent.agentlink = DBA_EvalHeadSummData116.AgentLink) ON DBA_userid.useridlink = DBA_EvalHeadSummData116.ObserverLink
    WHERE (((DBA_agent.phone)=[Forms]![frmNewScorecard]![txtPCAExtension]) AND ((DBA_EvalHeadSummData116.EvalDate) Between [Forms]![frmNewScorecard]![txtEvalPeriodStart] And [Forms]![frmNewScorecard]![txtEvalPeriodEnd]));

  2. #2
    Join Date
    Apr 2004
    Location
    Minneapolis, MN
    Posts
    12
    Disregard my previous post - here's where the problem lay (it wasn't with the query after all): After the user clicked a "Run" button on the form, a public sub was called. Once the public sub finished executing, then I'd call the query. An if statement existed in the query like this (names changed for simplification):

    If gblThisVariable = -1 Then
    'Run the troublesome query
    ElseIf gblThisVariable = 0 Then
    'Run something a little less troublesome
    End if

    The global variable checked a table on program start-up and, for this particular business, populated it with a -1.

    Unfortunately, I had too much coffee one day (or not enough), and decided to "Dim gblThisVariable As Integer" at the top of the sub in question - thus cancelling out how I'd populated it on startup! Hence, the variable always read 0, and the troublesome query never ran!

    Sometimes, I want to slap myself!!

    Thanks for looking,
    Paul

Posting Permissions

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