Page 1 of 1
Form Values as Query Criteria
Posted: Wed May 28, 2008 9:35 pm
by slc193
How do you take a value entered or selected in a field and set it as a criteria for a query? Is there any sort of tool for OO Base like MS Access' Expression Builder to accomplish this? Or is it simply a must to know SQL code in order to work with Base?
TIA.
Re: Form Values as Query Criteria
Posted: Wed May 28, 2008 11:58 pm
by kabing
Can you say more about what it is that you want to do?
Depending on your needs, form based filters might accomplish the same thing for you.
If you really need a query based on a value in a field, then you will need to use a macro written in Star Basic.
kabing
Re: Form Values as Query Criteria
Posted: Fri Jul 18, 2008 2:39 pm
by slc193
I have a form where data about an invoice is entered. The fields present are: Vendor, Invoice#, Date of Purchase, Purchaser, Entered By, Date of Entry, Season, and Total Price.
There is a subform in table view for entering the each line of purchase on the invoice. These fields are: Quantity, Item, Catalog #, Purchase Price, Show, and Department.
I have the query that looks up the data that I want on the report built and it works fine if I manually enter the InvoiceID (which is an autonumber field in the Invoice Table) into the query. I also have the report built and finished.
The issue is: How do I have the value of the InvoiceID field in the query be defined by whichever invoice is currently displayed on the Invoice form?
My dilemma is that I am very much a newbie at BASIC. I have just learned some of Visual Studio 2005, but the commands are different and I have been having trouble finding how to do the simple commands (like Open, which is show.form.* in VB and Close, which is *.close) in OO BASIC.
Re: Form Values as Query Criteria
Posted: Fri Jul 18, 2008 2:50 pm
by slc193
I guess the bigger question would be:
How do you dimension the value currently displayed in a field on a form?
and...
How do you call that value up in a query?
Re: Form Values as Query Criteria
Posted: Fri Jan 16, 2009 10:02 pm
by ugm6hr
I have been struggling with a slightly less complex situation, but would benefit from working towards a complete solution.
You need to add the Form Value as a field in the Query on which the Form is based, and ensure that this field is assigned an Alias (e.g.
Alias). In the Query criteria, just enter anything for now (e.g.
=1).
Then re-edit the Query in SQL mode, and edit the query criteria to change whatever you entered as criteria for the Alias in " quote marks (i.e.
="Alias")
This appears to work in the Query itself. Hopefully, a form based on this query will also work.
My blog re: this issue
http://cardiologylogbook.wordpress.com/ ... date-range
Re: Form Values as Query Criteria
Posted: Fri Jan 16, 2009 10:27 pm
by Villeroy
Download
http://user.services.openoffice.org/en/ ... hp?id=3371
Add a query ...
Code: Select all
SELECT "T"."ID", "T"."Name", "T"."Picture", "T"."Description", "F"."ID"
FROM "Table1" AS "T", "Filter" AS "F"
WHERE ("T"."Name" = "F"."Name") AND ("F"."ID" = 0)
... and build a simple report based on that query.
Open the form, pick a record by picture name from the combo, hit [Refresh], load the report.