currentdate / current month problem ?! 1

[CayJack]

Hi there,

I have a table S511 with a date (day, month, year) and another table S510 with a monthly date (month, year)
When I work with table S511 (D,M,Y) I can use the following formula without any issues:

if (month({S510.SPTAG}) = month(currentdate)) AND (year({S510.SPTAG}) = year(currentdate))
then ...
else ...,


Now i got that new table S510 (only month and year)and my formula doesn't work anymore.
if (month({S511.SPMON}) = month(currentdate)) AND (year({S511.SPMON}) = year(currentdate))
then ...
else ...,

Something is wrong with my formula as I am getting an error saying "a date is required here".
What is wrong ? Is there maybe a problem with "currentdate" , since that includes Day, month and year ?? I have no clue.
Any ideas ?

Thanks in advance for your help
Jacky

 

[SkipVought]

hi,

The difference is that SPTAG is DATE and SPMON is STRING. DATE is a NUMBER datatype and STRING is, well er um uhhhhh...a STRING datatype!!!

The Year() & Month() functions only work for DATES!

Parse the month and year using string functions, like Left() and Right() and be sure to CONVERT these strings, like "2013" & "09" to NUMBERS before you compare to the current year and month.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CayJack]

Thanks Skip.... I was thinking way to complicated. Left and Right works perfect... didn't even think about that way. Thanks a lot.

 

[CayJack]

So, that's what I did with the Formula:

if ToNumber(Right({S510.SPMON},2)) = month(currentdate) AND
ToNumber(Left({S510.SPMON},4)) = year(currentdate)
then {S510.ZUMKZWI3}
else 0

It works fine.

So I tried to change the following formula as well since I am not using the Date-Field anymore but the String with just Month and Year.

Local NumberVar jahr := Year(currentdate);
Select Month(currentdate)
Case 12: If {S501.SPTAG} In Date(jahr,1,1) To Date(jahr,12,31)
Then {S501.UMKZWI3}-{S501.GUKZWI3}
Else 0
Default: If {S501.SPTAG} In Date(jahr-1,Month(currentdate)+1,1) To currentdate
Then {S501.UMKZWI3}-{S501.GUKZWI3}
Else 0

Whatever I tried I didn't work...I have errors over errors.
Could you please give me an advice in the right direction ?

Below you can see my try - please don't laugh about it - I try to understand how to work with Strings:


if ToNumber(Right({S510.SPMON},2) = "01") AND ToNumber(Left({S510.SPMON},4)) = year(currentdate)
to
ToNumber(Right({S510.SPMON},2) = "12") AND ToNumber(Left({S510.SPMON},4)) = year(currentdate)
then .....
else
If ToNumber(Right({S510.SPMON},2) = month(currentdate+1)) AND ToNumber(Left({S510.SPMON},4)) = (year(currentdate)-1)
To currentdate
Then ...
Else 0

I know its not right but I don't know where and how I could start to change something :-(

Thanks again for your help.
Jacky

 

[CayJack]

It works now:

Local NumberVar jahr := Year(currentdate);
Select Month(currentdate)
Case 12: If cdate(Tonumber(Left({S510.SPMON},4)),Tonumber(Right({S510.SPMON},2)),1) In Date(jahr,1,1) To Date(jahr,12,31)
Then {S511.UMKZWI3}-{S511.GUKZWI3}
Else 0
Default: If cdate(Tonumber(Left({S510.SPMON},4)),Tonumber(Right({S510.SPMON},2)),1) In Date(jahr-1,Month(currentdate)+1,1) To currentdate
Then {S511.UMKZWI3}-{S511.GUKZWI3}
Else 0