[Solved] User-entered query criteria

Creating tables and queries
Post Reply
DesF
Posts: 4
Joined: Tue Feb 12, 2008 3:15 pm

[Solved] User-entered query criteria

Post by DesF »

OOo 2.3.1, WinXP Pro SP2.

I am working through textbook database exercises as a learning tool. The exercises are written for MS Access rather than OOo Base.

It involves creating a query to check for available hotel rooms within a booking database system.
The following criterias:
=[Enter Room Occupany]
>=[Enter Start Date] And <=[Enter End Date]

for respective Room Occupancy and Booking Date fields would be used by Access to request information from the user (using the prompts in the square brackets).
The user reponses are used to obtain and display the result of the query.

I need to know what is the format for an equivalent user-entered criteria using OOo Base? I have been unable to find this information through on-line tutorials and user guides.

Best regards,
Des.
Last edited by DesF on Fri Mar 07, 2008 7:07 pm, edited 1 time in total.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: User-entered query criteria

Post by Villeroy »

Base help on 'queries;parameter queries (Base)' wrote: You must place the variable between square brackets (=[x]) to create a query with variable parameters. Alternatively, you can use an equal sign followed by a colon (=:x).
Mmmh, I used to use the second syntax (=:x), but your above mentioned expression should work as well. Try the other syntax, try with double-quotes =["Enter Room Occupany"] or avoiding spaces =[Enter_Room_Occupany].
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
DesF
Posts: 4
Joined: Tue Feb 12, 2008 3:15 pm

Re: User-entered query criteria

Post by DesF »

Hello Villeroy & all,

After trying a few of the suggested criteria formats the following seemed to work as required:
= :Room_Occupancy
>= :Booking_Date
<= :Booking_Date1
query parameter.png
Thanks for the pointer to the correct Base help sub-topic.

Regards,
Des.
kschellenberg
Posts: 1
Joined: Thu Mar 27, 2008 5:22 am

Re: [Solved] User-entered query criteria

Post by kschellenberg »

I'm not sure I understood how you entered that criteria. Was there an AND between each of the parameters? Were they on separate lines (on the ORS)?

Never mind. I just figured out what you did. You just added multiple copies of the DATE field to act as the 'AND' in Access.
zeddock
Posts: 1
Joined: Sat Nov 29, 2008 10:55 pm

Re: [Solved] User-entered query criteria

Post by zeddock »

In the past I have used MS-Access query to bring up a question dialog for information inputs. Here was one:
Code:
Like "*" & [enter part of the description] & "*"


So in OpenOffice BASE, how do I accomplish the same?

Using
Code:
'LIKE :Enter_part_of_the_description

does not get it as wildcard do not seem to be available in this situation.

Thanx!
zeddock
OOo 3.0.X on Ubuntu 8.x + MS XP
djskafish
Posts: 11
Joined: Tue Dec 01, 2009 11:02 pm

Re: [Solved] User-entered query criteria

Post by djskafish »

I tried the same criterion for a query in my database. I entered the date field twice, the first column with ">= :Start_Date", the second column with "<= :End_Date". I get the prompt, and I enter 15/11/09 as the start date, which then automatically changes to #15/11/09#, and then 30/11 as the end date (similarly, #30/11/09#). Now, even though I have data points for the 15th, the 19th, the 27th and the 30th, I get an error that tells me there is no data.

Strangely, when I invert the dates (which should return a logical error, there are no actual dates in the range I am entering [>=30/11, <=15/11]), I get a blank query with no errors.
Windows XP Professional en Español, OOo 3.1
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: [Solved] User-entered query criteria

Post by Sliderule »

The dates entered . . . must include Month Day and Year.

You could enter the date in the format: YYYY-MM-DD or in another format, for example, MM/DD/YY or DD/MM/YY or MM/DD/YYYY or DD/MM/YYYY or MM-DD-YY or DD-MM-YY or MM-DD-YYYY or DD-MM-YYYY. In the last eight examples, Base will, depending on your 'local settings' know how to manipulate the entered values to turn it into a date the database will recognise ( that is, in database format of YYYY-MM-DD ).

Just an additional note, since OpenOffice 3.1 . . . you COULD enter the 'date range' on one line in the Criteria line, by using the BETWEEN clause.

For example, you could enter on the Criterion line:
  1. BETWEEN :From_Date AND :To_Date
  2. BETWEEN :Desde_Fecha AND :Hasta_Fecha
Anything after the colon ( : ) that is ONE WORD ( without spaces ) will be 'presented' to the user.

Sliderule
Post Reply