Need to create formula for seperating out account numbers

[nathanwiley]

Ok, I work in energy assistance, and I use crystal reports to run error checks. My newest project is creating a report so I can check account numbers for accuracy.

For example if a vendor has an account number like so "12-2254569891-215635489" 20 digits long including dashes.

but

if someone keys in a 18 digit account number then crystal would display this information so I could then correct the mistake or have the intake agent correct it.

here is how I started the formula...

(LEN({vwAVEAPClaims.AccountNmbr}) = 20 OR
ISNULL({vwAVEAPClaims.AccountNmbr}))
OR

but I have since learned that is should be closer to this...

IF (LEN({vwAVEAPClaims.AccountNmbr}) = 20 OR
ISNULL({vwAVEAPClaims.AccountNmbr})) then
TRUE
ELSE
FALSE

but I am stuck... here is the whole fomula I have but it displays the account numbers that match the lenth of 20 and doesn't show the incorrect data.

{vwAVEAPApplications.ProgramYear} = 2010 and
{vwAVApplications.ApprovedForEAP} and
(
(LEN({vwAVEAPClaims.AccountNmbr}) = 20 OR
ISNULL({vwAVEAPClaims.AccountNmbr}))
OR
(TRIM({vwAVEAPClaims.VendorName}) = "vendor name" OR
ISNULL({vwAVEAPClaims.VendorName}))
)

I hope anyone could help...

 

[CoSpringsGuy]

If your task is to find all account numbers that are not a length of 20 ... why dont you use

LEN({vwAVEAPClaims.AccountNmbr}) <> 20

in your record selection criteria?

if you also want empty account numbers add

and
isnull({vwAVEAPClaims.AccountNmbr}) = true


_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[nathanwiley]

Thank you for your prompt response, however I would like to find the account numbers that should be 20 digits in length, but arn't. I would also like to only show specific vendors,

for example vendor 1 has 20 digit account numbers whereas vendor 2 only has 9.

 

[CoSpringsGuy]

LEN({vwAVEAPClaims.AccountNmbr}) <> 20

in your record selection criteria?

if you also want empty account numbers add

and
isnull({vwAVEAPClaims.AccountNmbr}) = true
and
{vwAVEAPClaims.VendorName} = "vendor name"

you could create a multiple value parameter for vendor name as well in case there are a lot that are supposed to be 20 characters.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[nathanwiley]

Thank you, that worked... already found error!

 

[nathanwiley]

here is the code....

(
(LEN({vwAVEAPClaims.AccountNmbr}) <> 20 or
ISNULL({vwAVEAPClaims.AccountNmbr})) = TRUE
) and
{vwAVApplications.ApprovedForEAP} and
{vwAVEAPApplications.ProgramYear} = 2010 and
{vwAVEAPClaims.VendorName} = "Vendor Name" and
{vwAVApplications.dteApplication} >= DateTime (2009, 10, 08, 12, 00, 00)

 

[lbass]

No, you MUST always do the null check first--BEFORE the field being checked is referenced with a value, as in:

(
ISNULL({vwAVEAPClaims.AccountNmbr}) or
LEN({vwAVEAPClaims.AccountNmbr}) <> 20
) and
{vwAVApplications.ApprovedForEAP} and
{vwAVEAPApplications.ProgramYear} = 2010 and
{vwAVEAPClaims.VendorName} = "Vendor Name" and
{vwAVApplications.dteApplication} >= DateTime (2009, 10, 08, 12, 00, 00)

You do not need to expressly use "true".

-LB

 

[nathanwiley]

Thank you very much... I tested and it works great...

 

[nathanwiley]

iS IT POSSIBLE TO INCORPORATE ALL VENDORS IN A FORMULA AND ACCOUNT NUMBER LENGTH... I WORK IN CAPS SORRY

 

[lbass]

I don't know what you mean--you need to provide more specifics.

You should take the time to use proper case here, as people perceive caps as yelling.

-LB

 

[nathanwiley]

I know, about the yelling thing, but I had typed it all out prior to catching myself...

but in more specifics, I have lets say 10 vendors who each have a different style account number, some may be 7 and some may be 9 etc... is it possible to incorporate all vendors and variables into the formula to make one report so I don't have to have a report for each vendor.

I appreciate your help, and response.

 

[lbass]

Create a parameter for the vendor and then change your formula to something like this:

(
ISNULL({vwAVEAPClaims.AccountNmbr}) or
(
select {?Vendor}
case "Vendor ABC" : LEN({vwAVEAPClaims.AccountNmbr}) <> 20
case "Vendor DEF" : LEN({vwAVEAPClaims.AccountNmbr}) <> 9
case "Vendor GHI" : LEN({vwAVEAPClaims.AccountNmbr}) <> 7
)
) and
{vwAVApplications.ApprovedForEAP} and
{vwAVEAPApplications.ProgramYear} = 2010 and
{vwAVEAPClaims.VendorName} = "Vendor Name" and
{vwAVApplications.dteApplication} >= DateTime (2009, 10, 08, 12, 00, 00)

-LB

 

[nathanwiley]

Outstanding... Thank you SO Much... !

 

[nathanwiley]

I am in need of some more help... I need to make a report showing comparable data from previous years. We need to gather data showing an increase or decrease of income on the same report. Is this possible and this is what I have so far...

I know its wrong but I can't think of for the life of me, I didn't want to input income since everyone has different income level, some people have zero and some have 50 grand.

I also wanted to know if its possible to show both amounts on the report for example...

family member a made $3000 in 2008 and $6000 in 2009 which is an increase by $3000.

This is for a ROMA report -- anything you can help with would be appreciated. Thanks

(
ISNULL({vwAVMembersAll.Income}) in $161.72 to $47921.60 or
LEN({vwAVMembersAll.Income}) <> 20
) and
{vwAVApplications.ApprovedForEAP} and
{vwAVEAPApplications.ProgramYear} in 2008 to 2009

 

[lbass]

I don't think you should reference income in your selection statement at all. Insert a group on the family member field, and then create two formulas:

//{@2008}:
if {table.progyear} = 2008 then {table.income}

//{@2009}:
if {table.progyear} = 2009 then {table.income}

Place these in the detail section and insert maximums on them at the group (family member) level. Then suppress the detail section.

Not sure why the income level comparisons are important for an error report though.

-LB

 

[nathanwiley]

This isn't for an error report it is a different report that I didn't know to post new topic.. Thank you for your response.

 

[lbass]

At the upper left above the thread list, you will find "start new thread."

-LB