Create cursor and replace data to the cursor. 2

Niki_S · Aug 4, 2022

I have a cursor like this and I want a varchar field.

<Create Cursor Emp_Dtl(cFact C(5) Null,nEmpNo N(15,0) Null,cFullName C(60) Null,cDepartment C(50) Null,dJoin D Null,dResign D Null,Months int Null,Years int Null, Days int Null, Period c(100) Null)

SELECT Emp_Dtl
Append From Dbf('Emp_Detl')

SELECT Emp_Dtl
SCAN

_Years = (year(dResign) - year(dJoin)) + 'Years' ****5 Years****
_Months = (abs(month(dResign) - month(dJoin)) ) + 'Months' ****10 Months****
_Days = (abs(day(dResign) - day(dJoin)) ) + 'Days' ****5 Days****

SELECT Emp_Dtl
REPLACE Period WITH _Years + _Months + _Days

ENDSCAN>

How can I update my Period field as '5 Years 10 Months 5 Days'.

Thank you

Mike Lewis · Aug 4, 2022

Niki,

I'm not sure I understand your question. Are you saying that you want the Period field to contain a character string, and that character string should contain the text "xx Years yy Months zz Days", where xx, yy adn zz stand for the number of years, months and days respectively: the numbers 5, 10 and 5 being examples of such numbers?

If that's correct, this has nothing to do with cursors or varchars. It's a simple bit of string manipulation.

Given the variables _Year, _Month and _Day that hold the year, month and day respectively, then all you need is:

Code:

REPLACE Period WITH ;
  TRANSFORM(_Year) + " Years " + TANSFORM(_Month) + " Months " + ;
  TRANSFORM(_Day) + "Days"

If I have misunderstood, please explain your question more clearly.

By the way, varchar fields are rarely used for native Foxpro data (as opposed to data from a back-end database). In this case, your Char(100) field would suffice, or you could use a memo field.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

TamarGranor · Aug 4, 2022

I just want to point out that the math here isn't as simply as subtracting in each case. You have to adjust for the possibility that the resignation date is earlier in its year than the join date, or that the resign day is earlier in its month than the join day is in its.

You need to adjust for those in your code, something like this (not tested and I didn't include the logic to compute the number of days):

Code:

nYears = year(dResign) - year(dJoin)

do case
case month(dResign) > month(dJoin)
   nMonths = month(dResign) - month(dJoin)

case month(dResign) < month(dJoin)
   nYears  = nYears - 1
   nMonths = 12 - (month(dJoin) - month(dResign))

case month(dResign) = month(dJoin)
   do case
   case day(dResign) > day(dJoin)
      nMonths = month(dResign) - month(dJoin)      

   case day(dResign) < day(dJoin)
      nYears = nYears - 1
      nMonths = 12 - (month(dJoin) - month(dResign))

   case day(dResign) = day(dJoin)
      nMonths = 0
   endcase

endcase

Tamar

Niki_S · Aug 5, 2022

Thank you so much for both of you.
Can you please tell me the way of getting 'Days'?

Thank You

mjcmkrsr · Aug 5, 2022

Hi Niki S,

Please have a look at thread184-1817993

hth

MarK

mjcmkrsr · Aug 6, 2022

Hi Niki S

...

and you don't need the Years, Months, Days fields in your cursor (see code below)

Code:

Create Cursor Emp_Dtl(cFact C(5) Null, ;
				nEmpNo N(15,0) Null, ;
				cFullName C(60) Null, ;
				cDepartment C(50) Null, ;
				dJoin D Null, ;
				dResign D Null, ;
				cPeriod c(50) Null)
				
INSERT INTO Emp_Dtl values("ABCDA", 987456321, "Niki S", "Front Office", {^2001-01-01}, {^2020-05-31}, "")
INSERT INTO Emp_Dtl values("ABCDB", 123456789, "Jeff B", "Front Office", {^2001-02-01}, {^2020-04-30}, "")
INSERT INTO Emp_Dtl values("ABCDE", 456789123, "Homer F", "Back Office", {^2010-07-01}, {^2020-05-31}, "")



*!*	SELECT Emp_Dtl
*!*	Append From Dbf('Emp_Detl')


SELECT Emp_Dtl

SCAN

	REPLACE cPeriod WITH AMDateDiffAsWords(dJoin, dResign)

ENDSCAN

BROWSE 

CLOSE ALL
CLEAR ALL 

RETURN 

**********

FUNCTION AMDateDiffAsWords()
	LPARAMETERS  tdSDate, tdEDate

		LOCAL ldFirstNextMonth as Date, ;
				 liTotalMonths as Integer, ;
				 liYears as Integer, ;
				 liMonths as Integer, ;
				 liWeeks as Integer, ;
				 liDays as Integer, ;
				 lcAnswer as Character
				 
		ldFirstNextMonth = {}
		liTotalMonths = 0
		liYears = 0
		liMonths = 0
		liWeeks = 0
		liDays = 0
		lcAnswer = ""  

		*!*	If Day(tdSDate) > day(tdEDate), we calculate the number of days remaining in the 
		*!*	start month (may be zero) and add it to the number of days in the end date

		IF tdEDate > tdSDate
			IF DAY(tdSDate) > DAY(tdEDate)
				ldFirstNextMonth = GOMONTH(tdSDate - DAY(tdSDate) + 1 , 1)
				liDays = DAY(tdEDate) + (ldFirstNextMonth - tdSDate) - 1

			ELSE
				liDays = DAY(tdEDate) - DAY(tdSDate)
			
			ENDIF
		   
		*!*	Now the total number of months, years and weeks and the remaining days

		   liTotalMonths = MONTH(tdEDate) - MONTH(tdSDate) + (12 * (YEAR(tdEDate) - YEAR(tdSDate))) - IIF(DAY(tdSDate) > DAY(tdEDate), 1, 0)
		   liYears = INT(liTotalMonths / 12)
		   liMonths = liTotalMonths - (liYears * 12)
		   liWeeks = INT(liDays / 7)
		   liDays = liDays - (7 * liWeeks)
		   
		ELSE 
			=MESSAGEBOX("Please check order of parameters", 48, "Difference in Y/M/W/D between dates")

		ENDIF
		  
		*!*	And format the answer as text

		   IF liYears > 0
		      lcAnswer = ALLTRIM(STR(liYears)+ " Year" + IIF(liYears > 1,"s",""))
		      ENDIF
		   IF liMonths > 0
		      lcAnswer = lcAnswer +" "+ ALLTRIM(STR(liMonths))+" Month"+ IIF(liMonths > 1,"s","")
		      ENDIF
		   IF liWeeks > 0
		      lcAnswer = lcAnswer +" "+ ALLTRIM(STR(liWeeks)) +" Week"+ IIF(liWeeks > 1,"s","")
		      ENDIF
		   IF liDays > 0
		      lcAnswer = lcAnswer +" "+ ALLTRIM(STR(liDays))+" Day"+ IIF(liDays > 1,"s","")
		      ENDIF
		RETURN lcAnswer
ENDFUNC

hth

MarK

Niki_S · Aug 8, 2022

I used this way to get the day.

<DO CASE
CASE DAY(dResign) > DAY(dJoin)
dDay = DAY(dResign) - DAY(dJoin)

CASE DAY(dResign) < DAY(dJoin)
nMonths = nMonths - 1
dDay = 30 - ( DAY(dJoin) - DAY(dResign))

CASE DAY(dResign) = DAY(dJoin)
DO CASE
CASE DAY(dResign) > DAY(dJoin)
dDay = DAY(dResign) - DAY(dJoin)

CASE DAY(dResign) < DAY(dJoin)
nMonths = nMonths - 1
dDay = 30 - ( DAY(dJoin) - DAY(dResign))

CASE DAY(dResign) = DAY(dJoin)
dDay = 0

ENDCASE>

Is this correct?

Thank you.

mjcmkrsr · Aug 8, 2022

Hi Niki S

I'm very sorry but you're code does not yield correct results - not every month has 30 days, e.g. Feb may have 28 or 29 days, Jul and Aug have each 31 days.

Why not use the code I posted?

hth

MarK

Niki_S · Aug 8, 2022

Thank you all. I did my one as below.

SELECT Emp_Dtl
SCAN
IF dResign > dJoin
IF DAY(dJoin) > DAY(dResign )
FirstNextMonth = GOMONTH(dJoin - DAY(dJoin ) + 1 , 1)
nDays = DAY(dResign ) + (FirstNextMonth - dJoin ) - 1

ELSE
nDays = DAY(dResign ) - DAY(dJoin )

ENDIF

nTotalMonths = MONTH(dResign ) - MONTH(dJoin ) + (12 * (YEAR(dResign ) - YEAR(dJoin ))) - IIF(DAY(dJoin ) > DAY(dResign ), 1, 0)
nYears = INT(nTotalMonths / 12)
nMonths = nTotalMonths - (nYears * 12)
nWeeks = INT(nDays / 7)
nDays = nDays - (7 * nWeeks)

ENDIF

SELECT Emp_Dtl
REPLACE Months WITH nTotalMonths

IF nMonths > 0
_Month = TRANSFORM(nMonths) + " Months "
ELSE
_Month = ""
ENDIF

IF nYears > 0
_Year = TRANSFORM(nYears) + " Years "
ELSE
_Year = ""
ENDIF

IF nDays > 0
_Day = TRANSFORM(nDays) + " Days "
ELSE
_Day = ""
ENDIF

SELECT Emp_Dtl
REPLACE Period WITH _Day + _Month + _Year

ENDSCAN

Thank you. [pc2]

mjcmkrsr · Aug 8, 2022

Hi Niki S.

There's a little gotcha in the code you modified.

If you don't want the weeks in your result you have to comment out two lines of code - otherwise the number of days will be wrong

Code:

IF dResign > dJoin

[indent]IF DAY(dJoin) > DAY(dResign )
FirstNextMonth = GOMONTH(dJoin - DAY(dJoin ) + 1 , 1)
nDays = DAY(dResign ) + (FirstNextMonth - dJoin ) - 1

ELSE
nDays = DAY(dResign ) - DAY(dJoin )

ENDIF


nTotalMonths = MONTH(dResign ) - MONTH(dJoin ) + (12 * (YEAR(dResign ) - YEAR(dJoin ))) - IIF(DAY(dJoin ) > DAY(dResign ), 1, 0)
nYears = INT(nTotalMonths / 12)
nMonths = nTotalMonths - (nYears * 12)
[highlight #EF2929]nWeeks = INT(nDays / 7)
nDays = nDays - (7 * nWeeks)[/indent]
[/highlight]

ENDIF

hth

MarK

Niki_S · Aug 8, 2022

I have another point to clear.
If the record are as below,

dJoin = 2021-04-29 and the dResign = 2021-05-20.

In this the gap is 21. But according to this

liWeeks = INT(liDays / 7)

liDays = liDays - (7 * liWeeks)

the result of liDays = 0.
I want to get it as 21. how can I do that? If I do like this,

nDays = nDays

Is this correct or not?

mjcmkrsr · Aug 8, 2022

Hi Niki S.

Please read my former post

hth

MarK

mjcmkrsr · Aug 8, 2022

Hi Niki S.

Your code - modified and tweaked

Code:

SELECT Emp_Dtl

SCAN
[indent]IF dResign > dJoin
[indent]IF DAY(dJoin) > DAY(dResign )
FirstNextMonth = GOMONTH(dJoin - DAY(dJoin ) + 1 , 1)
nDays = DAY(dResign ) + (FirstNextMonth - dJoin ) - 1

ELSE
nDays = DAY(dResign ) - DAY(dJoin )

ENDIF[/indent]


nTotalMonths = MONTH(dResign ) - MONTH(dJoin ) + (12 * (YEAR(dResign ) - YEAR(dJoin ))) - IIF(DAY(dJoin ) > DAY(dResign ), 1, 0)
nYears = INT(nTotalMonths / 12)
nMonths = nTotalMonths - (nYears * 12)
[highlight #F57900]*!* nWeeks = INT(nDays / 7)
*!* nDays = nDays - (7 * nWeeks)
[/highlight]
[highlight #EF2929]
*!* ENDIF && please move it just before ENDSCAN[/highlight]

[highlight #F57900]*!* SELECT Emp_Dtl
*!* REPLACE Months WITH nTotalMonths[/highlight]

IF nMonths > 0
_Month = TRANSFORM(nMonths) + " Months "
ELSE
_Month = ""
ENDIF

IF nYears > 0
_Year = TRANSFORM(nYears) + " Years "
ELSE
_Year = ""
ENDIF

IF nDays > 0
_Day = TRANSFORM(nDays) + " Days "
ELSE
_Day = ""
ENDIF

[highlight #F57900]"!" SELECT Emp_Dtl[/highlight]
REPLACE Period WITH _Day + _Month + _Year

[highlight #EF2929]ENDIF[/highlight][/indent]

ENDSCAN

hth

MarK

Niki_S · Aug 8, 2022

Thank you Mark for your support. [bigsmile]

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Create cursor and replace data to the cursor. 2

Niki_S

Programmer

Mike Lewis

Programmer

TamarGranor

Programmer

Niki_S

Programmer

mjcmkrsr

Technical User

mjcmkrsr

Technical User

Niki_S

Programmer

mjcmkrsr

Technical User

Niki_S

Programmer

mjcmkrsr

Technical User

Niki_S

Programmer

mjcmkrsr

Technical User

mjcmkrsr

Technical User

Niki_S

Programmer

Similar threads

Part and Inventory Search

Sponsor