Custom filled Query

[leo1]

Is there a way to click on a query and have it ask to enter certain information for that query? I think Access could do that but I don't remember how.
What I have is a field that has the 12 months listed in it and I would it to ask to enter that month instea of making up 12 separate queries. How would I go about doing this.

Thank You

[DrewJensen]

In the criteria section add a colon followed by a string.

For example

SELECT * FROM TABLE1 WHERE COL_1 = :Val

When that query is run a dialog box will pop up and ask for the value to replace :val with.

You can have multiple 'replaceable parameters' such as this.

[leo1]

I pasted the example you gave but nothing showed up in the query and it didn't ask to enter any information. Can you advised on what I missed? Thank you very much for your help.
Is the table1 in your example supposed to be the table where my information is stored?

[DrewJensen]

yes - it was just an example.

[leo1]

This is what I put in the criterion section but it still doesn't ask for anything. The field "try back month" in my table uses a drop down list that you can select every month of the year. Do I need to put something in the string for that?

':''SELECT * FROM Bizcontacts WHERE COL_1 = :Val'''

[DrewJensen]

Alright - let's back up.

Are you comfortable with creating queries using the query designer in general?
Could you create a query that gives you what you want with a specific criteria in the designer now?

[leo1]

I'm still new but I think I could do it.

[leo1]

I created a simple one for now I can add other fields to it later.

First Field in Companyname
Second is First name
Last (is the one I want) Try back Month

[DrewJensen]

Ok - start a new query with 'Create query in design view'
First thing you do is select the table you want from 'Add table or query'.
After you select the table and click on Add then close that dialog box.

Now in the designer you can add all the columns from the table into the query by double clicking on the asterisk, the first entry in the tables list of columns.

Now find the column you want to use for the WHERE clause, ( the one you want to enter the search value for ) and double click on this.

This adds this column to the query.

In the grid on the lower part of the designer window you will see a row of checkboxes. The ones for the two column now added ( * and the single one you chose ) have green checks in these. Un tick the check box for the single column you just added.

In that column, two rows below is the 'Criteria' row. Enter a : and some word that makes sense as a prompt. Maybe :BackMonth

Ok try to run the query by clicking on the run query toolbutton.

[leo1]

Thanks it works. Thats awesome way easier than MSA

[leo1]

Is it possible to pick from multiple options for the query? Like picking from A B C for the query instead of typing it in?

Thank you

[Villeroy]

No, you can not offer allowed values in a listbox. This is where input forms come into play.

[leo1]

A follow up to this question. How would I make it so that the value wouldn't have to be exact? For example if the query was looking up the month in the year and I typed in only "Ju". It would bring up all the months that start with "Ju".
If its possible how would the formula look?

Thank You

[Villeroy]

See help-index, OOo Base, "designing;queries (Base)". Read about the "LIKE" operator.

[leo1]

I just switched to OOo 2.4 and now I can't get any of the queries I made to work, anyone else have this issue