Query by form alternative

[jbk]

Hello, newbie here trying to understand queries. I would like to use and alternate to "query by form". I basically does what I would like but I would like to us a more advanced searching system. What I don't like about the query by form is that when the user trys to search by customer name, it must be spelled exactly. What I would like a query to do is search for letters or word contained in there request. Example: If a user types in Miller and Miller, query results or "no records found" and using the query by form user must type in Miller & Miller to get any results. I would like the user to be able to type in "Mil" for example and result of any and all customer with "Mil" in there name would be displayed. I know what I'm asking to do can be done using a custom query. Any examples of a custom query would greatly appreciated.

[Stan Mathews]

Re: Search by form alternative

See the section here on QBF syntax.

[jbk]

Re: Search by form alternative

Thanks Stan for the quick reply. Would you happen to have any examples of your suggestions

[Stan Mathews]

Re: Search by form alternative

The examples are in the documentation.............

$
 contains
 $ software
 the field contains the word "software" anywhere within the field

$ "Mi"

will return records where "Mi" is found in the field chosen on the QBF.

[jbk]

Re: Search by form alternative

Okay.... Just looking for an alternative way of searching records such as a Xdialog box. Would like to have the user press "search" a Xdialog box is opened, user can then select several options such as: Date Range, Customer, City etc. And when using this dialog box, user is not limited to just one choice at a time. I know that the current query by form will allow this with the exception of date range, just wanted a more custom look (dialog box). Thank again...

[Stan Mathews]

Re: Search by form alternative

I know that the current query by form will allow this with the exception of date range,

Not.

..
between
1..7

A..M

4/16/2001..4/16/2002
is between one and seven

is between A and M

occurs between 4/16/2001 and 4/16/2002

[Ted Giles]

Re: Query by form alternative

Here are two ways - see buttons.
One uses an Index I've built, the other is a Standard presentation.
The one using an Index shows matches as you start typing the name.
Explore the buttons/options in the Standard one. They have a number of useful options.

[Ted Giles]

Re: Search by form alternative

You have a post in V10 as well which I have answered. Doesn't matter but it's as well to stick with the version you are working with.

[jbk]

Re: Query by form alternative

Here are two ways - see buttons.
One uses an Index I've built, the other is a Standard presentation.
The one using an Index shows matches as you start typing the name.
Explore the buttons/options in the Standard one. They have a number of useful options.

[Ted Giles]

Re: Query by form alternative

Pardon?

[jbk]

Re: Query by form alternative

Thanks for your response. Is there any way that using your suggestion/exmaples, multiple selection/filters can be applied to narrow the results down even further. Example: I would like to choose a 'customer' in a particular 'city'.

[Ted Giles]

Re: Query by form alternative

Yes indeed there is.
1) A progressive search. Look for examples on the Forum
2) A Tree view. This will, for example, display a list of Cities, with a + to expand and show the customers in those cities. This is not a 2 minute job, but it's quite easy to do.
3) Make the Index bigger, as in, CUSTOMER+". "+City [its a bit crude doing it this way though]
4) Have a browse with all of the customers and cities shown, with a DblClick to take you to the Customer Form. The user can do a Quick Sort on city or you can add a button to do it.

You might want to consider having a separate structure for Customers and Activities, linked via a Cust ID.

Customer Table - Link = Cust_ID
Activity Table Ditto
Invoice Table Ditto

[jbk]

Re: Query by form alternative

Thanks again Ted, attached is my latest... Created an operation and with the help of Query genie I was able to create a dialog box similar to what I was looking for. There's (2) buttons: "Query with Dialog" and "Query with Dialog 2". There exactly the same but the "Query with Dialog 2" contains a date range and has a mind of its own. For some reason it will work sometimes. I would like the user to be able to filter there search selection by city, customer date range etc. Not sure why the date range is throwing things off. The "Query by Dialog" button is looking at an operation called "Search" which I basically just copied "Search2" and deleted condition "date range". Here's my questions:

(1) How do I implement a "working" date range filter
(2) How do I change the name of this dialog box from "Parameters" to "??????"
(3) How do I clear last information that user input in dialog box

Once again thank you in advance...

[jbk]

Re: Query by form alternative

Just an update... Was messing with the sample program and can get "Query by Dialog2" search to work properly if I choose a filter (customer, city etc) AND date range (start & end). I really am looking for the option similar to "Query by Dialog" were user can choose how they want to filter (and/or selection). "Query by Dialog2" forces user to choose date range (start & end).

[Ted Giles]

Re: Query by form alternative

Gonna be tomorrow now. My turn to cook.