How do I create a parameter query?

[ptoye]

I’m having difficulty in understanding the help on parameter queries. It seems to imply that I can set a “variable” to be used as input for a query - presumably as part of a criterion (at least, that’s how Access worked when I used it decades ago). But the description veers between “variable” and “placeholder” without defining either.

I tried (in Query Design) view typing =[date] into the criterion field, but was told that there wasn’t a column called “date”. Adding quotes didn’t help either. Trying SQL view and adding * WHERE “Year” = :[Date]* gave an SQL error.

Can some kind soul please tell me where I’m going wrong?

Thanks to those who answered - but see these screenshots.

Using the [] notation I get Parameter 1.png Parameter 2.png Parameter 3.png

Using the : notation I get SQL errors, and then I can’t even open the query for editing Parameter 4.png(/upfiles/14462864181636484.png) Parameter 5.png

Using LO 5.0.2.2 with HSQLDB under Windows 7 Home Premium.

[PYS]

Hi

The Queries section in our FAQ answers to such questions. In particular, this page describes how to proceed.

[EDIT #1]

Date is a reserved word, you can not use it. Use e.g. InputDate instead.

[EDIT #2]

I watched your screenshots. So I wonder if you have a good look at the FAQ that I have indicated. It states:

• Parameter names cannot include spaces
• Parameters must not be enclosed in square brackets ( [ and ] )
• If you do not use ? one must use :Parameter_Name, so e.g. :EnterYear (note the :)

So:

Regards

[ptoye]

Yes, but see my edited question!

After your comment - doesn’t work - get the same error. And as I understand it the text is pure text, not a variable name, so clashes with reserved words shouldn’t matter.

[ptoye]

I must be going senile. Of course you’re right. I’d also not realised that spaces aren’t allowed in the prompt text, which they are in Access. (Access also has more facilities which don’t seem to be in LO). The FAQ is helpful, but is a bit unclear in parts. I’ll try to clean it up a bit when I know more about it. I also found https://www.youtube.com/watch?v=NcJrodWSIQg very helpful.

[peterwt]

When a Parameter Query is run a pop up will show allowing you to enter a value for the Parameter.

In the Query Design View put in the Criterion for the Date Field :enter (that is colon enter, where enter can be any text and will show in the pop up.

The SQL view will look like:-

SELECT “ID”, “Name”, “Calc” FROM “Table1” WHERE “Name” = :name

[ptoye]

That’s what I thought - but see my edited question with the screenshots.

[ptoye]

See my comment to Pierre-Yves above.