DoCmd.SetParameter Help

[state90]

I must have this wrong because it keeps returning an error message when I try to run the command button. For the life of me, I have researched how to use the DoCmd.SetParameter and it is set up exactly how I have seen it used. Please advise....

* "Country_Code" is a field in the query to be opened
* "RPT_EXP_By_Cat_SummTEST" is the query
* Me!TExt120 is the text box on the form that contains the criteria selection I wish to set for the field "Country_Code"

Private Sub Command161_Click()

DoCmd.SetParameter "Country_Code", Me!Text120
DoCmd.OpenQuery " RPT_EXP_By_Cat_SummTEST", , acViewNormal
Forms("Frm_PARAMETER_Metrics").Visible = False

End Sub

Any help is much appreciated.

 

[JHB]

What is the error number and description you get?

 

[arnelgp]

on you Open query, you have extra space:
" EXP_By_Cat_SummTEST"

should be:
"EXP_By_Cat_SummTEST"

 

[state90]

Sorry...took the weekend off!

The error message I get is
"Run-Time error "2498";
An expression you entered is the wrong data type for one of the arguments"

When I select "Debug" it opens the VBE and the following row is highlighted

DoCmd.SetParameter "Country_Code", Me!Text120

Text120 is a text field and Country Code is a text field

 

[state90]

Good catch arnelgp. I did fix the space but the error is still the same. Man....I wish that simple mistake had fixed it!

 

[arnelgp]

inspect your query and the datatype of your parameter.

 

[JHB]

Show the SQL-string for the query.

 

[state90]

Here is the SQL for the query.

SELECT REF_ACT_Expense_Current_Final.Category_Sort, REF_ACT_Expense_Current_Final.Category, Sum(REF_ACT_Expense_Current_Final.Functional_USD_Amount) AS Functional_USD_Amount, REF_ACT_Expense_Current_Final.Region, REF_ACT_Expense_Current_Final.Function, REF_ACT_Expense_Current_Final.Geography, REF_ACT_Expense_Current_Final.BudgetRegion, REF_ACT_Expense_Current_Final.Cost_Center_Num, REF_ACT_Expense_Current_Final.Country_Code, REF_ACT_Expense_Current_Final.CountryName, REF_ACT_Expense_Current_Final.Legal_Entity, Sum(IIf([REF_ACT_Expense_Current_Final]![Contract_Month]=[Forms]![Frm_PARAMETER_Metrics]![CMLess3],[REF_ACT_Expense_Current_Final]![Functional_USD_Amount],0)) AS CMLess3Amt, Sum(IIf([REF_ACT_Expense_Current_Final]![Contract_Month]=[Forms]![Frm_PARAMETER_Metrics]![CMLess2],[REF_ACT_Expense_Current_Final]![Functional_USD_Amount],0)) AS CMLess2Amt, Sum(IIf([REF_ACT_Expense_Current_Final]![Contract_Month]=[Forms]![Frm_PARAMETER_Metrics]![CMLess1],[REF_ACT_Expense_Current_Final]![Functional_USD_Amount],0)) AS CMLess1Amt, Sum(IIf([REF_ACT_Expense_Current_Final]![Contract_Month]=[Forms]![Frm_PARAMETER_Metrics]![CM],[REF_ACT_Expense_Current_Final]![Functional_USD_Amount],0)) AS CMAmt
FROM REF_ACT_Expense_Current_Final INNER JOIN REF_Legal_Entity_Mapping ON REF_ACT_Expense_Current_Final.Legal_Entity = REF_Legal_Entity_Mapping.Legal_Entity_Code
WHERE (((REF_ACT_Expense_Current_Final.Contract_Month)=[Forms]![Frm_PARAMETER_Metrics]![CM] Or (REF_ACT_Expense_Current_Final.Contract_Month)=[Forms]![Frm_PARAMETER_Metrics]![CMLess1] Or (REF_ACT_Expense_Current_Final.Contract_Month)=[Forms]![Frm_PARAMETER_Metrics]![CMLess2] Or (REF_ACT_Expense_Current_Final.Contract_Month)=[Forms]![Frm_PARAMETER_Metrics]![CMLess3]))
GROUP BY REF_ACT_Expense_Current_Final.Category_Sort, REF_ACT_Expense_Current_Final.Category, REF_ACT_Expense_Current_Final.Region, REF_ACT_Expense_Current_Final.Function, REF_ACT_Expense_Current_Final.Geography, REF_ACT_Expense_Current_Final.BudgetRegion, REF_ACT_Expense_Current_Final.Cost_Center_Num, REF_ACT_Expense_Current_Final.Country_Code, REF_ACT_Expense_Current_Final.CountryName, REF_ACT_Expense_Current_Final.Legal_Entity
HAVING (((REF_ACT_Expense_Current_Final.Category_Sort)="1" Or (REF_ACT_Expense_Current_Final.Category_Sort)="2" Or (REF_ACT_Expense_Current_Final.Category_Sort)="3" Or (REF_ACT_Expense_Current_Final.Category_Sort)="4" Or (REF_ACT_Expense_Current_Final.Category_Sort)="5" Or (REF_ACT_Expense_Current_Final.Category_Sort)="6" Or ((REF_ACT_Expense_Current_Final.Category_Sort)="1" Or (REF_ACT_Expense_Current_Final.Category_Sort)="2" Or (REF_ACT_Expense_Current_Final.Category_Sort)="3" Or (REF_ACT_Expense_Current_Final.Category_Sort)="4" Or (REF_ACT_Expense_Current_Final.Category_Sort)="5" Or (REF_ACT_Expense_Current_Final.Category_Sort)="6") Or ((REF_ACT_Expense_Current_Final.Category_Sort)="1" Or (REF_ACT_Expense_Current_Final.Category_Sort)="2" Or (REF_ACT_Expense_Current_Final.Category_Sort)="3" Or (REF_ACT_Expense_Current_Final.Category_Sort)="4" Or (REF_ACT_Expense_Current_Final.Category_Sort)="5" Or (REF_ACT_Expense_Current_Final.Category_Sort)="6") Or ((REF_ACT_Expense_Current_Final.Category_Sort)="1" Or (REF_ACT_Expense_Current_Final.Category_Sort)="2" Or (REF_ACT_Expense_Current_Final.Category_Sort)="3" Or (REF_ACT_Expense_Current_Final.Category_Sort)="4" Or (REF_ACT_Expense_Current_Final.Category_Sort)="5" Or (REF_ACT_Expense_Current_Final.Category_Sort)="6") Or ((REF_ACT_Expense_Current_Final.Category_Sort)="1" Or (REF_ACT_Expense_Current_Final.Category_Sort)="2" Or (REF_ACT_Expense_Current_Final.Category_Sort)="3" Or (REF_ACT_Expense_Current_Final.Category_Sort)="4" Or (REF_ACT_Expense_Current_Final.Category_Sort)="5" Or (REF_ACT_Expense_Current_Final.Category_Sort)="6") Or ((REF_ACT_Expense_Current_Final.Category_Sort)="1" Or (REF_ACT_Expense_Current_Final.Category_Sort)="2" Or (REF_ACT_Expense_Current_Final.Category_Sort)="3" Or (REF_ACT_Expense_Current_Final.Category_Sort)="4" Or (REF_ACT_Expense_Current_Final.Category_Sort)="5" Or (REF_ACT_Expense_Current_Final.Category_Sort)="6") Or ((REF_ACT_Expense_Current_Final.Category_Sort)="1" Or (REF_ACT_Expense_Current_Final.Category_Sort)="2" Or (REF_ACT_Expense_Current_Final.Category_Sort)="3" Or (REF_ACT_Expense_Current_Final.Category_Sort)="4" Or (REF_ACT_Expense_Current_Final.Category_Sort)="5" Or (REF_ACT_Expense_Current_Final.Category_Sort)="6")))
ORDER BY REF_ACT_Expense_Current_Final.Category_Sort;

 

[arnelgp]

country_code is a field in your previous query and is not a parameter. also you have a lot of redundant statement in your Having clause, should be trimmed to:

HAVING (REF_ACT_Expense_Current_Final.Category_Sort BETWEEN "1" AND "6")

or

HAVING (REF_ACT_Expense_Current_Final.Category_Sort IN ("1,"2","3","4","5","6")

 

[state90]

I always feel guilty relying on experts such as yourself in these forums so I do make sure I am learnign instead of just relying on you to fix everythign for me so I am in the middle of really learning VBA I bought Access 2013: The Bible or whatever it is called so progress on that end. The DoCmd.SetParamter was something I came across in these forums when Googling solutions. I am not even sure if that resolves what I need to accomplish so no surprise that I had it in the wrong context. I will keep cranking on VA and circle back when I have more to base my request for guidance on! SQL...I have no experience with other than knowing that I can build a query and then view the SQL view so when I saw all those Having clauses that was the result of buildign the query in the front-end view...if that is the rigth phrase. SQL education comes next! I appreciate your help and will try those changes you suggest and focus on exactly what the problem I need to resolve requires in terms of the VBA coding.

 

[arnelgp]

we are all learners, sir, we become mature over time. challenges like yours drives us to do more, learn more, search for more. i cannot claim i know all, that what ive been taught, what i have read, what i have searched, the same info is what im sharing.