Formula to add X no. of days in Crystal reports 2

[balin]

I'm trying to unravel the Accpac date syntax with relation to Crystal reports.

I inderstand pwFormatDate but I don't understand the Accpac date format.

I want to add 24 days to any Accpac date. I tried pwFormatDate({anydate}+24). This works until it pushes the date into the next month. Then nothing is displayed and no errors are reported. Obviously adding 24 to the date was hopefull. So I need a formula that does it properly.

Any tips? Even an explanation of the cryptic date format would help.

JON

 

[jayconverse]

pwformatdate({anydate})+24

Jay Converse
IT Director
Systemlink, Inc.

 

[balin]

Thanks Greatly,

I was confused by the way Crystal reports had formated the Accpac date numbers - 27th August 2004 Showed as 20,0408,27.00

I now realise it's just the display formatting and is really 20040827

So pwFormatDate counts days right?? To add 1 yr. add 365 but what about adding 1 month?? 30 31 or 28??

Can't find anything in Help on this.

JON

 

[dgillz]

Dateadd("d", X,Numbertodate({YourACCPACDateField}))

Replace X with the nuber of days, and you are in. To add a month, try this:


Dateadd("m",1,Numbertodate({YourACCPACDateField}))


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[jayconverse]

Actually, it's:

Dateadd("d",1,pwFormatDate({YourACCPACDateField}))

Jay Converse
IT Director
Systemlink, Inc.

 

[dgillz]

I am unfamiliar with the pwformatdate() function but numbertodate() will work just fine.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[jayconverse]

pwFormatDate and pwFormatTime are custom functions that are installed in the U2LCAPW.DLL with Accpac Advantage series.

Jay Converse
IT Director
Systemlink, Inc.

 

[balin]

So the Custom functions don't have any help reference material?

Thanks in advance Jay - I'm up and running.

JOn

 

[jayconverse]

Nope, you just have to know them. But here's a posting from another forum:

Sage Accpac Crystal Function Installed Library

IsTrue (String)

This function will take in a string, and return its equivalent boolean value. Will return True if the string is TRUE, T, or 1 (any case).

pwFormatDate (Number)

Formats a date in the same way the Accpac does in UI programs. The number should be of the form yyyymmdd.

pwFormatTime (Number)

Formats a time value in the same way Accpac does in UI programs. The number should be of the form hhmmsshh.

TrimZeros (String)

Trims trailing zeroes off a string value. Ie changes “xyz0000” into “xyz”.

LookUpString(String, String, String)

First parameter is a language code (numeric code). Second parameter is a list of language codes. Third parameter is a list of language names. Used to decode language fields in bank services.

SaveNumber(String, String, String, String)

Saves a number into a file. First parameter is the file name. Second is the number to be stored. Third is the file mode flag (“TRUE” to create new file else appends). The forth parameter is the number ID as defined in GetBankTotal below. This function is used in conjunction with GetBankTotal below.

GetBankTotal(String)

Function reads number from a file and calculates bank total. Parameter is the file name to read. Returns the total. This function is used in conjunction with save number. File contains records of the form:

struct BankTotal
{
char ID;
char Space;
char Total[30];
};

ID Total Value
'1' Entries
'2' NSFs
'3' Deposits
'4' Checks


PrintNonNull(String)

Print one space if the blank spaces or null string is parsed. Print a parameter passed.

TranslateType(Number)

Translate number to an account type. Parameter is the type. Does the following converstion:

Account Type Number (input) Account Type String (returned)
1 "IN"
2 "DB"
3 "CR"
4 "IT"
5 "UC"
6 "DA”
7 "AD"
8 "CA"
9 "AC"
10 "PI"
11 "PY"
12 "ED"
13 "UD"
14 "AD"
15 "CB"
16 "GL"


RemoveLeadZero(String)

Removes leading zeroes from a string.

smGetSeries()

Returns the System Manager product edition (1=Enterprise, 4=Corporate, 6=Small Business).

LicenseStatus(String,String)

Checks if a license is valid. First parameter is the two letter prefix (such as CS), second parameter is the version (such as 53A). Returns 0 if ok, -1 if not found, -2 if expired.

OptionMulticurrency()

Returns “CS”.

OptionOptionalTables()

Returns “OB”.

OptionNationalAccounts()

Returns “NA”.

OptionOptionalFields()

Returns “OB”.

pwNumericStringToNumber(String)

Switches numeric strings to numbers, where the following are hardcoded:
* Negative sign is "-" and precedes the number, with no space after the sign
* Decimal sign is "."
* There's no group ("thousands") separator
Such strings (i.e. opt fld VALUE data) would have been created with bcdToStr.
(Since those strings come from DB string fields, we use locale-independent representations.) Using Crystal's ToNumber on such strings would fail when the locale changes (i.e. negatives become (1,5) instead of -1.5).

pwFormatString(String, String, Number, String)

Formats a standard database field for display. First parameter is a language code. Second parameter is the string to format. Third parameter is a database field type:

Number Type
1 String
2 Binary
3 Date
4 Time
5 Float
6 BCD (fixed precision numeric)
7 Short Integer
8 Long Integer
9 Boolean
100 Money

The forth parameter is not used (but you must provide one, empty string is ok).

pwGetString(String, String)

Loads a string from pwuflLLL.dll where LLL is a language code like eng. (I.e. pwufleng.dll). First parameter is the language code. Second parameter is string to translate.



Jay Converse
IT Director
Systemlink, Inc.

 

[dgillz]

Jay had a great post, but it is worth pointing out that these are all ACCPAC-specific crystal functions (my main product, Macola, has a lot of these too). If the .dll that makes these functions available to crystal is not present, the reports that use these functions will not run.

In my case, I avoid using custom functions if at all possible, for this reason.

Also many, if not all, of the abilities provided by these custom functions is available in standard crystal, although you may need to use a few more keystrokes


Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz