You are here:

Using MS Access/Access 2007 - Report doesn't display data as in query

Advertisement


Question
Hi Julie,

I have an access query to retrieve an unbound field called "Cdate" which has both Datevalue (like 41230) as well as Text (like "NA"). I need to convert the datevalue to Date and retain rest of text as text. I am able to get the date using query:

CDATE FIELD:

IIf(db_cm.[cDate]="NA",[db_cm].[cDate],CStr(CDate(oildb_cm.[cDate]))) AS [cDate]

But, I'm unable to get the same in Access Report. It is sameI tried using the same code in Control Source of the field, but the report is giving as #ERROR in that field.

Please advise what criteria has to be given to get the date value as date and text value as text in the same field.

Answer
Hi Gopinath,

Thanks for the question.

Try the following ( have given you two options to see if either suits your scenario:

1)

Add an unbound text box to your report

Then in that textbox put an = sign, and then your code above, but remove the reference to the table.  It should still work as long as the report is based on the table.

Therefore your unbound textbox should look like the following:

=IIf([cDate]="NA",[cDate],CStr(CDate([cDate])))

or 2)

Set up the cDate field in the query just like you have, then base the report on that query.  Then the control source for the field would be the name you have given the field in the query, not the whole formula.

Let me know if neither of these work for you.  

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Julie Misson

Expertise

My area of expertise is Microsoft Access 2000-2007. I have been building microsoft databases for the past twelve years. I would be competent in answering questions from novice Access 2000 users to the more advanced including VBA. If I cannot help, I am more than likely be able to point you to websites that can.

Experience

I have a Microsoft Access business where I build database for small businesses. These are usually one off designs, where there is no off the shelf software avaliable to meet the business needs. I also teach Microsft Access to beginners. I am the owner of the www.simply-access.com website.

Education/Credentials
Self taught in Microsoft Access. Have done some units in normalisation and SQL, but most of what I know I have learnt from books originally and more recently the Internet.

©2016 About.com. All rights reserved.