not like

[ksnpc]

Hi,
I'm using CR 9 connected to an ODBC database. I have a memo field where the data string should always begin with a year. I'm trying to find records that don't follow that format so they can be corrected.

I've tried to use the the formula editor to select records that do not begin with '18*', '19*' or '20*' (I don't think I have any records prior to the 1800s). I can't figure out how to get it to work. Does anyone have any suggestions?

Thanks,
Shauna

 

[synapsevampire]

Use the Report->Edit Selection Formula-Record and try something like:

{table.field} startswith "18"
or
{table.field} startswith "19"
or
{table.field} startswith "20"

-k

 

[ksnpc]

Thanks!

 

[kskid]

If you are looking for records that do not start with 18, 19, or 20, then

not({table.field} startswith "18") and
not({table.field} startswith "19") and
not({table.field} startswith "20")


or

not(left({table.field},2) in ["18","19","20"])

Cheers,
-LW

 

[ksnpc]

I used:
not ({EO.EO_DATA} startswith "17" or {EO.EO_DATA} startswith "18" or {EO.EO_DATA} startswith "19" or {EO.EO_DATA} startswith "20")

but I think it was your bottom solution that I was attempting earlier with no luck - I believe it was because I was trying to use wildcards. Thanks!

 

[kskid]

Is this a mandatory field? If not, then you will have to handle null condition, too.
(
isnull({EO.EO_DATA}) or
not(left({EO.EO_DATA},2) in ["18","19","20"])
)

Cheers,
-LW

 

[synapsevampire]

Wildcards can work too, and might prove the fastest for some databases:

not({table.field} like "18*")
and
not({table.field} like "19*")
and
not({table.field} like "20*")

Sorry I missed that do NOT exist, but it appears that you figured it out.

-k