Query with form control as parameter not working (2003)

[IanR]

Hi, I have a problem query. It looks at a text box on a form to get the value it needs for a where clause. A simple thing that I have set up many times before. Unfortunately, this query is not working because it cannot seem to resolve the value of the text box. If I remove the form reference from the crieria pane of the query, the query brings back lots of records. If I replace the form reference with the value that I know is in the text box on the form, the query works (i.e., it brings back the data that I am expecting). The query is called from code. At the point where it is called I have stopped the code and used the immediate window to show the value returned by the form reference that I am using in the criteria pane (? [Forms]![frm_EnterPickResults]![BatchIDx]) and the expected data (in this case a batch ID) comes back. If I copy this batch ID, that I have acquired in the immediate window, back into the query - the query works.

As far as I can tell, the form control reference is not being resolved by the query, even though it is resolved if typed into the immediate window. A very similar query, that is run from code in the previous line, which uses the same form control as a parameter, works fine.

Is this a bug?

This is the query that does not work:

INSERT INTO tbl_Packaging ( BatchID, OrderID, apar_id, LineNum, PackType, ProdCode, QtyToPick, PickDate )
SELECT tbl_PickAndPackBatchDetail.BatchID, tbl_PickAndPackBatchDetail.order_id, tbl_PickAndPackBatchDetail.apar_id, tbl_PickAndPackBatchDetail.line_no, "Pouches" AS PackType, qry_Relation_Pouches.rel_value, Sum(tbl_PickAndPackBatchDetail.QtyPicked) AS SumOfQtyPicked, First(Date()) AS PickDate
FROM (tbl_PickAndPackBatchDetail LEFT JOIN qry_AlgArticleClient ON tbl_PickAndPackBatchDetail.article = qry_AlgArticleClient.article) LEFT JOIN qry_Relation_Pouches ON qry_AlgArticleClient.article_id = qry_Relation_Pouches.article_id
WHERE (((qry_Relation_Pouches.rel_value) Is Not Null And (qry_Relation_Pouches.rel_value)<>"0" And (qry_Relation_Pouches.rel_value)<>"") AND ((tbl_PickAndPackBatchDetail.BatchID)=[Forms]![frm_EnterPickResults]![BatchIDx]) AND ((tbl_PickAndPackBatchDetail.QtyPicked)>0))
GROUP BY tbl_PickAndPackBatchDetail.BatchID, tbl_PickAndPackBatchDetail.order_id, tbl_PickAndPackBatchDetail.apar_id, tbl_PickAndPackBatchDetail.line_no, "Pouches", qry_Relation_Pouches.rel_value;

Thanks

[HansV]

How are you calling the query from code? That may make a difference.

[IanR]

Hi Hans,

Both the query that works, and the one that doesn't, are called using DoCmd.OpenQuery "QueryName"

Ian

[HansV]

I'm afraid I'd have to see the database then. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

[GARYPSWANSON]

Ian,

I have had something similar to this happen in databases where multiple update type queries are run one after one another. All of the queries seemed fine, however, to get them to work when being launched by code, I had to put in a docmd.close query queryname to close the queries before running the next one. Never did figure out what the issue was but it worked.

Just a shot in the dark here, but try using trim with your data to get rid of any trailing spaces that you dont see. Also make sure you dont have conflicting data types. HTH

[IanR]

Gary,
That looks very interesting. I must admit that this is not the first time that I have seen this problem, it's just the first time that I couldn't get around it by getting the query parameters from somewhere else. I'm not in the office today but I will check the query closing trick tomorrow.
Hans,
If Gary's tip doesn't work I'll send in a database. As I say, I have seen this before and would like to know what is going on.

Thanks
Ian