Error: "A memo field can't be used in a formula."

[rcrei]

I am working on debugging a report that used<br>to work. I think it must have stopped working<br>after our upgrade Crystal 5 to Crystal 6 as part of PeopleSoft upgrade. (Not sure how it got through our testing.)<br><br>The report uses ODBC access to a comma<br>separated file. When we use the tonumber<br>function to convert text to number, we get the error&nbsp;&nbsp;&quot;A memo field can't be used in a formula.&quot; .&nbsp;&nbsp;It appears that we get it for<br>all fields fields that we try to convert. <br><br>A strange sympton that I have noticed is that<br>if you do insert field - all the fields are listed, but if you try to create a new formula the fields are not listed only the<br>name of the mapping. I'm wondering if this<br>signifies the problem.<br><br>I think there is something wrong with the<br>ODBC mapping, but can't find anything. <br><br>Also, I can't find any description of what<br>a memo field is. If someone could help me with just that I would appreciate it.<br><br>Any ideas or suggestions for continued debugging would be appreciated. <br><br>Hope someone out there has an idea!! Thanks in advance. <br><br>

 

[MalcolmW]

The file definition for your text file probably has defined the fields as having more than 254 characters.&nbsp;&nbsp;String fields of this size and larger are treated as &quot;memo&quot; fields in Crystal Reports, in all versions.<br>Memo fields cannot be manipulated in formulas, and thus don't appear in the list of available fields in the formula editor.<br>You will need to redefine the fields in your ODBC mapping to get around this issue - then life will be rosy again.<br>

 

[rcrei]

<br><br><br>&nbsp;&nbsp;&nbsp;MalcolmW - Thanks so much for your quick response.<br><br>&nbsp;&nbsp;&nbsp;Using the ODBC control panel, I changed the size of the fields being used in the<br>&nbsp;&nbsp;&nbsp;formulas, but it didn't work. Is there anything else that I have to do to make<br>&nbsp;&nbsp;&nbsp;sure that that Crystal sees the change in size? Is there any way that I can<br>&nbsp;&nbsp;check what size that Crystal sees it as?<br>&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;Also, it looks like when we use the GUESS function all the fields in our CSV (comma<br>&nbsp;&nbsp;&nbsp;separated files) are set to 255. I reviewed the other crystal reports that have used<br>&nbsp;&nbsp;this method and I can find one where it works (255 characters fields used in&nbsp;&nbsp;&nbsp;formulas). Any ideas on how that would work.<br><br>&nbsp;&nbsp;&nbsp;I didn't develop this report so I feel a little in the dark so any of your help will be<br>&nbsp;&nbsp;appreciated.

 

[MalcolmW]

I haven't worked with text fields in a long time - and when I did, I didn't have any serious problems.<br>If you create a new report using the text file as a datasource, can you see the fields in the formula editor?<br>(determine whether the report is corrupt, or the file is the problem)<br><br>If you take just a few rows of the text file, and use that as a datasource, can you see the fields in the formula editor (maybe there are some rows in the file that are improperly defined)?<br>Have you tried changing to some other delimter other than commas, or going with fixed width?<br>Other than that, I'm stumped.&nbsp;&nbsp;<br>Regarding 255 verses 254, Crystal says that 254 is limit for non memo fields.&nbsp;&nbsp;If you have a 255 char field, in the formula editor, highlight the field, and right click on browse.&nbsp;&nbsp;You should get the field definition including the length.&nbsp;&nbsp;Does it say 255?&nbsp;&nbsp;If it does, you'll have proved Seagate has been wrong.&nbsp;&nbsp;If it says 254, then where did the extra character go? (perhaps it is the comma)

 

[rcrei]

Thanks for the additional ideas.

I tried to take the report that works (even though I'm not sure why) and look
at the field length. I got into the formula editor. When I highlight the field
and do Browse data field. I get &quot;ODBC error:&quot; with no error text then it tells me
that the field is string 255.

In the report that is not working, I changed the ODBC definition and can see
the SCHEMA.INI file are set to having the fields be 254, but still can't get these
seen in the formula editor.

Just to add more confusion to the situation, one user just returned from vacation and the report works from her desktop!! I tried to check her ODBC configuration, but
the control panel for ODBC was missing (yuk!) Anyway, I am going to do more playing
on her machine once we get the reports we need now printed - I'm afraid to break
something!!

Here are my latest questions:

- Can you think of anything that would differ based on different user installations? Should the ODBC setting that point to a common directory and SCHEMA.INI all be
the same.
- Do you have to anything to get a report to see a change in an INI file?


Thanks for your patience and your help.

Rosemary

 

[MalcolmW]

To the last question, which might be the most fruitful to pursue, Database|Verify Database will cause CR to reload the database structure. Otherwise it assumes it has the correct one. Sorry, I should have mentioned this, as any changes to the structure will not be recognized by CR, unless the Database|Verify on every print option is selected, or the Database|Verify command is executed.

 

[rcrei]

Malcom since you have been so helpful, I wanted to let you know where
I stand at the moment. I got the following information from Seagate:

SCR gets the type, length, size, etc field info by using SQLColumns call to
ODBC.

Up to SCR 6, the MS ODBC driver (ODBCJT32.DLL) used to return information
(after an SQLColumns call) in a certain format: ?column Size, Column
Length, ?

What happened, is that after MDAC 2.1 the MS has modified the ODBC driver
and implemented double bite support. Therefore the info returned by an
SQLColumns call will be slightly different in that the information is
shifted by one column. What happens is that If you upgrade the ODBC driver
to anything past MDAC 2.0 (2.1, 2.5 etc) and still use SCR 6, SCR 6 will
read the column length info and believe it is in fact the column size info.
Therefore, we basically end up with a maximum &quot;non-memo&quot; size of 127
characters (2x127 = 254 which is the max number before 255).

In version 7 of SCR, P2SODBC has been modified to properly read the correct
info from what the new ODBCJT32.DLL returns to an SLQColumns call.

Based on this, I changed the length of fields to 127 and it got rid of the original
problem, but now I get a blank ODBC error followed by SQL server error.

Anyway, I thought the above information would be interesting to you.

Thanks again for all your help.

 

[MalcolmW]

Rosemary,
thanks for posting that - that is a strange one.
I never had much faith in v6, although I guess it could be argued this is a Microsoft induced problem. I kinda jumped from v4 to v7, in terms of ones I used on a production basis. V8 is fairly good too, so you might want to consider an upgrade (your tolerance for upgrades may be a bit stretched by now though).
cheers,
Malcolm