David,
 
I am not sure I understand exactly what you are after, but could you use a substring in your database view? I did not create the view below, but the Substring suffixterm is pulling characters 3-99 from vDataViewThesXrefs.Term.
 
CREATE VIEW dbo.DU_ExhObjThemes
AS
SELECT     dbo.vDataViewThesXrefs.ID AS exhobjxrefid, dbo.vDataViewThesXrefs.Term, SUBSTRING(dbo.vDataViewThesXrefs.Term, 1, 1) AS prefixterm,
                      SUBSTRING(dbo.vDataViewThesXrefs.Term, 3, 99) AS suffixterm, dbo.ExhObjXrefs.Remarks, dbo.ExhObjXrefs.SequenceNumber,
                      dbo.ExhObjXrefs.CatalogueNumber, dbo.ExhObjXrefs.CaseNumber AS AlternateListOrder
FROM         dbo.vDataViewThesXrefs INNER JOIN
                      dbo.ExhObjXrefs ON dbo.vDataViewThesXrefs.ID = dbo.ExhObjXrefs.ExhObjXrefID
WHERE     (dbo.vDataViewThesXrefs.ThesXrefType = 'Theme') AND (dbo.vDataViewThesXrefs.TableID = 49) 
 
Josh
 

Josh Meehan
TMS Application and Database Administrator
The Solomon R. Guggenheim Museum
345 Hudson Street, New York, NY, 10014
212-423-3669
[log in to unmask]


From: The Museum System (TMS) Users [mailto:[log in to unmask]] On Behalf Of David Armstrong
Sent: Friday, February 20, 2009 5:53 PM
To: [log in to unmask]
Subject: Crystal Report question

I know that there are a lot of Crystal Reports wizards out there and I hope that one of you can help me out.  I am trying to create a formula that returns the value of a string without the first three characters.  As far as I can tell, I need to write a function something to the effect of…

 

Dim Status As String

Dim StatusLength as Number

Status = "{CnPrProp_1.CnPrProp_1_Status}"

StatusLength = len({CnPrProp_1.CnPrProp_1_Status})

formula = Status (right("{CnPrProp_1.CnPrProp_1_Status}", StatusLength-3)

 

Basically what I’m trying to do is use the “right” function to pull the number of characters returned to by the len() evaluation of the string, less three (to drop the first three characters).  I’m not sure about the proper syntax, or the correct way to actually create the function in Crystal.  I am using Crystal XI.  Any tips?  Thanks in advance!

 

 

 

 

David Armstrong

Database Administrator

MOCA  THE MUSEUM OF CONTEMPORARY ART