Programmable Queries

[bobsmallwood]

Is there a way to modify a query's selection criteria through a form to avoid having to go into the query's design mode to do so?

 

[jlockley]

I Keep trying to do this. There's a faq on it at

http://www.thedbcommunity.com/faq/24.htm

with a downloadable example. I can't get the example to work, though. Please don't take this as an answer. I too am looking for more information on this.

 

[JoeNg]

Let start with something simple.

Table:
Order.db
Order #
Order Date
Order Qty
Order Price
Order by

Let say you want to find orders with different date range.
Your query would look like this:
Q=Query

Order.db | Order # | Order Date | Order Qty | Order Price | Order by |
| Check | Check ~sDateRange | Check | Check | Check |
EndQuery

Now, I want to able to extract records by different date range.

In the form, I would create 2 unbound fields and name them, fldBeginDate and fldEndDate.
*Unbound fields are fields that do not connect to a field in a table.
You could also define the format property of this field to a date format.

Create a button, in the Pushbutton method, add these codes:

Var
sBeginDate String
sEndDate String
sDateRange String
EndVar

sBeginDate = fldBeginDate
sEndDate = fldEndDate

If sBeginDate=Blank() or sEndDate=Blank() Then
MsgStop(“Error”,”Missing begin and/or end date”)
Return
EndIf

lError = False
Try
DateVal(sBeginDate)
OnFail
LError =true
EndTry

If lError Then
MsgStop(“Error”,”Invalid Begin Date”)
Return
EndIf

lError = False
Try
DateVal(sEndDate)
OnFail
LError =true
EndTry

If lError Then
MsgStop(“Error”,”Invalid Begin Date”)
Return
EndIf


If DateVal(sEnddate) < DateVal(sBeginDate) Then
MsgStop(“Error”,”End date is before begin date.”)
Return
EndIf

sBeginDate = Format(“DM2,DD2,DY3”,DateVal(sBeginDate)) ;Convert to mm/dd/yyyy format
sEndDate = Format(“DM2,DD2,DY3”,DateVal(sEndDate)) ;Convert to mm/dd/yyyy format

sDateRange = “>=”+sBeginDate+”,<=”+sEndDate ;Build date range

Q=Query

Order.db | Order # | Order Date | Order Qty | Order Price | Order by |
| Check | Check ~sDateRange | Check | Check | Check |
EndQuery
If Not Q.Execute() Then
MsgStop(“Error”,”Cannot get order data.”)
ErrorShwo()
EndIf

That’s it. Enter the dates and you will be able to get data for different date range.