dynamic queries

[alvinmcg]

I have some queries which are based on the prior month. How can I setup my query to ask me to enter data or can this be automated to select the prior calendar month?

alvinmcg

 

[almir]

You can try something like this:

Var
StartDate,
EndDate, Date
EndVar

StartDate.view("Enter start date:" prompt user to enter criterion for start date
EndDate.view("Enter end date:" prompt user to enter criterion for end date

:ALIAS_NAME:TABLE_NAME.DB | StartDate | EndDate |
| >=~StartDate,<=~EndDate |; passes values to the fields in query

EndQuery

Regards,

Almir

 

[bigandfat]

Here is some code I came up with that I attached to a pushbutton to run a query of last months data.
Note a little funny business to get the start of a day at 7:00 am instead of 12:01 am with the time however you should get the idea from the date handling. The basic idea came from The DB community web site.

I hope this helps.
Big D

Var
d0 Date
myQBE query
StartOfMonth Number
LastMonth Number
DaysLastMonth Number
Start Number
StartofLastMonth Number
FormVar Form
endVar

method pushButton(var eventInfo Event)
d0=today()
StartOfMonth=day(d0)-1.29166666-1
LastMonth=month(d0)-1
Start=StartOfMonth
Switch
case LastMonth=1aysLastMonth=31
case LastMonth=2aysLastMonth=28
case LastMonth=3aysLastMonth=31
case LastMonth=4aysLastMonth=30
case LastMonth=5aysLastMonth=31
case LastMonth=6aysLastMonth=30
case LastMonth=7aysLastMonth=31
case LastMonth=8aysLastMonth=30
case LastMonth=9aysLastMonth=30
case LastMonth=10aysLastMonth=31
case LastMonth=11aysLastMonth=30
case LastMonth=12aysLastMonth=31
case LastMonth=0aysLastMonth=31
endswitch

StartOfLastMonth=day(d0)-1.29166666

myQBE=Query
&quot;M6ReelReportsM6ReelReports&quot; |DATE|GRADE NUMBER| RUN TIME|ELAPSED TIME| PRODUCTION TONS|REEL SPEED|REEL LENGTH|AVERAGE SHEET WIDTH|RDWT CD SPREAD|RCAL CD SPREAD|TOTAL BREAKS|TOTAL BREAK TIME|
|Check>TODAY-(~StartofMonth+~DaysLastMonth+1), <TODAY-(~StartOfMonth+1) | Check| Check|Check|Check|Check|Check|Check|Check|Check|Check|Check|



endquery

if not executeQBE(myQBE) then errorShow()
endif
FormVar.Open(&quot;:Global_Forms_800_X_600M6ProductionLastMonth.fdl&quot
endMethod

 

[footpad]

bigandfat,

You know, I think there might be a better way to return the number of days in the previous month. While I'm sure your code currently works, it seems, well, a bit imprecise.

Here's how I might approach that problem:

method run(var eventInfo Event)
var
   dt    Date
   siMM,
   siYY,
   siDD  SmallInt
endVar

   ; Get First Day of this month
   dt = today()
   siMM = dt.month()
   siYY = dt.year()
   dt = Date( string( siMM ) + "/01/" +
              string( siYY ) )

   ; Go to last day of last month and then calc days
   dt = dt - 1
   siMonthDays = dt.DaysInMonth()

   ; Show Results
   msgInfo( "FYI", "Last month ended on " + String( dt ) +
                   " and had " + String( siMonthDays ) + " days." )


endMethod

It's looks at the problem a bit differently, but should return an accurate count of days. You can certainly extend this in any number of ways.

For example, you could return the current day in the previous month with this:

method run(var eventInfo Event)
var
   dt    Date
   siMM,
   siYY,
   siDD  SmallInt
endVar

   ; Get First Day of this month
   dt = today()
   siMM = dt.month()
   siYY = dt.year()
   siDD = dt.day()
   dt = Date( string( siMM ) + "/01/" +
              string( siYY ) )

   ; Calculate the same day in the previous month
   ; by returning the last day of the previous month
   ; and then using that to determine the month and
   ; year needed for the final date.

   dt = dt - 1
   siMM = dt.month()
   siYY = dt.year()
   dt = Date( string( siMM ) + "/" +
              string( siDD ) + "/" + 
              string( siYY ) )

   siMonthDays = dt.DaysInMonth()

   ; Show Results
   msgInfo( "FYI", "Last month was " + String( dt ) +
                   " and had " + String( siMonthDays ) + " days." )


endMethod

As usual, Paradox provideds different ways to do things and it sometimes helps to know these different approaches, so you can choose the one appropriate for your current situation.

Hope this helps...

-- Lance