Luc, you are welcome. I hope it is of some use to you, whether for learning or for actual application.
I have taken that and extended it for a very narrow/vertical function, namely to calculate distance and intensity/pace adjustments for Track/Cross Country training. I pass several inputs to that extended stored procedure (data from tables or variables or expressions from either||both) and it returns a formatted HH:MM:SS target pace for a specific athlete for a specific repetition-distance. Some other coaches have only half-jokingly referred to these outputs as my "secret plans". (I once left a copy of one of these workout charts/tables after a workout, where it was later captured by the "enemy".) The core need, though, for this SP was the return of the HH:MM:SS.SSS string, given the input of a time difference. That is, more or less, what Razzak did originally, not long after the Form Timer capability was added to RB forms. Obviously, it could be used for anything where an elapsed time is required. Francais au Allemagne in Belgium? Ich kann Deutsch, aber fast keine Französich. Take care, from Memphis, where it is NOW TOO HOT - and I don't mean the music, Steve -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Luc Delcoigne Sent: Friday, June 11, 2010 2:58pm 14:58 To: RBASE-L Mailing List Subject: [RBASE-L] - Re: setting a form variable with a stored procedure Thanks, Steve. This will be helpful, I'm sure. I'll have a look into it this weekend. Greetings from Belgium to you all in Memphis, Tennessee. Luc D. -------------------------------------------------- From: "Wills, Steve" <[email protected]> Sent: Friday, June 11, 2010 6:10 PM To: "RBASE-L Mailing List" <[email protected]> Subject: [RBASE-L] - Re: setting a form variable with a stored procedure > Luc, if this helps, please feel free to make use of it. > > The commands below show an example of using a stored procedure to assign > a value to a variable. These were issued at the command line/R> using > spReturnHHMMSSString(), which receives an INT, derived from a pair of > TIME variables, and returns a TEXT (12), an Elapsed Time, such as > '00:03:59.4'. > > The procedure file, ReturnHHMMSSString.PRO, is pasted below, between the > '>>>>...' and '<<<<...' lines. Paste it into your editor, then save as > the name in the header documentation, then play with the commands. > > It works for what it is, probably because, in its original form, it was > derived from something Razzak/RBTI authored. It could do with a couple > of mod's/enhancements, as indicated in my header notes. (Maybe I'll get > to those someday, maybe not.) > > > Hope it helps. > > Steve in Memphis > > > --*** 10:06:32.244 *** > R>SET VAR vStartTM = .#TIME > > R>SET VAR vFinishTM = '10:14:58.644' > R>SET VAR vRoelants3000mWR1965_INT = (.vFinishTM - .vStartTM) > R>SET VAR vRoelants3000mWR1965 = (CALL > spReturnHHMMSSString((.vRoelants3000mWR1965_INT))) > -- *** or *** > R>SET VAR vRoelants3000mWR1965 = (CALL spReturnHHMMSSString((.vFinishTM > - .vStartTM))) > > R>SET VAR vFinishTM = '10:34:11.844' > R>SET VAR vPuttemans10000mSilber1972_INT = (.vFinishTM - .vStartTM) > R>SET VAR vPuttemans10000mSilber1972 = (CALL > spReturnHHMMSSString((.vPuttemans10000mSilber1972_INT))) > -- *** or *** > R>SET VAR vPuttemans10000mSilber1972 = (CALL > spReturnHHMMSSString((.vFinishTM - .vStartTM))) > > R>SET VAR vFinishTM = '12:21:04.044' > R>SET VAR vLismontMarathonSilber1972_INT = (.vFinishTM - .vStartTM) > R>SET VAR vLismontMarathonSilber1972 = (CALL > spReturnHHMMSSString((.vFinishTM - .vStartTM))) > -- *** or *** > R>SET VAR vLismontMarathonSilber1972 = (CALL > spReturnHHMMSSString((.vLismontMarathonSilber1972_INT))) > > R>sho var > Variable = Value > Type > -------------------------------- ------------------------------ > ------- > #DATE = 06/11/2010 > DATE > #TIME = 10:39:38.025 > TIME > #PI = 3.14159265358979 > DOUBLE > SQLCODE = 0 > INTEGER > SQLSTATE = 28000 > TEXT > #NOW = 06/11/2010 10:39:38.025 > DATETIME > vStartTM = 10:06:32.244 > TIME > vFinishTM = 10:34:11.850 > TIME > vRoelants3000mWR1965 = 00:08:26.400 > TEXT > vET_SSS_INT = 400 > INTEGER > vET_HH_INT = 0 > INTEGER > vET_MM_INT = 8 > INTEGER > vET_SS_INT = 26 > INTEGER > vETString = 00:08:26.400 > TEXT > vET_HH_Text = 00 > TEXT > vET_MM_Text = 08 > TEXT > vET_SS_Text = 26 > TEXT > vET_SSS_Text = 400 > TEXT > STP_RETURN = 00:08:26.400 > TEXT > vPuttemans10000mSilber1972 = 00:27:39.600 > TEXT > vLismontMarathonSilber1972 = 02:14:31.800 > TEXT > vLismontMarathonSilber1972_INT = 8071800 > INTEGER > vRoelants3000mWR1965_INT = 506400 > INTEGER > vPuttemans10000mSilber1972_INT = 1659606 > INTEGER > > >>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>>>>>>>>>>>> > --************************************************ > --*** ReturnHHMMSSString.PRO ********************* > --************************************************ > --*** 2008.05.23, JSW **************************** > --************************************************ > --*** Borrowed from PACE:spReturnETString.RMD, *** > --*** which was originally "borrowed" *** > --*** on one of Razzak's FORM TIMER examples. *** > --************************************************ > --*** Routine receives > --************************************************ > --*** NOTE: Will return fractions of seconds *** > --*** : if TIME FORMAT is so defined ... *** > --*** ALSO: Related to fractions of seconds, *** > --*** : I need to make modifications *** > --*** : will handle variations in SETTINGS *** > --*** : TIME. Currently, the assumption *** > --*** : 1,000th of seconds. *** > --************************************************ > --*** NOTE: Also, although it should compute *** > --*** : an E/T spanning more than a day, *** > --*** : those results would be inaccurate, *** > --*** : all E/T's are assumed to occur *** > --*** : the span of a single day. *** > --*** ALSO: If longer spans are implemented, *** > --*** : examine PUT ... RETURN TEXT (12) *** > --*** : as string could surely lengthen. *** > --************************************************ > > {SET PROC spReturnHHMMSSString LOCK ON} > {PUT ReturnHHMMSSString.PRO AS spReturnHHMMSSString vElapsedTM INT > RETURN TEXT (12)} > > SET VAR vET_SSS_INT INT = (MOD(.vElapsedTM,1000)) > SET VAR vET_HH_INT INT = (INT(.vElapsedTM/3600000)) > SET VAR vET_MM_INT INT = (INT(MOD(.vElapsedTM,3600000)/60000)) > SET VAR vET_SS_INT INT = + > ((.vElapsedTM - ((.vET_MM_INT * 60000) + (.vET_HH_INT * 3600000)) > )/1000) > > SET VAR vETString TEXT ='00:00:00.000' > SET VAR vET_HH_Text TEXT > SET VAR vET_MM_Text TEXT > SET VAR vET_SS_Text TEXT > > > -- *** HH Portion of Time String *** > -- *** Assumes TIME FORMAT of HHMMSSSSS, i.e. 100th's of SS's > -- *** So, 1 HOUR = 3600000 || ((60*60)*1000)) > IF (.vET_HH_INT < 10) THEN > SET VAR vET_HH_Text = ('0' + (CTXT(.vET_HH_INT))) > ELSE > SET VAR vET_HH_Text = (CTXT(.vET_HH_INT)) > ENDIF > > -- *** MM Portion of Time String *** > IF (.vET_MM_INT < 10) THEN > SET VAR vET_MM_Text = ('0' + (CTXT(.vET_MM_INT))) > ELSE > SET VAR vET_MM_Text = (CTXT(.vET_MM_INT)) > ENDIF > > -- *** SS Portion of Time String *** > IF (.vET_SS_INT < 10) THEN > SET VAR vET_SS_Text = ('0' + (CTXT(.vET_SS_INT))) > ELSE > SET VAR vET_SS_Text = (CTXT(.vET_SS_INT)) > ENDIF > > -- *** .SSS Portion of Time String *** > IF (.vET_SSS_INT < 10) THEN > SET VAR vET_SSS_Text = ('00' + (CTXT(.vET_SSS_INT))) > ELSE > IF (.vET_SSS_INT) < 100 THEN > SET VAR vET_SSS_Text = ('0' + (CTXT(.vET_SSS_INT))) > ELSE > SET VAR vET_SSS_Text = (CTXT(.vET_SSS_INT)) > ENDIF > ENDIF > > -- *** HH:MM:SS Time String *** > SET VAR vETString = (SPUT(.vETString, .vET_HH_Text, 1)) > SET VAR vETString = (SPUT(.vETString, .vET_MM_Text, 4)) > SET VAR vETString = (SPUT(.vETString, .vET_SS_Text, 7)) > SET VAR vETString = (SPUT(.vETString, .vET_SSS_Text, 10)) > > --*** For leaning/debugging do not CLEAR VAR ... *** > --CLEAR VAR vET_HH_Text, vET_MM_Text, vET_SS_Text, vET_SSS_Text, + > -- vET_HH_INT, vET_MM_INT, vET_SS_INT, vET_SSS_INT > > RETURN .vETString > > RETURN > > <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< > <<<<<<<<<<<<<<<<<<<<<< > > > > > > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On Behalf Of Luc > Delcoigne > Sent: Friday, June 11, 2010 1:53am 01:53 > To: RBASE-L Mailing List > Subject: [RBASE-L] - Re: setting a form variable with a stored procedure > > This solution is not so evident for me. > Karen's solution works fine, but I'd like to have some more info on your > > solution for the future. > > Can you give me an example in extenso ? > > Thank you very much, Mike. > > Luc Delcoigne > > -------------------------------------------------- > From: "Mike Byerley" <[email protected]> > Sent: Friday, June 11, 2010 12:32 AM > To: "RBASE-L Mailing List" <[email protected]> > Subject: [RBASE-L] - Re: setting a form variable with a stored procedure > >> Design your stored procedure to return a result and assign the result >> directly to the variable Like: >> >> set var vTheVarToSet SomeDataType = (Call StoredProcedureName(Arg1, > Arg2)) >> >> If the Form Field you want to set is not a variable field, then use > the >> Property command to set the field value to the variable returned by > the >> stored procedure. >> >> >> ----- Original Message ----- >> From: "Luc Delcoigne" <[email protected]> >> To: "RBASE-L Mailing List" <[email protected]> >> Sent: Thursday, June 10, 2010 5:38 PM >> Subject: [RBASE-L] - setting a form variable with a stored procedure >> >> >> Hi, >> >> On a form I have multipele currency fields. >> >> Via a stored procedure I calculate the sum of certain fields (which >> varies) and want to display the result of my stored procedure in a >> variable (or other?) field on the form. >> >> In access you can set the source/value property of a variable to " >> =Procedure()". >> >> In rbase I have a stored procedure that calculates the variable >> "vpricetopay". >> How can I set a variable to display this result dynamically on my form > >> without saving the value to a table ? >> >> Tx >> >> Luc Delcoigne >> > >
