Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Start position less than 1 error Post Reply Post New Topic
Page  of 2 Next >>
Author Message
mabrown
Newbie
Newbie


Joined: 08 Jun 2007
Online Status: Offline
Posts: 27
Quote mabrown Replybullet Topic: Start position less than 1 error
    Posted: 21 Sep 2017 at 7:19am
We have 2 databases with the same data in them (one was converted to the other.)  Formula 1 works and formula 2 returns the error "Start position less than 1 or not an integer".  Any ideas why the second formula does not work??

1. Varchar(100) null  
If {inventory_master.web_base_sku} < '09000' Then
    (If trim(replace(Mid({inventory_master.description},instr({inventory_master.description},'Pkg'),20),"Pkg"," ")) startswith "/"
        then Tonumber(replace(trim(replace(Mid({inventory_master.description},instr({inventory_master.description},'Pkg'),20),"Pkg"," ")),"/"," "))
        else Tonumber(trim(replace(Mid({inventory_master.description}, instr({inventory_master.description},'Pkg'),20),"Pkg"," "))))
Else 0

2. Varchar (120) not null
If {INVENTOR.ITEM_ID} < '09000' Then
    (If trim(replace(Mid({INVENTOR.ITEM_DESC},instr({INVENTOR.ITEM_DESC},'Pkg'),20),"Pkg"," ")) startswith "/"
        then Tonumber(replace(trim(replace(Mid({INVENTOR.ITEM_DESC},instr({INVENTOR.ITEM_DESC},'Pkg'),20),"Pkg"," ")),"/"," "))
        else Tonumber(trim(replace(Mid({INVENTOR.ITEM_DESC}, instr({INVENTOR.ITEM_DESC},'Pkg'),20),"Pkg"," "))))
Else 0
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Sep 2017 at 7:57am
I am not sure it is the formula as much as the data it is evaluating on.
does every row in the {INVENTOR.ITEM_DESC} field have "Pkg"?
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Sep 2017 at 7:59am
also note that using < on a string may not be getting you want you think or intend...
'1' is > '09000'
IP IP Logged
mabrown
Newbie
Newbie


Joined: 08 Jun 2007
Online Status: Offline
Posts: 27
Quote mabrown Replybullet Posted: 21 Sep 2017 at 8:06am
No not every record has PKG but remember the first formula works WITH THE SAME DATA and returns the data we are looking for.  What could be different about the database or structure that returns the error on the second formula?

Edited by mabrown - 21 Sep 2017 at 8:07am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Sep 2017 at 8:59am
Is it exactly the same data set?

I didn't break it down entirely but the "Start position less than 1 or not an integer" is likely referring to once of the mid() functions.
I would split each into a different formula and run them to see which one breaks.
Also is each formula set to handle nulls the same way?
IP IP Logged
mabrown
Newbie
Newbie


Joined: 08 Jun 2007
Online Status: Offline
Posts: 27
Quote mabrown Replybullet Posted: 21 Sep 2017 at 10:03am
I checked the table fields for null and Crystal did not report any null records and the fields are set to not null in the table structure that is not working.  (The one that works allows nulls....)
This is the section that fails Mid({INVENTOR.ITEM_DESC},instr({INVENTOR.ITEM_DESC},'Pkg'),20)

OK maybe there is another way to get what we want.  We want to get the pkg size so we can use it in another formula.
Data
XYZ Widget Pkg/20
abc widget Pkg/6
TTT widget Pkg/100
KKK widget



Edited by mabrown - 21 Sep 2017 at 10:04am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Sep 2017 at 10:24am
basically you want the 'number' at the end of the string only when the string also has 'Pkg' or ' Pkg/' otherwise dummy in a zero.
Correct?
IP IP Logged
mabrown
Newbie
Newbie


Joined: 08 Jun 2007
Online Status: Offline
Posts: 27
Quote mabrown Replybullet Posted: 21 Sep 2017 at 10:32am
Yes
IP IP Logged
kevlray
Admin Group
Admin Group
Avatar

Joined: 29 Oct 2009
Online Status: Offline
Posts: 1587
Quote kevlray Replybullet Posted: 21 Sep 2017 at 1:29pm
if instr( {INVENTOR.ITEM_DESC}, 'Pkg/') > 0 then
right( {INVENTOR.ITEM_DESC}, instr( {INVENTOR.ITEM_DESC})+4)
else instr( {INVENTOR.ITEM_DESC}, 'Pkg') > 0 then
right( {INVENTOR.ITEM_DESC}, instr( {INVENTOR.ITEM_DESC})+3)
else
'0'

I think this covers the bases correctly (not tested). If you need a numeric value (i.e. for math operations), then convert each string value to a number (i.e., CDbl function).

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 22 Sep 2017 at 9:51am
agreed with kevlray, although I think the instr() is missing the search text for both the THEN parts.
Also other things to consider, you can use InStrRev() instead to look for the strings in from right to left in case 'pkg' is used in another capacity in the string and it mixes up a row. Also your original process was using a trim in case these items might have lead or trailing spaces that would confuse the process.
IP IP Logged
Page  of 2 Next >>
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.