Form Values as Query Criteria

[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.

[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

[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.

[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?

[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

[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.