Substr Function

Forum|Forum|10 years ago
June 23, 2015
16 replies
6671 views

I have a script that "walks" through all the records in an Access database table to find the largest file ID number. This script has been working correctly. Now I need to add a prefix to all the ID numbers. This requires me to modify my script so it ignores the one character prefix (prefix is either a "T" or a "Y"). I was trying to use the Substr function to accomplish this.

This is the original script that works:


while(!oDB.isEOF()){

 if(xfa.record.DataConnection1.FileName.value > nMaxID)

  nMaxID = Number(xfa.record.DataConnection1.FileName.value);

 oDB.next();

}

Below is how I tried to modify the script above to ignore the prefix but I get an error that says Substr is not defined. What have I got wrong?


while(!oDB.isEOF()){

 if(xfa.record.DataConnection1.FileName.value > nMaxID)

  nMaxID = Number(Substr(xfa.record.DataConnection1.FileName.value,2,12);

 oDB.next();

}

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

D

DKinsleyAuthor

New Participant

Thank you. I will give it a try. My concern is that the vValue will not find the largest number in the database column when each value starts with text ("T" or "F"). Example T1012 or F1013

radzmar

New Participant

Here'e the complete script I was thinking of.


while (!oDB.isEOF()) {   

  if (xfa.record.DataConnection1.FileName.value > nMaxID) {

       var vValue = xfa.record.DataConnection1.FileName.value;

       nMaxID = Number(vValue.substr(1,12));

  }

  oDB.next();

}

D

DKinsleyAuthor

New Participant

Thanks for your help. When you can, please post what your script should look like.

Thank you

radzmar

New Participant

Yes, the code got a bit flawed by copy and paste at my mobile I'm afraid :-O

D

DKinsleyAuthor

New Participant

I am mistaken. The script above is still not working correctly.

radzmar: your script does not include "substr" - is it missing or can I use Number(vValue, 2, 5) the same way as substr ?

D

DKinsleyAuthor

New Participant

This is now working. Thank you to all the responders for helping me solve this issue.

This works:


while(!oDB.isEOF()){

 if(xfa.record.DataConnection1.FileName.value > nMaxID)

  nMaxID = Number(xfa.record.DataConnection1.FileName.value.substr(1,12));

  Test.rawValue = nMaxID;

 oDB.next();

}

Thank you!

D

DKinsleyAuthor

New Participant

radzmar,

Your script does not have substr in it? Is it missing?

The script is going out to the database and looking for the largest number. The numbers start with either "T" or "Y" so I need to have the script ignore the first text character and only look at the remaining numeric digits. I would thing the substr would need to be run before the value is saved in a variable.

radzmar

New Participant

I'm just quessing because I cannot create a similar scenario at my end right now, but you should try to store the records value in a variable before you apply the substr() method.


while(!oDB.isEOF()){  

if(xfa.record.DataConnection1.FileName.value > nMaxID) 

var vValue = xfa.record.DataConnection1.FileName.value;

nMaxID = Number(vValue, 2,12);  

oDB.next(); 

}