Entering dates into a query 1

[alvinmcg]

I have a small application and need to run monthly queries/reports on the data. I have created a QBE query and have entered the month beginning and ending dates to collect the data I need for each month.

There must be a way to have Paradox ask, when the query is run, for these two dates and insert into the query. I know I saw someone run one in the past, but can not find any code.

I also anticipate needing code to check the date format to ensure a correct date is entered.

This the current query in SQL:
BEGIN

SELECT DISTINCT D."SPR Number", D."Acceptance Date", D.Description, D."Type of Use", D1."Project Name", D2.Sequence, D2."Completion Type"
FROM "COs.DB" D, "Projects.DB" D1, "Completion Types.DB" D2
WHERE
(D."Acceptance Date" >= '12/01/2003')
AND (D.&quot;Acceptance Date&quot; <= '12/30/2003')
AND (D1.&quot;SPR Number&quot; = D.&quot;SPR Number&quot
AND (D2.&quot;Short Name&quot; = D.&quot;Type of Use&quot
ORDER BY D2.Sequence, D1.&quot;Project Name&quot;, D.&quot;Acceptance Date&quot;, D.Description, D.&quot;Type of Use&quot;, D2.&quot;Completion Type&quot;, D.&quot;SPR Number&quot;

END
I am not a programmer (at least not since the 80's) and appreciate all the help I can get. Our IT department just will not assist. Thank you all for your assistance.

 

[footpad]

alvinmcg,

Yes, there is a way to do it and yes, you have to use ObjectPAL to get there.

Unfortunately, it's not something that can be easily distilled into a short reply.

You need to:

1. Create a form designed to only obtain dates from the user.

2. Create a script that:

a) opens your dates form.

b) waits for the user to either enter valid dates or cancel the process.

c) place the date values entered by the user into variables.

d) dumps the date variables into your query using tilde variables.

e) runs your query.

f) either tells the user that no matches were found or does something with the results, e.g. open a report containing those results, saves them to an Answer table, or whatever it is you normally do with the query results.

Since you're new to ObjectPAL, a detailed walkthrough is going to take some time to prepare. I'll work on that later this evening and post it when I'm finished.

In the mean time, here are some articles from my site that will help you become familiar with the basic tasks involved:

--

http://techtricks.com/paradox/btntutor.php

--

http://techtricks.com/paradox/printone.php

--

http://techtricks.com/paradox/poplists.php

--

http://techtricks.com/paradox/formexit.php

I may end up creating a new tutorial for this, as it involves a lot of choices. I can show you the process using the sample files. Once you've done it sucessfully with the sample files, you should be able to do it with your own tables.

Hope this helps...

-- Lance

 

[member 159550]

Lance,

Can the same thing be done for looking up a client in a database using First Name and Last Name. We have in our customer table lots of Lewis's it would be nice to be able to locate using both fields to go straight to the correct record.

Thanks,

Lewy

 

[footpad]

Lewy,

Sure. Simply create a secondary index on the two fields and then use a setRange against that secondary index. When I've done similar things, I've created a search form with a grid and two edit boxes. As you type values in the edit boxes, the grid updates to reflect the entered values.

Let's see if I can cobble together something quickly using the sample files, though perhaps not to my usual level of detail:

1. Change :WORK: to the sample directory.

2. Copy CONTACTS.DB to CONTACT2.DB.

3. Restructure CONTACT2 and do the following:

3.a. Move Company to the first field position.
3.b. Create a primary key on Company.
3.c. Add a secondary index called FullName that includes Last Name and First Name. Make sure these are case-insensitive.

We now have a table to play with. I chose Contacts because I know the Company values are all unique (and thus a good key) Now, let's create a close to the one I think you're looking for.

1. Create a new form containing CONTACT2.DB in the data model.

2. Drop and bind a table frame to Contact2. Leave room above the table frame for field objects.

3. Place two field objects on the form. Name the first fldLastName and the second fldFirstName.

4. Drop a button to the right of the field objects and add the following code to its pushButton() event:

method pushButton(var eventInfo Event)
var
   tc   TCursor
   uio  UIObject
   str  String
endVar

   tc.attach( Contact2 )

   ; make sure we're using teh right index
   tc.getIndexName( str )
   if str <> &quot;FullName&quot; then
      tc.switchIndex( &quot;FullName&quot; )
   endIf

   ; apply the values entered by the user
   if fldLastName.Value = &quot;&quot; then
      tc.setRange( &quot;&quot; )  ; clear the range
   else

      if fldFirstName.Value = &quot;&quot; then
         tc.setRange( fldLastName.Value,
                      fldLastName.Value + &quot;Z&quot; )
      else
         tc.setRange( fldLastName.Value,
                      fldFirstName.Value,
                      fldFirstName.Value + &quot;Z&quot; )
      endIf
   endIf

   Contact2.resync( tc )

endMethod

Now, run the form and play with different values in either of the field objects. While doing so, keep an eye out for a few things:

-- Entering &quot;B&quot; for the Last Name gives you a list of all the last names starting with B.

-- Leaving both fields blank resets the list.

-- Each time you sucessfully apply a range, the record number indicator in the status bar shows the number of matches found. This does not happen when you use filters.

-- If you save the form at this point, it has the same range applied when you reopen the form later. This can be either a blessing or a curse, especially while you're working on an application. For best results, add code to the form's close event to clear the setRange. (See the pushButton code above for a hint.)

-- If you work with a larger dataset over a network, you'll notice that performance of this approach far exceeds the performance of filters.

This happens for two reasons: Filters have to search the entire database on a record by record basis. Ranges, on the other hand don't need to do this because the table is already ordered correctly. Thus, BDE can jump directly to the matching records. Ranges also only need the values in the secondary index, instead of the entire record.

Both of these improve performance and lead to quicker results.

At this point, it would be relatively trivial to add a second button to grab the key value of the selected record in the table frame (e.g. Customer2.Company.Value) and use that to locate (or qLocate(), which is better) the same record on a different form.

Now, getting this to work depends largely on the design of the tables. Ranges limit you to wildcarding the last field specified in the range. That is, you can wildcard the last name by itself or all first names matching a given last name, but not both...at least not with this implementation.

There are other ways to locate similar names; this particular example simply demonstrates the concept. I didn't carry it too far because a lot depends on how you're using the data in your applications.

In any event, this is a very good example of how Paradox rewards good database design.

Hope this helps...

-- Lance