Print year in foxpro9

[Niki_S]

I have a data set which have nparayear as 2020.
If my nparayear is equal to this year that means 2021 there is no issue to print number as 2021. But if someone run the file in 2022 I need to get my nparayear as 2021. how can I do this? This is how I tried.

stra="select  nParaID, cParaNo, cParaYear, cParaCD from MIS.dbo.wshPara  WHERE cParaCD=?thisform.cboFactory.value+'C'"
SQLEXEC(hndOps,stra,'_wshPara')

SELECT _wshPara
IF YEAR(date()) then
 
 SELECT val(cParaYear)  as nParaYear FROM _wshPara INTO CURSOR N_Year
 year=N_Year.nParaYear 
 
ELSE
year_new=ALLTRIM(N_Year.nParaYear)+ 1
year=year_new.nParaYear 

 ENDIF

 

[Chris Miller]

IF YEAR(date()) then

This will error, as a number isn't a boolean value in VFP.

I don't get what you really want. The next year always is YEAR(date())+1.

Chriss

 

[Niki_S]

I modified it like this.

SELECT _wshPara
IF YEAR(date()) && I need to get Current  year
 
 SELECT val(cParaYear)  as nParaYear FROM _wshPara INTO CURSOR N_Year   $$ I need to covert cParaYear into Val
 year=N_Year.nParaYear    && Need to eual current year to the field data after I need to Don't update my sql table cParaYear
 
ELSE
 cParaYear=YEAR(date())+1  && I need to update sql table cParaYear field as new year. If we are in 2022 the cParaYear field in sql table need to be update as 2021

ENDIF

But It comes an error message 'mismatch data type' in "IF YEAR(date()) ".
I mentioned what I need in my code. Can you please help me to do this?
Thank you!

 

[Chris Miller]

Year(date() is the current year. But it's not what you need for IF, IF needs a .T. or .F. value (that's true or false), not a number.

I don't know what you want as the IF condition. If you want to check whether the aear is the current year just from DATE(), that's today and that's always the current year. You have to have something to compare it with, don't you? Before you branch with IF.

So, in short, it's still unclear what you want.

Chriss

 

[Gerrit Broekhuis]

Niki,

Perhaps you need something like this:

<code>If year(date()) = 2021
...
Else
...
Endif</code>

Regards, Gerrit

 

[Niki_S]

Yes Gerrit,
I did it as below,

SELECT _wshPara
IF YEAR(date())=2021 
 
 SELECT val(cParaYear)  as nParaYear FROM _wshPara INTO CURSOR N_Year
 year=N_Year.nParaYear 
 
ELSE
_cParaYear=YEAR(date())+1
stra="update MIS.dbo.wshPara set cParaYear=?_cParaYear where cParaCD=?thisform.cboFactory.value+'C'"
SQLEXEC(hndOps,stra)
 ENDIF

Is this correct?

 

[Chris Miller]

Technically this is valid code. It doesn't seem right from the perspective that after this year only the ELSE branch will run.

I guess you need something that needs to kick in when a new year begins. You can't check that with YEAR(date()), this has to be compared with the previous Year(date()).
Assume you store the current year in a configuration table, then you'd

1. Load the year from config data
2. Compare IF Year(date())>cofigyear, which would become true the first time the application is started in a later year than the last time due to config.
3. Do anything that needs to be done due to year change, for example extending calendar data.
4. Update config to the now new current year.

But I really still don't get what you actually want and only you can tell us, we can't tell you what you want or need.

Chriss

 

[Niki_S]

Thank you Chriss,
What I need is I have a table in SQL server like below,

nParaID   cParaNo   cParaYear    cParaCD
219       1         2020         VTMC

And I have a foxpro9 form which do various things. In here I need to update my cParaYear as 2021 If I am not inserting my data in 2021 that means , If I'm inserting my data in 2022 that means if the system date in 2022 it should update as 2021. If I'm inserting my data in 2021 there haven't anything to change. For this purpose I used my code as below,

stra="select  nParaID,cParaYear from MIS.dbo.wshPara  WHERE  cParaCD=?thisform.cboFactory.value+'C'"
SQLEXEC(hndOps,stra,'_ParaYear')

SELECT _ParaYear

IF YEAR(date())=2021 
 
 SELECT ALLTRIM(STR(VAL(_ParaYear.cParaYear))) FROM _ParaYear INTO CURSOR N_Year 
 
ELSE

_cParaYear=YEAR(date())+1
stra="update MIS.dbo.wshPara set cParaYear=?_cParaYear where cParaCD=?thisform.cboFactory.value+'C'"
SQLEXEC(hndOps,stra)

 ENDIF

Is this correct or if this is wrong what should I do?

 

[Chris Miller]

The logic doesn't seem sound to me.

It looks like you want to have MIS.dbo.wshPara.cParaYear to be the previous year.
You want to check whether that already is the case to not do an unnecessary UPDATE.
Well, but you don't get there by checking whether YEAR(date())=2021 but by checking whether YEAR(date())=VAL(cParaYear)+1.

stra="select  nParaID,cParaYear from MIS.dbo.wshPara  WHERE  cParaCD=?thisform.cboFactory.value+'C'"
SQLEXEC(hndOps,stra,'_ParaYear')

SELECT _ParaYear

IF VAL(cParaYear) = YEAR(date())-1
   * No update of MIS.dbo.wshPara, cParaYear is as wanted.

   SELECT cParaYear FROM _ParaYear INTO CURSOR N_Year && not sure whether that's what you need.
   * You don't need to create a further cursor to get what ou already have in _ParaYear

   * You can directly read this field into a variable definition:
   * N_Year = VAL(cParaYear) && the numeric year
   * C_Year = ALLTRIM(cParaYear) && the year as a string (trimmed)
   
ELSE
   * Update  MIS.dbo.wshPara.cParaYear:
   _cParaYear=TRANSFORM(YEAR(date())-1)
   stra="update MIS.dbo.wshPara set cParaYear=?_cParaYear where cParaCD=?thisform.cboFactory.value+'C'"
   SQLEXEC(hndOps,stra)
ENDIF
* Maybe you also want to have a cursor or variable with the last year in the ELSE case, too. 
* Well, then it wouldn't even depend on what's stored you can just set a variable N_LastYear = Year(Date())-1.

Chriss

 

[Niki_S]

Here I need to make a small change. I need to get the year according to the field data. That means it is not depend on system date. It shows as below.

nParaID   cParaNo   cParaYear    cParaCD     dXFactory
219       1         2020         VTMC        2020-08-21 00:00:00.000

In this we have to check the year with dXFactory. So I changed my code as below,

stra="select  nParaID,cParaYear from MIS.dbo.wshPara  WHERE  cParaCD=?thisform.cboFactory.value+'C'"
SQLEXEC(hndOps,stra,'_ParaYear')

SELECT _ParaYear

IF VAL(cParaYear) = TTOD(dXFactory)-1  *****I need to get year*****
   SELECT cParaYear FROM _ParaYear INTO CURSOR N_Year 
   
ELSE
   _cParaYear=TRANSFORM(TTOD(dXFactory)-1)
   stra="update MIS.dbo.wshPara set cParaYear=?_cParaYear where cParaCD=?thisform.cboFactory.value+'C'"
   SQLEXEC(hndOps,stra)
ENDIF

Is this okay ?
Thank you.

 

[Chris Miller]

Well, you already know Year(date()) gives you the year. That's also true for Datetimes.

YEAR(dXFactory)-1 will work, no need to first cast from datetime to date.


Chriss

 

[Niki_S]

Thank you chriss, your help is really helpful to me.

TTOD(dXFactory)-1

I think this method is also okay, isn't it

 

[Chris Miller]

No, this gives you a date, not a year, and only one day before dXFactory, not one year.

adding or subtracting a number does different things depending on what data type:
1. Datetime: add/subtract seconds
2. Date: add subtract days
3. YEAR(date): add/subtract a year.

Well, actually the third case is only the year, so +/- 1 here just is normal math of numbers.

There's also a possibility to go back or forth with GOMONTH(),
For example GOMONTH(DateTime(),-12) means gong back one year to the same date and time of that date. That might be helpful, too.

Chriss

 

[Niki_S]

Okay chriss I got what you tell. Once again thank you for your huge support.

 

[Niki_S]

I have to update my code like this.
I have two tables on sql server as below,

table vInvFinalAll,

cFtyCD    cInvNo            dXFactory
VTM       VTM0001/S/20      2020-08-21 00:00:00.000
VTM       VTM0023/S/20      2018-07-15 00:00:00.000
SFC       SFC653/45         2010-04-23 00:00:00.000

table wshPara,

nParaID   cParaNo   cParaYear    cParaCD
219       1         2020         VTMC  
219       2         2018         VTMC
234       1         2010         SFCC

I have to change my cParaYear based on dXFactory.
If I insert data into my foxpro form In 2021 that means this year anything no need to change in cParaYear. But if I insert data in next year [2022] the cParaYear want to update as 2021.
My final output want to get as below,

table wshPara,

nParaID   cParaNo   cParaYear    cParaCD
219       1         2021         VTMC  
219       2         2019         VTMC
234       1         2011         SFCC

I don't have any idea of this, someone can please tell me howto do that. I did my code as below,

stra="select   cFtyCD, cInvNo,dXFactory from MIS.dbo.vInvFinalAll WHERE  cInvNo=?thisform.txtInvoiceNo.VALUE"
SQLEXEC(hndOps,stra,'_XFactory')

SELECT _XFactory

IF VAL(dXFactory) = YEAR(dXFactory)-1
   SELECT dXFactory FROM _XFactory INTO CURSOR N_Year 
   
ELSE
	stra="select  nParaID,cParaYear from MIS.dbo.wshPara  WHERE  cParaCD=?thisform.cboFactory.value+'C'"
	SQLEXEC(hndOps,stra,'_ParaYear')
	SELECT _ParaYear
   _cParaYear=TRANSFORM(YEAR(cParaYear )+1)
   stra="update MIS.dbo.wshPara set cParaYear=?_cParaYear where cParaCD=?thisform.cboFactory.value+'C'"
   SQLEXEC(hndOps,stra)
ENDIF

Thank you

 

[Chris Miller]

I already gave you what you need. ou want the YEAR from the dxFactory datetime, so you want YEAR, not VAL.

You also don't want 2010 to become 2020 when you use the form in 2022, only for the record of cParaYear = 2019.

So just make a better defintion of what you want, this will never work, because you can't decide when the cParaYear already was updated already. Say you work on the record of 2010 and update it to 2011 on January 10, 2022. When the app then is used on February 22, nothing tells it that 2011 already was updated this year, so you'd again update it to 2012 and then it would increase by 2 in the same year.

There has to be something telling you which records need an update and which not. If the cPAraYear isn't always last year but also can be year-3 or year-9, all you know is cParaYear has to increase by 1 once each year and have to ensure it's not done twice.

All in all you need more precise definitions of what you need, examples only help to see whether your definition is working, and these examples just tell me you still have no idea on the real need.



Chriss

 

[Steve Meyerson]

Hi Chriss, Niki,

I may be far off on this, but it seems to me the OP might want to do something as simple as check to see if a date is legitimate in accordance with op's necessary parameters, then flag it or change it.

If that's the case, the op might simply specify the range of acceptable dates, then use the BETWEEN(testDate,<earliest>,<latest>) function to test each date and flag or change it as desired if the test fails. Rewrite accordingly.

Steve