CREATE QUERY, MODIFY QUERY

These two commands open the Query Designer, for a new or existing query, respectively, sort of.

Usage

CREATE QUERY [ cFileName | ? ]
        [ NOWAIT ] [ SAVE ]
        [ AS nCodePage ]
        [ WINDOW cDefiningWindow ]
        [ IN [ WINDOW ] cContainingWindow |
          IN SCREEN | IN MACDESKTOP ]
MODIFY QUERY [ cFileName | ? ]
        [ NOWAIT ] [ SAVE ]
        [ AS nCodePage ]
        [ WINDOW cDefiningWindow ]
        [ IN [ WINDOW ] cContainingWindow |
          IN SCREEN | IN MACDESKTOP ]

Specifying the ? in CREATE QUERY brings up the Save As dialog, so you can specify a name for the new query. Unlike some of the other CREATE commands, omitting the parameter in CREATE QUERY doesn’t display the Save As dialog—instead you see Query1 (or whatever number you’re up to) and you can name it on the way out. With MODIFY QUERY, either ? or omitting the file name entirely brings up the Open dialog.

For CREATE QUERY, the Query Designer opens with the ADD TABLE dialog in front of it. This lets you add the first table to the query on the way in. (You can skip that step by pressing ESC or clicking Cancel.)

These commands include a bunch of optional clauses (many of them undocumented) generally relevant only in programs. Since you can’t include the Query Designer in a distributed application, issuing these commands in a program isn’t terribly useful, unless you are in one of those unusual installations that has purchased the development version of Visual FoxPro for each desk. Even if you could distribute the Query Designer, it’s not a very good tool for end users. It’s both too restrictive and too low-level. Get FoxFire! or another add-on query creation tool instead.

If you really think you need one of the optional clauses, check out MODIFY COMMAND, which supports them and where they’re actually useful. For more information on using the Query Designer, see “These Are Not Your Father’s Power Tools.”

And what about that “sort of” in the introduction above? Turns out if you CREATE QUERY AnExistingQuery (with SET SAFETY ON), you get asked if you want to overwrite it. If you say no, it opens up just as if you’d issued MODIFY QUERY. If SAFETY is OFF, CREATE QUERY AnExistingQuery just opens it up. Stupidly, in either case, the Choose Table dialog appears as if it were a new query. If you MODIFY QUERY ANewQuery, it behaves like CREATE QUERY. Frankly, we like this behavior because it means we can just use MODIFY QUERY all the time and forget that CREATE QUERY exists.

A typical interactive session for us involves lots of typing from the command window, and we like just cursoring up a few lines and hitting return to repeat a previously entered command. We expect that FoxPro will “do what we mean, not what we say,” and past versions have allowed us this luxury. CREATE FORM myNewForm means “create a new one” the first time we issue it, but it means “drag up the old one” if we say it again. If we really mean to create a new form, then we’ll see that there’s already one by the name we chose, and we’ll close it and use a new name. We can’t remember the last time we chose to overwrite an existing form. This is the behavior in the current product for the CREATE FORM command, and we expect the same for the CREATE QUERY.

It turns out we have different habits here. One of us habitually uses MODIFY for these tasks while the other prefers CREATE. If you want to use CREATE, you’ll want SAFETY OFF, so you don’t have to keep telling VFP not to overwrite existing queries (or forms or whatever).

Beware. If you issue MODIFY QUERY for an existing QPR and the Query Designer can’t figure out how to parse the query (most likely because you changed it by hand), the Query Designer opens with less than your entire query represented. What you actually see depends on the query. The QD parses what it can and plonks those items in the right places, but anything it doesn’t understand (and even some things that it should understand) gets dumped. The View Designer, which is really just another face for the Query Designer, behaves pretty much the same way.

Example

CREATE QUERY MyNewQuery

See Also

Create SQL View, Modify Command, Modify View, Select-SQL