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.
Form Values as Query Criteria
Re: Form Values as Query Criteria
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
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
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
Re: Form Values as Query Criteria
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.
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
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?
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
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
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
Last edited by ugm6hr on Tue Feb 17, 2009 9:33 am, edited 1 time in total.
Re: Form Values as Query Criteria
Download http://user.services.openoffice.org/en/ ... hp?id=3371
Add a query ...
... 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.
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)
Open the form, pick a record by picture name from the combo, hit [Refresh], load the report.
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice