MS ACCESS 2007 queries that present you with a parameter value dialogue box

[larocqgi]

I have designed a query in Access 2007. What verbiage do I use in the criteria row of the field in question in order to have the user presented with a dialogue box that tells him that he needs to enter a parameter before the query is run? I want my query designed like such because the users are not tech-savy when it comes to designing a query. The field in question is labelled as "comment".

[NeoPa]

Any text within brackets - [] - which doesn't match up to a valid reference will be treated as a prompt for a value from the operator.

[larocqgi]

I know about the brackets, but it doesn't work. I'm trying to query products that have latex, for example, in them. Because "latex" appears in a field with other words, all separated by a comma, I put in my box [enter *comment*]. I've tried different versions and nothing works.

[NeoPa]

So, why didn't you ask that question - instead of the one you did ask. I answered your question - the question for this thread - correctly already (If that doesn't work for you then you're simply not using it correctly).

If you want to filter a field called [Comments] such that it finds records where the field contains the value that you want to prompt for, then you would need something like :

WHERE [Comments] Like '*' & [Enter filter value] & '*'

The [Enter filter value] part is an example and illustration of the answer to the original question.

[larocqgi]

Let me be clearer because the above does not work. In my query, I have three fields (product, dosage form, and comment). This query is built from a "product" table which consists of several fields, one of which is "comment". The "comment" field is made up of words separated by commas. For example, for a given product, there might be in the "comment" field the following text: FD&C No. 1, latex, non-kosher, sulfites.
I want to design my query in such a way that when another user uses that query, he will not have to input, for example, "latex" in the design view. I want the user to click the appropriate query button in the switchboard. When the user clicks that specific query button, a dialog box will appear that will prompt the user to enter "latex" because he will want to search for all products containing latex. When I do this with the expression that you have given me, it returns zero results and I know for a fact that this is not possible. If I do my query manually by entering "latex" in the design view, I get results.

Thanks for any help you can provide me.

[NeoPa]

Larocqgi:
Let me be clearer because the above does not work.

Clearer's a great idea, but I already explained that if it's not working then you're not doing it correctly.

Let's start with clearer anyway. A good (and fairly obvious) way to be clear is to include details of :

1. Exactly what you're doing (The SQL executed).
2. The layout of the data you're working with (Table [Product] and Fields [Product], [Dosage Form] & [Comment]).
3. The source data (Some example records that illustrate the point).
4. The test data ("latex").
5. The actual results (Empty).
6. The expected results.

You've already indicated what results you're getting (#5), which is good, and what you expect (#6) would be pretty obvious when we can see the data used. You've also supplied the filter value you used in your test (#4) and most of the details of your data layout (#2), although FieldType of [Comments] may be important if it's other than Text - EG. Memo. If you provide the SQL (#1) and the sample data (#3) I will endeavour to determine what is going wrong.

[larocqgi]

Boy, you ask tough questions! Let's see if I understand your questions.

Data Type is "text".

#3 Sample Data: The property sheet for my "comment" field indicates that the control source is "comment".

#1 SQL: If the user was to do the query manually, in the design view of the query, I would enter in the criteria row of the "comment' field, Like "*non-kosher*". This will bring me accurate results. It will give me a list of products which are non-kosher. But what I need is what do I enter so that the user will only need to type "non-kosher" in a parameter value dialog box when he clicks on the query button of the switchboard.

Thanks,

[NeoPa]

Let me see if I can explain myself better. I'll return to the original order if I may, as that seems to make more sense to me.

#1 - SQL - I need you to post the SQL you were using when this went wrong. EG :

SELECT [Product]
     , [Dosage Form]
     , [Comment]
FROM   [Product]
WHERE  ([Comment] Like '*' & [Enter filter value] & '*')

This is just an example. I need you to post what you used.

#3 - The Source Data - I need you to post some data records that reflect your example data. EG :

Product  Dosage Form  Comment
A        X1           FD & C No 1, Nonkosher, Sulfites
B        X1           Latex, Sulfites
C        X1           Nonkosher, Sulfites
D        X1           Latex
E        X1           FD & C No 1, Latex, Sulfites
F        X1           Nonkosher

In this example I would expect to see results including products B, D & E.

[larocqgi]

NeoPa, you have #3 (source data) to perfection. No need to add to this one.

As for the SQL, I used the PRODUCT table, and the following fields from that table: PRODUCT, DOSAGE FORM, COMMENT. In the COMMENT field, I have tried the following expressions in the "criteria" row:

1. [enter comment] This causes dialog box to appear when I run the query, but zero returns.
2. Like [enter*comment*] This causes dialog box to appear when I run the query, but zero returns.
3. WHERE [Comments] Like '*' & [Enter comment] & '*'. This causes dialog box to appear when I run the query, but zero returns.

Thanks,

Ginette

[NeoPa]

Let's see if we can't put this one to bed Ginette. Let me start by saying that option #3 in your list is exactly as I would expect. This should work.

However, and this is quite a big however, I need exact details from you for this, not just general indications. As such, I need you to post your example data precisely (not simply a general indication, but exact details - although not all records need be included). I need you to post your exact SQL - copy pasted from your project.

It seems clear to me that something, somewhere, is not exactly as represented. This is quite common. People can overlook details when relating information. I myself struggled with some data just yesterday when the total value worked out by my program was different from my adding up all the individual values. I had to look and look until I noticed a value I'd got as 15 but should have been 11.

There is nothing wrong with the setup as you've described it, so I assume there is a minor detail wrong somewhere, which I can identify if I have direct access to all the information, but not from general indications (Certainly not those I have already as they're checked and confirmed as fine by me - yet you're reporting they're not working for you).

Another alternative for you is to attach your database. If you'd like to take that approach, you will need to follow the instructions in Attach Database (or other work) very carefully. If you manage that properly I will happily look into it for you. Clearly, I'll then need less from you in the way of posting information. I will endeavour to explain any problem I find either way.

[larocqgi]

Let's see if I can give you what you want without sacrificing privacy.

In one of my records for "comments", I have: FD&C Blue No. 1, animal-sourced gelatin, sunflower oil, sulfites, chemical solvents (acetic acid, ethanol, n-butyl, isopropyl alcohol), non-kosher, no dedicated mfg'ing line, Ca, Mg, K, Na, sorbic acid as preservative

In another record for "comments" I have: Numerous artificial colours, animal-sourced gelatin, sunflower oil, sulfites, chemical solvents (acetic acid, ethanol, n-butyl, isopropyl alcohol), non-kosher, no dedicated mfg'ing line, Ca, Mg, K, Na, sorbic acid as preservative

And lastly, here is a snapshot of my query in "design view".

Thanks.

[NeoPa]

Panic over. All is now clear :-)

Let's start by giving you a link that will make the sharing of SQL code, and queries generally, much easier for you - Extracting/Updating SQL from a QueryDef. You can use it to update your query with my suggested solution code if you like.

Anyway, the problem is that the format of the solution I suggested is specifically for the SQL version of the query. It cannot be used, exactly as suggested, on the criteria line. I missed this earlier (You explained it correctly) as it's very common for members to use incorrect terms in their explanations and I assumed you'd understand that this was not something to put into the Criteria slot. My error.

The Criteria value could be done as :

Like '*' & [Enter comment] & '*'

The whole SQL could be :

SELECT [Product]
     , [Dosage Strength/Dosage Form]
     , [Comment]
FROM   [Product]
WHERE  [Comment] Like '*' & [Enter comment] & '*'

[larocqgi]

NeoPa, you did it!! THANK YOU. I just tried the new code in my criteria row and it works. I'm so relieved! Thanks again. :)

[NeoPa]

LarocqGi:
NeoPa, you did it!!

Always with the surprise!! :-D

I'm glad we got there in the end Ginette :-)