Syntax problem (2000 sr3)

Topic

New Reply

I am using an unbound form to feed parameters to a query. On the left I have a list box with the Multi-select property set to extended. I feed the selected items into a textbox on the right which has some code behind it to format the string. The textbox will eventually be hidden. In the query I have as a parameter [forms]![formname]![textboxname] (not their real names, obviously).

When I input a single record, the query runs fine. When I put in more than one, I end up with an error message saying the parameters are too complicated. The string created in the textbox is just a list of numbers separated by Or exactly as I would type into the criteria of the query by hand, e.g. 1 Or 3 Or 6

I am missing something (perhaps quotes?) when I create my string. I know it shouldn’t be a difficult problem, but I’m not sure where I am dropping the ball. I am working with numbers, but since these are generated from a couple of earlier levels of query to filter out my end list, I’m not sure whether that may be some of my problem. I did try setting the format in the parameter query to General Number but that didn’t make a difference. I can post the code if you want to see it. It is taken from Access Developer’s Handbook, chapter 7 – Multiselect. The breakdown is clearly in feeding multiple numbers to the query.

Reply | Quote

Replies

Or is a keyword in SQL. You can only use a parameter or a form to feed constant values to a query, not SQL keywords such as In or Or or And.

MSKB article ACC2000: How to Create a Parameter In() Statement describes two methods to get around this limitation. The article has a link to a downloadable sample queries database with a working example.

Reply | Quote

Thanks, as usual, Hans. This works fine. Is there any way I can hook this up so that the parameter prompt doesn’t appear, but takes its information from the form as I had originally envisioned? If it will be complicated, I won’t bother. My main interest is to make this simple and obvious for the end user. The multiselect list allowed the user to see the name associated with the number that the query would use and if I can do it this way, I would prefer to.

On a related front. Can you explain what exactly the Instr function is doing? In the KB article, the first string was the message in the parameter prompt. In the sample queries database, the second string was the message. Is this because a non-field name gets interpreted as a call for a parameter prompt so the string to be compared is what actually gets typed into the message box? Just trying to understand what is actually going on.

Reply | Quote

Never mind on fixing the form. In a fit of rational thinking – – I figured it out. I used the Instr function in a new column with the following syntax: InStr([Forms]![Form2]![Text2],[EmployeeID]) As in the KB article, I had “> 0 or is null ” in the criteria and Bob’s your uncle Worked fine. I was sure I tried that one, but obviously hadn’t prayed quite properly to the Access gods.

If you want to answer the theoretical part of my question, I’d still be interested.

Reply | Quote

Never mind on fixing the form. In a fit of rational thinking – – I figured it out. I used the Instr function in a new column with the following syntax: InStr([Forms]![Form2]![Text2],[EmployeeID]) As in the KB article, I had “> 0 or is null ” in the criteria and Bob’s your uncle Worked fine. I was sure I tried that one, but obviously hadn’t prayed quite properly to the Access gods.

If you want to answer the theoretical part of my question, I’d still be interested.

Reply | Quote

The MSKB article mentions two different ways to enter a list of values in a query parameter: the first one uses InStr, the second a user-defined function InParam. The order of the arguments of InParam is the opposite of that in InStr; this has no intrinsic reason, it’s just the way the one who wrote it designed it. The example in the sample queries database (qryInParam) employs the second method.

Reply | Quote

The MSKB article mentions two different ways to enter a list of values in a query parameter: the first one uses InStr, the second a user-defined function InParam. The order of the arguments of InParam is the opposite of that in InStr; this has no intrinsic reason, it’s just the way the one who wrote it designed it. The example in the sample queries database (qryInParam) employs the second method.

Reply | Quote

Thanks, as usual, Hans. This works fine. Is there any way I can hook this up so that the parameter prompt doesn’t appear, but takes its information from the form as I had originally envisioned? If it will be complicated, I won’t bother. My main interest is to make this simple and obvious for the end user. The multiselect list allowed the user to see the name associated with the number that the query would use and if I can do it this way, I would prefer to.

On a related front. Can you explain what exactly the Instr function is doing? In the KB article, the first string was the message in the parameter prompt. In the sample queries database, the second string was the message. Is this because a non-field name gets interpreted as a call for a parameter prompt so the string to be compared is what actually gets typed into the message box? Just trying to understand what is actually going on.

Reply | Quote

Or is a keyword in SQL. You can only use a parameter or a form to feed constant values to a query, not SQL keywords such as In or Or or And.

MSKB article ACC2000: How to Create a Parameter In() Statement describes two methods to get around this limitation. The article has a link to a downloadable sample queries database with a working example.

Reply | Quote