X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Calculated Field Set To Short Field Showing As Memo Field In Crystal Reports, Access 2013    
post Jul 15 2019, 10:53 AM

Posts: 67
Joined: 14-June 18

Looking for help.

I have set up a OLE DB (ADO) connection to my Access DB from Crystal Reports. In my Access DB I have a several tables that use calculated fields to combined fields together. Example: "FirstName" field and a "LastName" fields are combined in a calculated field "FullName"

All the calculated fields are set to Short Text as the result type.

My issue is that the Crystal Report application see these fields as MEMO or Long Text fields and many of the abilities to filter and group them are lost.

I looked extensively on Crystal Reports forms and the experts say there is nothing CR can do to see the field differently. It's the way it's presented via the Access DB/OLE DB connection - if the fields allow or show more then 255 characters CR sees it as a MEMO field.

Anyone in here have any ideas on how I can insure Access displays calculated fields as short text strings via a OLE DB (ADO)?

This post has been edited by fitzdesignz: Jul 15 2019, 11:08 AM
Go to the top of the page
post Jul 15 2019, 01:18 PM

Posts: 130
Joined: 28-March 18
From: Virginia

I don't use Crystal Reports, and don't use calculated fields either (preferring to do this type of concatenation in a query).

I think, with a query, you could use:
SELECT FirstName, LastName, Left(yourTable.FullName & "", 255) as FullName
FROM yourTable

then Crystal Report might see it as only 255 characters (max) and treat it as short text.

I don't use CR, so I cannot tell you if this will work or not.

Also, how big are you FirstName and LastName fields? If you shorten them to no more than 50 characters each, does C&R still treat FullName as memo?

Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
post Jul 17 2019, 10:42 AM

Posts: 67
Joined: 14-June 18

Hi dale.fye,

Thanks for your input.

Unfortunately using the query didn't work.

I do have some more insights:

I added my access db connection to crystal reports via the ODBC (RDO) data source by manually creating a connection in the Microsoft ODBC Data Source Admin utility.
With this connection all my access DB fields are seen as I expected (GREAT!). However, my issue with this is that when distributing the CR reports to end users it requires all local machines to also have the connection set up in the Microsoft Data Source Admin utility. This would be a pain to implement across 25+ machines.

So, I've narrowed my issue down to the type of connection I'm using in to connect my Access DB to Crystal reports. I originally thought this was a table setup/setting issue within my Access DB.

I have two options:
1- Use the Microsoft Data Source Admin utility. Maybe create a script to automate the setup. (Less ideal)
2- Figure about another connection type that works nativity with Crystal Reports to connect to Access DB (.accdb) Which I'm hopeful someone in this forum has some insights on.
Go to the top of the page
post Jul 17 2019, 05:51 PM

Posts: 67
Joined: 14-June 18


I figured it out!

To create a connection from Access .accdb to Crystal Reports - Do the following:

In Crystal reports:
Create a new OLE DB (RDO) connection
Select “Enter Connection String”

Enter the following as the connection string:

Without Password:
Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\mydatabase.accdb;

With Password:
Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\mydatabase.accdb; Uid=Admin;Pwd=123456;

BOOM! Works beautify. I feel like I should create a help document somewhere for this. I been searching the web for an answer for almost 3 weeks now.
Go to the top of the page
post Jul 18 2019, 12:48 AM

UtterAccess VIP
Posts: 1,540
Joined: 4-June 18
From: Somerset, UK

Congratulation on finding the answer to your problem.

Too late now but that info and much else besides is available online at https://www.connectionstrings.com/microsoft...db-odbc-driver/

I recommend that site for any question about connection strings.

Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    18th August 2019 - 10:24 AM