Conflict with Null formula

[CrystalBox]

Hello Tek-Tip experts. I have an ACCESS database and use Crystal 11; I'm using this formula to quiry records that have a "null value" under the "Classification" field. I'm using this formula to accomplish that:

({EXP_INTAKE.INTAKE_NUMBER} >= "10-001") and isNull({EXP_CLASSIFICATION.CLASSIFICATION}) or {EXP_CLASSIFICATION.CLASSIFICATION}=''

The formula worked fine until I got a record that mystified me. The formula returned a record with data in the field that was supposed to be blank (null). The record looked something like this:

Intake number Classification Date

10-001 A 11/01/2010
B 11/10/2010
(blank) (blank)

I looked into the database records and noticed that someone had inadvertently place a blank space probably by hitting the return key (example above). Apparently my formula is acting on that blank row.

I know that a a quick remedy would be to delete the blank row, but since I'm just a user and not the manager of the database, I would have to go through some red tape to do so. Is there some way I could modify my quiry to resolve this issue?

Thank you

 

[lbass]

You can correct the blank by using trim, but you also need parens to return the correct data like this (I removed the unnecessary ones):

{EXP_INTAKE.INTAKE_NUMBER} >= "10-001" and
(
isNull({EXP_CLASSIFICATION.CLASSIFICATION}) or
trim({EXP_CLASSIFICATION.CLASSIFICATION})= ""
)

I would worry about the intake number criterion unless the field is always formatted the same "##-###".

-LB

 

[Turkbear]

Hi,
Use the Trim function to eliminate the extra space:

{EXP_INTAKE.INTAKE_NUMBER} >= "10-001"
 AND
(
 isNull({EXP_CLASSIFICATION.CLASSIFICATION})
OR
Trim({EXP_CLASSIFICATION.CLASSIFICATION}) = ""
)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CrystalBox]

Thanks guys. I tried it with the Trim function but it didn't work. One thing I didn't consider before asking you for help was the "{EXP_CLASSIFICATION.DATE}" field, which didn't matter until now. I'm guessing the date field has to be included in the formula as well, since it also has blank space. I'm just not sure of the syntax.

Thanks

 

[lbass]

Please post the updated selection formula so we can see what you are doing. Please include the entire formula.

-LB

 

[CrystalBox]

Actually I don't have an updated formula. I just thought that your formula would work with the inclusion of the
"{EXP_CLASSIFICATION.DATE}" field in your formula. I'm just not certain about the sytax order.

 

[lbass]

The order doesn't really matter much, but the parens do--please use them as both Turkbear and I showed. If you want more help, you need to show the actual formula.

-LB

 

[CrystalBox]

Thanks guys for your quick response. I now realize that I'm dealing with three fields:

Classification Change Classification Date

To test my suspicion on the blank field, I filled in the blank row with junk info and refreshed my report and the record in question was removed.

So back to the formula. I tried this formula but had problems getting past the "classification_date" field. I'm guessing it because it's not a string field. I'm not sure how to get around this.

{EXP_INTAKE.INTAKE_NUMBER} >= "10-001" and
(isNull({EXP_CLASSIFICATION.CLASSIFICATION}) or
Trim({EXP_CLASSIFICATION.CLASSIFICATION})= "" and Trim({EXP_CLASSIFICATION.CHANGE})=""
and trim({EXP_CLASSIFICATION.DATE}))

 

[Turkbear]

Hi,
Check your systax for the red part:

{EXP_INTAKE.INTAKE_NUMBER} >= "10-001" 
and
(
isNull({EXP_CLASSIFICATION.CLASSIFICATION})
 or
Trim({EXP_CLASSIFICATION.CLASSIFICATION})= "" 
and Trim({EXP_CLASSIFICATION.CHANGE})=""
and [COLOR=red] trim({EXP_CLASSIFICATION.DATE})[/color]
)

I do not think you trim a Date field and even if a string you are not giving it a test for anything ( like Trim(field) = "")

Try

{EXP_INTAKE.INTAKE_NUMBER} >= "10-001" 
AND
( [COLOR=green]//first part of compound AND/OR statement[/color]
isNull({EXP_CLASSIFICATION.CLASSIFICATION})
OR
([COLOR=green]//first part of compound AND within OR[/color]
Trim({EXP_CLASSIFICATION.CLASSIFICATION})= "" 
AND 
Trim({EXP_CLASSIFICATION.CHANGE})=""
AND 
NOT IsDate({EXP_CLASSIFICATION.DATE})
)([COLOR=green]//END of AND part of OR statement[/color]
)[COLOR=green]//END of main AND/OR

Double check the parens, they are critical for proper evaluation of the criteria ( I mave have lost track)



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CrystalBox]

I used your modified formula without the comments and I got the error message "a string is required here" on the
NOT IsDate({EXP_CLASSIFICATION.DATE}))field.

{EXP_INTAKE.INTAKE_NUMBER} >= "10-001"
AND
(isNull({EXP_CLASSIFICATION.CLASSIFICATION})
OR
Trim({EXP_CLASSIFICATION.CLASSIFICATION})= ""
AND
Trim({EXP_CLASSIFICATION.CHANGE})=""
AND
NOT IsDate({EXP_CLASSIFICATION.DATE}))

 

[Turkbear]

Hi,'
Sorry about that, I used the wrong function to test for NULL dates, try, instead

{EXP_INTAKE.INTAKE_NUMBER} >= "10-001" 
AND
( 
isNull({EXP_CLASSIFICATION.CLASSIFICATION})
OR
(
Trim({EXP_CLASSIFICATION.CLASSIFICATION})= "" 
AND 
Trim({EXP_CLASSIFICATION.CHANGE})=""
AND 
(
IsNull({EXP_CLASSIFICATION.DATE}) OR  {EXP_CLASSIFICATION.DATE}) = date(0,0,0)
)
)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
as with my other post, please correct the parens - this one is extraneous ( cut and paste is not always an exact science):

IsNull({EXP_CLASSIFICATION.DATE}) OR {EXP_CLASSIFICATION.DATE}) = date(0,0,0)




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[CrystalBox]

Thanks Turkbear. I tested each statement of the formula and it worked until I got to

"OR {EXP_CLASSIFICATION.DATE}) = date(0,0,0)"

I get the error message:

"The remaining text does not appear to be part of the formula

 

[fisheromacse]

it appears you need remove the ")" after .DATE}

 

[CrystalBox]

Thanks fisheromacse, you are correct, the formula got validated, but the formula didn't fix my problem. My original formula worked every time, EXP_INTAKE.INTAKE_NUMBER} >= "10-001") and isNull{EXP_CLASSIFICATION.CLASSIFICATION}) or {EXP_CLASSIFICATION.CLASSIFICATION}='', until a user entered the return key after posting a record and created a blank row. The example is repeated below.

Hey guys I really appreciate the help you've given me in the past and know that you are busy helping other folks, so I understand if my problem doesn't get resolved. Thanks guys, Have a Happy Thankgiving.


Intake number Change Classification Date

10-001 A 11/01/2010
B 11/10/2010
(blank) (blank) (blank)