month to date query

[bigandfat]

Does anyone have code to calculate month to date? TODAY is fine but a MONTH & YEAR function would have been nice.
Thanks in advance.

 

[NotAllThere]

This is October and month(today()) evaluates to 10. What more do you need!

 

[footpad]

bigandfat,

I presume you mean a QBE query? Can't be done. However, as the following code sample shows, you can do this using a SQL query:

method pushButton(var eventInfo Event)
; ---------------------------------------------------------------
; This routine verifies that the user has selected a month and
; and then runs the report query.
; ---------------------------------------------------------------
var
	loRetval  Logical   ; Flag indicating success or failure.
	siQMonth  SmallInt  ; Holds the month the user wants to print.
	sqlPrint  SQL       ; Query using the user's selection.
	tcAnswer  TCursor   ; Holds results, for performance and eval.
	dbTables  Database  ; Location of the tables for the query.
endVar

const
	ERRTITLE = "Can't Print Birthday List"  ; error dialog title.
endConst

	; Let the user know something is happening and define vars.
	setMouseShape( mouseWait )
	doDefault
	loRetval = TRUE  ; Assume we'll succeed.
	siQMonth = fldMonthList.theList.List.Selection - 1

	; Next, point the database variable to the DATA alias.
	dbTables.open( ":DATA:" )

	; Now, see if the user chose the entire year, if so,
	; we'll use a different SQL statement to create answer
	; than we'd use for a specific month.

	If siQMonth = 0 then
		sqlPrint = SQL

			Select Distinct
				e."LastName", e."FirstName",
				e."Department", e."BirthDate",
				extract( Month from e."BirthDate" ),
				extract( Day from e."BirthDate" )
			from
				":DATA:EMPLOYEE" e

		EndSQL
	else
		sqlPrint = SQL

			Select distinct
				e."LastName", e."FirstName",
				e."Department", e."BirthDate",
				extract( Month from e."BirthDate" ),
				extract( Day from e."BirthDate" )
			from
				":DATA:EMPLOYEE" e
			where
				( extract( Month from e."BirthDate" ) = ~siQMonth )

		endSQL
	endIf

	errorTrapOnWarnings( No )
	Message( "Selecting employees..." )
	If not sqlPrint.executeSQL( dbTables, tcAnswer ) then

		; The query could not be run, so display the error to the
		; user and save the query in :PRIV: for support purposes.

		errorShow( ERRTITLE,
					  "Reason: The query failed; see details..." )
		sqlPrint.writeSQL( ":PRIV:BADQUERY.TXT" )
		loRetval = FALSE

	else

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

		If tcAnswer.nRecords() = 0 then  ; no records returned

			setMouseShape( mouseArrow )
			msgStop( ERRTITLE, "No matches were found for your " +
									 "criteria.  Please try a using a" + 
									 "different selection." )
			loRetval = FALSE
		else
			; create the ANSWER table
			tcAnswer.instantiateView( ":PRIV:ANSWER.DB" )

		endIf
	endIf

	; Save the criteria to an environment variable so the report 
	; can print it.
	writeEnvironmentString( "RPTTITLE", fldMonthList.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

Specifically, note the use of the EXTRACT function in the SQL statement assignment.

Hope this helps...

-- Lance

 

[warrenw]

Var
sVar,eVar Date
m,y SmallInt
q query
endVar

m = month(today())
y = year(today())
sVar = date(m,1,y)
eVar = today()

q = Query
ANSWER: RIV:ANSWER.DB

YourTable.DB | InvDate | Amount |
| Check >=~sVar,<=~eVar | Check |

EndQuery

if NOT executeQBE(q) then
errorshow()
endIf


Modify for your own query and put it behind a pushbutton. Should give you month to date.

 

[bigandfat]

If anyone is interested here is what I did:

method pushButton(var eventInfo Event)
d0=today()
StartOfMonth=day(d0)-1.29166666
Start=StartOfMonth



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| Check| Check|Check|Check|Check|Check|Check|Check|Check|Check|Check|

endquery

if not executeQBE(myQBE) then errorShow()
endif
FormVar.Open(&quot;:GlobalForms:Carl's Page.fsl&quot
endMethod

d0,StartOfMonth are Date variables
Start is a Date Time ( Timestamp )

It seems you can interchange date / timestamp functions but not on the same line of code. The date will revert to mindnight normal start of day, but papermills start of day is 7:00 am hence .2916666 of a day
Start of month is the number of days so far this month ( date format)
start is the same in timestamp format
I subtract the time which has passed so far this month from TODAY() to get the month to date factor.

Thanks to all those who helped me out