Showing a substiute value in a report if conditions are met – Access

70 pts.
Tags:
Access 2003
Microsoft Access
Reports
In one of my tables, there is a date field which shows dates of an event happening (called "Issue Date"). This gets input through various measures. Input form or directly into the table or query. One of the reports I am trying to design is a report that will show all materials that have been issued. This date field is one of the field which I want to show in the report but I don't want the result to be the date itself. I want the report to replace the date (what ever the date might be) with the value "ISSUED". Seems like a simple task but I am pulling my hair out trying to figure it out. Can you offer any help?

Answer Wiki

Thanks. We'll let you know when a new response is added.

Use expression instead of field.
For example,

<pre>issued: IIf([Issue Date] Is Not Null;’ISSUED’)</pre>

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BrentSheets
    Moderator Note: Hello Gbullo and welcome to IT Knowledge Exchange. When you post a question, it's important to list the software or product involved. All you mentioned is "reports" when could be applicable to many products. On future questions, please provide more details and provide also applicable tags to help place your question in the proper category. You can add details to your question by clicking the "Add to Discussion" button and providing more information. Thanks!
    6,925 pointsBadges:
    report
  • Gbullo
    Brent, Sorry about not providing more details. Thanks for the reminder. I am using Access 2003. Thanks, Gbullo
    70 pointsBadges:
    report
  • SbElectric
    I did not fully understand the report format. Can you post a dummy report layout. Just two lines of report format will make it easy for me to understand your requirements. Thanks.
    2,540 pointsBadges:
    report
  • Gbullo
    SbElectric, I haven't designed anything yet. I am trying to create a report in Access. This is a Material Control / Inventory Control system I am trying to design. Very simple and not too much fluff. All the data entry and maintenance will be preformend through the back end and there will not be any users other than myself and one other person. Everyone else will be receiving status reports One of the reports I am having troubles with is a report showing all my inventory and making sure I can point out all materials that have been issued. In one of my tables, I have a column called "Issued" and the data type is "Date" This date will be manually entered with the issue date once the item has been issued. What I want to do is run a material status report, showing all my inventory and in the Issued column on the report, I want to show one of two things. Blank or the word "Issued" I will have other reports which I will want to show the actual Issued date for those items. That one is easy. It's the report showing something different than the entered data that I am having problems with and I'm not sure where to start. The design of the query or the design of the report and what expression to use. Hope that gives you a little more detail. Thanks for any help you can offer, George
    70 pointsBadges:
    report
  • tlsanders1
    How about adding a column to the query - something like IssuedYesNo: IIF(issued>0,"Issued","") So, if Issued has a date in it, IssuedYesNo will have the string "Issued". If Issued does not have a date in it, IssuedYesNo will be an empty string. Then put that IssuedYesNo in the report.
    1,340 pointsBadges:
    report
  • SbElectric
    Yes, Tisanders1 has provided an easy and solution by using IIF function. Please let us know if this worked for you. We will be happy to provide more information in creating a new column in querry to popul;te "Issued" text when needed. Good luck.
    2,540 pointsBadges:
    report
  • Gbullo
    Thanks guy for your help but I still can't get it to work. We tried placing the IIF statement in the query as a new column but how I should reference back to the proper table. Do I select the table from the drop down and then the field name "issue date" then place the IIF statement in the Critera section? I'm conufsed. George
    70 pointsBadges:
    report
  • SbElectric
    Ok, let us re-visit your Reporting criteria: I am assuming you want to report all items but want to add a new column (say Status). If the Issued field has a date then Status will show “Issued” otherwise it will show blank. I have Access 2007 but, I am sure Access 2003 works same way for this function. Open the Query in Design mode & select the table(s). Now select the fields that you want in the report. On last column on Query under the Field – type Status: IIF([Issued] > 0, “Issued, “ “) If you execute this query hopefully you will see “issued” if the Issued field has a date otherwise it will blank. Now you can use this query as input to your report. By the way, in the query you do not have to select “Issued” field, if you do not want to show in the report. Just selecting in the IIF field will suffice. If you want a report showing only the items where Material has been issued – then develop another query & under Criteria use > 0 for the Issue column. You may use “Is null” similar way to report if material has not been issued. Hope this helps – it is really simple but a bit cumbersome to put in words to explain. You are welcome to contact me at sbfinance@bellsouth.net, for further explanation. Best wishes.
    2,540 pointsBadges:
    report
  • Gbullo
    All, I got figured out. Actually Msi77 gave me a good example that worked. I had to change the semi colon to a comma after the word null but the expression worked and returned the value I needed. Everyone has been so patient and kind to me and I appreciate everyone's input. It was all valuable. Thanks to everyone, Regards, George Bullo
    70 pointsBadges:
    report
  • msi77
    In my Access, the delimiter is semicolon. :-)
    1,660 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following