Query parameters - Too few parameters problem

[mholderi]

Hello,
As I am very lost in this kind of request I'm asking you some help...

Thanks of this forum I'm able now to run a query with one parameter.

The code is:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set dbs = CurrentDb
'Get the parameter query
Set qdf = dbs.QueryDefs("Query_CheckListe_List")
'Supply the parameter value
qdf.Parameters("Status") = "Ready_For_Check_List"
'Open a Recordset based on the Query_CheckListe_List
Set Me.Recordset = qdf.OpenRecordset(dbOpenDynaset)
Me.Requery

This is runing fine for the query with only one parameter: [Status].

Now in this query I would like to add also a condition on an other field link to a combobox in the same Form.
It would be: Like ([Forms]![TA_CheckList_Preparation]![Combo_TypeOfTrade]) & "*"

But If I open the form again I get the message: "Too few parameters. Expected 2."

Could someone help me, please?
Thank you.
Michel.

 

[apr pillai]

Change the statement as given below:

Your expression:

Like ([Forms]![TA_CheckList_Preparation]![Combo_TypeOfTrade]) & "*"

Changed expression:
Like chr$(34) & ([Forms]![TA_CheckList_Preparation]![Combo_TypeOfTrade]) & "*" & chr$(34)

The result of this expression will look like the following:

Like "CriteriaText*"

 

[mholderi]

Thank you for your answer, but must I have to replace this statement in the query itself or in the VBA code? I tried to insert it in the VBA but the "Like" expression is not recognize

Here what I tried:

'Supply the parameter value
'Like ([Forms]![TA_CheckList_Preparation]![Combo_TypeOfTrade]) & "*"
qdf.Parameters("Type_Trade") = Like chr$(34) & ([Forms]![TA_CheckList_Preparation]![Combo_TypeOfTrade]) & "*" & chr$(34)
qdf.Parameters("Status") = "Ready_For_Check_List"

i tried also directly in the query but I have the same error message as before.

Thank you.

 

[JHB]

Your setup is in a kind wrong, parameters is used for transferring a value in a variable to the query, and not as you are trying which is to transfer the whole criteria part.
Open your query and look how it is using the parameter "Status".

 

[mholderi]

Hello JHB,
Thank you for your remark, but if I tried only with the parameter[Status] it's working correctly.
Now I would like to add another criteria which is a result of a combobox in the form itself. If I input it in the query like:

Like ([Forms]![TA_CheckList_Preparation]![Combo_TypeOfTrade]) & "*"

I have the error message: "Too few parameters. Expected 2."

So what I tried was to transmit as second parameter the full code "like...", but it seems it's also not working

Any idea?
Thx,
Michel.

 

[JHB]

Show the SQL string for the query "Query_CheckListe_List".

 

[mholderi]

PARAMETERS Status Text ( 255 );
SELECT Table_Import_VPBank_Deals.[Type of Trade], IIf(IsNull([Registerred_name])=True,"No Name configured",[Registerred_name]) AS Registered_name, Table_Import_VPBank_Deals.Isin, Table_Import_VPBank_Deals.[Order Date and Time], Table_Import_VPBank_Deals.[Trade Date], Table_Import_VPBank_Deals.[Trade Date] AS BIBI, Table_Import_VPBank_Deals.[Value Date], Table_Import_VPBank_Deals.Shares, Table_Import_VPBank_Deals.Gross, Table_Import_VPBank_Deals.[Fee %], Table_Import_VPBank_Deals.[Order-Nr CSP], Table_Import_VPBank_Deals.Make_CheckList
FROM Table_Import_VPBank_Deals LEFT JOIN TA_Cut_Off_Minimum ON Table_Import_VPBank_Deals.Isin = TA_Cut_Off_Minimum.ISIN
WHERE (((Table_Import_VPBank_Deals.[Type of Trade]) Like ([Forms]![TA_CheckList_Preparation]![Combo_TypeOfTrade]) & "*") AND ((Table_Import_VPBank_Deals.TA_Status)=[Status]));

 

[JHB]

As I mention in post #4, you setup is wrong, (read it again for explanation).
On the hand there are 2 ways to do it if the combobox [Combo_TypeOfTrade] is in the same form, then you can use both solutions, else only no 1. can be used.
No 1.

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set dbs = CurrentDb
'Get the parameter query
Set qdf = dbs.QueryDefs("Query_CheckListe_List")
'Supply the parameter value
qdf.Parameters("Status") = "Ready_For_Check_List"
qdf.Parameters("ComboBoxValue") = "[Forms]![TA_CheckList_Preparation]![Combo_TypeOfTrade]"
'Open a Recordset based on the Query_CheckListe_List
Set Me.Recordset = qdf.OpenRecordset(dbOpenDynaset)
Me.Requery

Change the Parameter part and Where part in your query to:

PARAMETERS Status Text ( 255 ), ComboBoxValue Text ( 255 );
....
WHERE (((Table_Import_VPBank_Deals.[Type of Trade]) Like Eval([ComboBoxValue]) & "*") AND ((Table_Import_VPBank_Deals.TA_Status)=[Status]));

No 2.

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Set dbs = CurrentDb
'Get the parameter query
Set qdf = dbs.QueryDefs("Query_CheckListe_List")
'Supply the parameter value
qdf.Parameters("Status") = "Ready_For_Check_List"
qdf.Parameters("ComboBoxValue") = Me.Combo_TypeOfTrade
'Open a Recordset based on the Query_CheckListe_List
Set Me.Recordset = qdf.OpenRecordset(dbOpenDynaset)
Me.Requery

Change the Parameter part and Where part in your query to:

PARAMETERS Status Text ( 255 ), ComboBoxValue Text ( 255 );
....
WHERE (((Table_Import_VPBank_Deals.[Type of Trade]) Like [ComboBoxValue] & "*") AND ((Table_Import_VPBank_Deals.TA_Status)=[Status]));

 

[mholderi]

Hello,
Thank you for your answer it's working as expected, but as I have a lot of criterias, it will become very hard to manage the query with references. I think I will duplicate this query
See attached a print screen of my actual query.
Thanks again for your time.
@+
Michel.

 

[JHB]

Hello,
Thank you for your answer it's working as expected, but as I have a lot of criterias, it will become very hard to manage the query with references

Maybe - but, I see you have given your field names and controls meanfull names, so no, I don't think so!
Only for info - don't have spaces in the field name - like "Type of Trade" better and still understandable is "TypeOfTrade".