Ask Your Question
0

How do I create a parameter query? [closed]

asked 2015-10-26 17:11:20 +0200

ptoye gravatar image

updated 2015-10-31 11:15:55 +0200

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-03-15 13:55:39.256745

2 Answers

Sort by » oldest newest most voted
0

answered 2015-10-28 12:54:34 +0200

peterwt gravatar image

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

edit flag offensive delete link more

Comments

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

ptoye gravatar imageptoye ( 2015-10-31 11:10:50 +0200 )edit

See my comment to Pierre-Yves above.

ptoye gravatar imageptoye ( 2015-11-03 11:24:44 +0200 )edit
1

answered 2015-10-28 16:26:52 +0200

pierre-yves samyn gravatar image

updated 2015-11-02 16:57:59 +0200

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:

image description

Regards

edit flag offensive delete link more

Comments

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 gravatar imageptoye ( 2015-10-31 11:10:49 +0200 )edit

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=NcJro... very helpful.

ptoye gravatar imageptoye ( 2015-11-03 11:23:57 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2015-10-26 17:11:20 +0200

Seen: 3,320 times

Last updated: Nov 02 '15