Creating a Query through a Form

[weathergirl]

I'm trying to create a search of a table with a form. I'm using the form for easy entering of fields instead of having to go into the query and change the criteria. I'm trying to query different field such as date, location, event, etc. What I want to happen is type in the search parameters they are looking for, hit a button on the form, and a table would be produced with the information fitting their criteria.

I've tried using tilde variables but they are not pulling the information from the table. If any one could give me ideas on the script to run this it would be much appreciated.

Thanks,
Weather Girl

 

[Langley]

What you describe is also the way I allow users to create ad hoc reports on all my applications. I have no problem, so I have to assume that you are overlooking something. Post your button code so we can see what you've got.

Typically I just assign variables from the form (i.e. variable = field.value), setting them to null if the user omits them. Then I just tilde them into my query script.

Also, have you tried manually querying the table with the same criteria to make sure your data fits the query model?


Mac

"Do not delve too deeply in the arts of your enemy and so become ensnared by them"

langley_mckelvy@cd4.co.harris.tx.us

 

[footpad]

Weather Girl,

If you've got a handle on using tilde variables, then all you really need to worry about is assigning the values into those variables. Mac's comment about doing it directly from the form is well-taken, for you avoid the overhead and hassle of dealing with your criteria table.

Here's an example taken from something I did a while back that shows a few different things:

method pushButton(var eventInfo Event)
; ----------------------------------------------------
; This routine verifies that the user has selected a 
; department and runs the query this form automates.
; ----------------------------------------------------
var
   loRetval  Logical ; Flag - success or failure.
   strValue  String  ; Holds user's selected department
   qryPrint  Query   ; Query using user's selection.
   tcAnswer  TCursor ; Results, for performance and eval.
endVar

const
   ERRTITLE = "Can't Print Roster"  ; title of error dialogs.
endConst

   setMouseShape( mouseWait )

   doDefault

   ; First, initialize the flag variables.
   loRetval = TRUE  ; Assume we'll succeed.

   strValue = fldDeptList.value
   If strValue.match( &quot;<..>&quot; ) then
      strValue = &quot;..&quot;
   endIf

   ; Now, define the query and try to run it.

   qryPrint = Query

      :DATA:EMPLOYEE | Department |
               Check | ~strValue  |

   endQuery


   Message( &quot;Selecting employees...&quot; )
   If not qryPrint.executeQBE( tcAnswer ) then

      ; The query could not be run, display the error
      ; and save the query in :PRIV: for debugging.

      errorShow( ERRTITLE,
                 &quot;Reason: The query failed to run; &quot; + 
                 &quot;see details...&quot; )
      qryPrint.writeQBE( &quot;:PRIV:BADQUERY.TXT&quot; )
      loRetval = FALSE

   else

      ; The query ran, so see if we got any records.

      If tcAnswer.nRecords() = 0 then  ; no matches

         setMouseShape( mouseArrow )
         msgStop( ERRTITLE, 
                  &quot;No matches were found for your &quot; +
                  &quot;conditions.  Please try different &quot; +
                  &quot;ones.&quot; )
         loRetval = FALSE
      else
         ; create the ANSWER table
         tcAnswer.instantiateView( &quot;:PRIV:ANSWER.DB&quot; )
      endIf
   endIf

   ; Save the user's criteria to an environment 
   ; variable so it appears on the report.
   writeEnvironmentString( &quot;RPTTITLE&quot;, fldDeptList.Value )

   ; Make sure the TCursor gets closed
   If tcAnswer.isAssigned() then
      tcAnswer.close()
   endIf

   ; If the process succeeded, then close the form.
   ; Otherwise, stick around so the user can try again.

   setMouseShape( mouseArrow )
   if loRetval then
      formReturn( True )
   endIf

endMethod

In this case, the form contained a listbox containing a list of the departments in the lookup table and an extra item as the default (&quot;<All Departments>&quot. Near the beginning of the code, we see if the listbox is set to the all departments value and, if so, put a wildercard into the variable (though it probably should have been blank) or we put the value selected by the user.

Next, we defined the query and included the user's selection using a traditional tilde variable. (Note the shortcut for automatically checking all fields.)

Now, we try to run the query, using a variation that stores the results in memory, rather than an ANSWER table. This is a significant performance benefit, though you'll note that we still pay that price by actually creating the ANSWER table later.

However, before we do, we check to see if there were any matches to the underlying query. (Perhaps the department called HLSEC hasn't actually hired anyone yet.) If no matches are found, we display a note to the user and let them try again.

Note, though, that there's some code to make certain that the query ran. If there's a problem, you'll notice we do two things: 1) We save the query to a separate .TXT file so it can be reviewed for problems and 2) display the contents of the error stack to the user, so they get an idea of what Paradox thinks the problem is.

Depending on the experience of your users, you may need to override that, but it's a good place to start.

Next, you'll notice that we write the actual criteria value to an environment variable. This is done for the sake of the actual report, which contains a calculated field to read the environment variable and update the the report's page header accordingly. (That way, you'll be able to tell one report from others accidentally left on the top of the laser printer.

At this point, we do the standard clean-up process, which should always include a formal closing of your TCursors. While it's not as bad of a problem in recent versions of Paradox, older ones would periodically leave TCursors in memory, even after the variables pointing to them were no longer in scope. Thus, you'd get memory leaks and odd-little &quot;Record Already Locked&quot; errors. So this is a good safety strategy to use.

Finally, you'll note that we do not automatically do a formReturn( TRUE ) unless the query is successfully run. That way, the user can modify the query conditions until they actually get results from their efforts.

There are certainly many different ways to do this, but I hope it helps a little...

-- Lance

 

[weathergirl]

Here's the script that I have for the form. The query works if I don't use the form.

method pushButton(var eventInfo Event)
var
sName String
qryWarningLogQuery Query
tv TableView
endVar

sName = Start_Day.value
sName = End_Day.value
sName = Month.value
sName = Year.value
sName = County.value
sName = Storm_Type.value
sName = Event_Type.value
sName = Begin_Time.value
sName = End_Time.value
sName = Verified.value
sName = Not_Verified.value
sName = Missed.value


qryWarningLogQuery = Query

ANSWER: WarningLogQuery.DB

SORT: WarningLog.DB->&quot;Day&quot;, WarningLog.DB->&quot;Month&quot;, WarningLog.DB->&quot;Year&quot;,
WarningLog.DB->&quot;County Name&quot;, WarningLog.DB->&quot;Type&quot;, WarningLog.DB->&quot;Issue Time&quot;,
WarningLog.DB->&quot;End/Cancel Time&quot;, WarningLog.DB->&quot;Verified&quot;, WarningLog.DB->&quot;Not Verified&quot;,
WarningLog.DB->&quot;Missed&quot;, WarningLog.DB->&quot;Event Type&quot;, WarningLog.DB->&quot;Time of Event&quot;,
WarningLog.DB->&quot;Warning #&quot;, WarningLog.DB->&quot;Event&quot;, WarningLog.DB->&quot;Location&quot;


WarningLog.DB | Warning # | Type | County Name | Month | Day | Year |
| Check | Check = ~sName | Check = ~sName | Check ~sName | Check ~sName | Check ~sName |

WarningLog.DB | Issue Time | End/Cancel Time | Verified | Not Verified |
| Check | Check | Check ~sName | Check ~sName|

WarningLog.DB | Missed | Event Type | Event | Location | Time of Event |
| Check ~sName | Check ~sName | Check ~sName | Check | Check |

EndQuery

executeQBE(qryWarningLogQuery)
tv.open(&quot;RIV:ANWSERB&quot
endMethod

 

[footpad]

Weather Girl,

I suspect that your problem lies in the fact that sName only contains Missed.value at the end of your initialization. When you assign a new value to a variable, it overwrites any previous values.

I think you'll need twelve different query variables, with one for each field on your form.

Hope this helps...

-- Lance

 

[Langley]

Lance is right, but also remember to change the variables in the query itself to reflect the new names.

Now a couple of other things:

1) I wouldn't bother to sort the table before the query, since your results will be in answer.db

2) Drop the equals = signs in your query (i.e. check = ~sname). It is not necessary since it is implied, and it will seriously mess up the query when a value happens to be null.




Mac

&quot;Do not delve too deeply in the arts of your enemy and so become ensnared by them&quot;

langley_mckelvy@cd4.co.harris.tx.us

 

[weathergirl]

I added more variables and it's still not giving me the results I want. It opens up the ANSWER table all the data in it. Is there maybe something I need to do with defining the fields on the form?

Also on the time and date I'm trying to do a start date/time and an end date/time. Will have be able to do that by querying on field in the table?

method pushButton(var eventInfo Event)
var
sName String
sName1 String
sName2 String
sName3 STring
sName4 String
sName5 String
sName6 String
sName7 String
sName8 String
sName9 String
sName10 String
sName11 String
qryWarningLogQuery Query
tv TableView
endVar

sName = Start_Day.value
sName1 = End_Day.value
sName2 = Month.value
sName3 = Year.value
sName4 = County.value
sName5 = Storm_Type.value
sName6 = Event_Type.value
sName7 = Begin_Time.value
sName8 = End_Time.value
sName9 = Verified.value
sName10 = Not_Verified.value
sName11 = Missed.value


qryWarningLogQuery = Query

Query

ANSWER: RIV:ANSWER.DB


WarningLog.DB | Warning # | Type | County Name | Month | Day | Year |
| Check | Check ~sName5 | Check ~sName4 | Check ~sName2 | Check ~sName ~sName1 | Check ~sName3 |

WarningLog.DB | Issue Time | End/Cancel Time | Verified | Not Verified |
| Check | Check | Check ~sName9 | Check ~sName10 |

WarningLog.DB | Missed | Event Type | Event | Location | Time of Event |
| Check ~sName11 | Check ~sName6 | Check | Check | Check ~sName7 ~sName8 |

EndQuery

executeQBE(qryWarningLogQuery)
tv.open(&quot;RIV:ANSWER.DB&quot
endMethod

Thanks,
Weather Girl

 

[Langley]

I want you to do a couple of things to verify that what you think you should get is actually what you should get based on your query.

1) Do a manual query using the same values you are plugging into your variables. If this produces the results you want, then proceed to step 2 to find out which variable is being mistranslated. If it does not produce the results you want, then the fault is in the structure of the query, not the variable assignment.

2) Insert the following in your code (before the query):

view("sname = "+sname)
view("sname1 = "+sname1)
;etc... use cut and paste and this will go fast

return ; this will stop execution

this will allow to see what values are being inserted into your variables. Once you are satisfied that the variable values are right, remove this code.


Mac

&quot;Do not delve too deeply in the arts of your enemy and so become ensnared by them&quot;

langley_mckelvy@cd4.co.harris.tx.us

 

[weathergirl]

Mac,

I ran the query manually and it works fine. Then I put the code you gave me into the script. When I ran the query from the form a box appeared asking if the value was ok for each variable. I would hit okay on all of them and it worked. Then I took the code out and it didn't work. What should I do now?

Thanks,
Weather Girl

 

[weathergirl]

I found my problem. Now the query is running fine from the form.

But I do have one problem/question I'm still trying to figure out with it. I have the one field for entering the days into the table. But now in the query on the form I want to be able to search a beginning day and end day. I tried assigning the date to two variables but that doesn't work. Is there a way I can do this?

Thanks,
Weather Girl

 

[footpad]

Weather Girl,

It depends on ehich field you're searching in this query. It looks like you're looking at Time of Event, is this correct? If so and this is a date or date/time field, then your criteria should be:

| Time of Event                | 
| Check >= ~sName7, <= ~sName8 |

However, that will only work when you always have two values in sName7 and sName8. If you want to be able to specify a greater than a certain date, a less than a certain date, and a range of dates (e.g. you may have values in all three, then you should probably do something like this:

if sname7 <> &quot;&quot; then
   sname7 = &quot;> &quot; + sname7
endIf

if sname8 <> &quot;&quot; then
   sname8 = &quot;< &quot; + sname8
endIf

if ( sname7 <> &quot;&quot; ) and
   ( sname8 <> &quot;&quot; ) then
   sname7 = sname7 + &quot;, &quot;
endIf

In this case, then you'll want to specify that field's criteria as:

| Time of Event         | 
| Check ~sName7 ~sName8 |

Now, if you're trying to search the separate month, day, and year fields, it gets a lot more tedious, in that case, you'll need to promt the user for a date value, break it into its component pieces, and then define the query so that it uses the two month fields in a range (like was done above), the two day fields, and (of course) the two year fields.

Hope this helps...

-- Lance

 

[weathergirl]

Lance,

I've tried both of your suggestions. Neither of them seem to work. I'm trying to search for a range of dates or time. Example: From the 1 to the 15. I only searching like this with date and time. When I tried using both the scripts, it would put the same number in both the starting and ending date. It does the same thing for the time. When I try to query the time with other parameters, the query doesn't work at all.

Should my fields be defined or undefined? It seems to work both ways. I didn't know if that would affect the date and time fields.

Thanks for your help,
Weather Girl

 

[Langley]

Definitely undefined. You would be looking at an actual record if they were defined. I think it would help us to help you if we could see a sample of the data that's in the fields you are trying to search. I have a feeling it's not what we might think it is.

Mac

&quot;Do not delve too deeply in the arts of your enemy and so become ensnared by them&quot;

langley_mckelvy@cd4.co.harris.tx.us

 

[weathergirl]

Here's some sample data for what I'm trying to query.

Day: 1
Month: 6
Year: 2002
County: Yellowstone
Type: Severe
Event Type: Wind
Time of Event: 3:15 PM
Verified: Y
Not Verified: N
Missed: N

All the records I'm trying to search are similar to this. I'm just trying to do the date range and time range with there being only one field for each. Hope this helps.

Weather Girl

 

[Langley]

A couple of questions. Are the day/month/year fields numeric or alpha? Is the time field an alpha or actual time field?

I personally think you would be better served by either converting completely to a real date field, or at least adding one for searches; but, I believe we can sort this out now.

Let's say you want to search for all WIND events that ocurred between January 1 2000 and April 15 2000.

(pseudocode)

|Day               | Month            | Year       |Event|
|check >= 1, <= 15 | check >= 1, <= 4 | check 2000 |WIND |

You can't really do that under your current set up. You could search for everything that happened between January 1 and April 30. The problem is your day field. If you span months then the day field has no accurate meaning in the query. In my example about WIND events above, you would end up with all WIND events for the following ranges: Jan 1-15, Feb 1-15, Mar 1-15, and Apr 1-15. Nothing that happened after the 15th of any given month would show up in the result.

Mac
Mac

&quot;Do not delve too deeply in the arts of your enemy and so become ensnared by them&quot;

langley_mckelvy@cd4.co.harris.tx.us

 

[weathergirl]

Mac,

My time field is an actual time field. All the day, month and year fields are numeric.

I tried using some code like you gave in your example. It doesn't work. When I put in the >,<,and = signs it bogs up everything and none of the field will work.

Here's my code again. I don't know what's going wrong.

method pushButton(var eventInfo Event)
var
sName String
sName1 String
sName2 String
sName3 STring
sName4 String
sName5 String
sName6 String
sName7 String
sName8 String
sName9 String
sName10 String
sName11 String
qryWarningLogQuery Query
tv TableView
endVar

sName = Start_Day.value
sName1 = End_Day.value
sName2 = Month.value
sName3 = Year.value
sName4 = County.value
sName5 = Storm_Type.value
sName6 = Event_Type.value
sName7 = Begin_Time.value
sName8 = End_Time.value
sName9 = Verified.value
sName10 = Not_Verified.value
sName11 = Missed.value


qryWarningLogQuery = Query


ANSWER: RIV:ANSWER.DB


WarningLog.DB | Warning # | Type | County Area | County Name | Month | Day | Year |
| Check | Check ~sName5 | Check | Check ~sName4 | Check ~sName2 | Check ~sName ~sName1 | Check ~sName3 |

WarningLog.DB | Issue Time | End/Cancel Time | Verified | Not Verified |
| Check | Check | Check ~sName9 | Check ~sName10 |

WarningLog.DB | Missed | Event Type | Event | Location | Time of Event |
| Check ~sName11 | Check ~sName6 | Check | Check | Check ~sName7 ~sName8 |


EndQuery

if sname7 <> &quot;&quot; then
sname7 = &quot;> &quot; + sname7
endIf

if sname8 <> &quot;&quot; then
sname8 = &quot;< &quot; + sname8
endIf

if ( sname7 <> &quot;&quot; ) and
( sname8 <> &quot;&quot; ) then
sname7 = sname7 + &quot;, &quot;
endIf

executeQBE(qryWarningLogQuery)
tv.open(&quot;RIV:ANSWER.DB&quot
endMethod

Thanks,
Weather Girl

 

[Langley]

if sname7 <> &quot;&quot; then
   sname7 = &quot;> &quot; + sname7
endIf

if sname8 <> &quot;&quot; then
   sname8 = &quot;< &quot; + sname8
endIf

if ( sname7 <> &quot;&quot; ) and
   ( sname8 <> &quot;&quot; ) then
   sname7 = sname7 + &quot;, &quot;
endIf

The above code needs to be above the query statement and right below the variable assignment - not below the query statement.

Mac

&quot;Do not delve too deeply in the arts of your enemy and so become ensnared by them&quot;

langley_mckelvy@cd4.co.harris.tx.us

 

[weathergirl]

Mac,

Thanks for you help. It works now except for the time. I insert the same code,

if sname7 <> &quot;&quot; then
sname7 = &quot;> &quot; + sname7
endIf

if sname8 <> &quot;&quot; then
sname8 = &quot;< &quot; + sname8
endIf

if ( sname7 <> &quot;&quot; ) and
( sname8 <> &quot;&quot; ) then
sname7 = sname7 + &quot;, &quot;
endIf

for the date with the different variables and that works but the time doesn't. Do I need to do anything with the format is the ANWSER table?

Weather Girl

 

[Langley]

Time values are funny and you may have to play around with how you stick them into your variable. It may be necessary to use the formatStringAsTime() function to test your users syntax, and then plug the values in as TIME variables rather than string variables. I have never queried against a time field (oddly enough), though I have done a number of time calculations.

Mac

&quot;Do not delve too deeply in the arts of your enemy and so become ensnared by them&quot;

langley_mckelvy@cd4.co.harris.tx.us

 

[weathergirl]

Mac,
I think what you've described sounds like it might work. I've tried inserting a formatStringAsTime() but nothing happens. Where should I insert the code and what esle to do I need to do with the string variables?

Thanks,
Weather Girl