Showing a substiute value in a report if conditions are met – Access
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?
Software/Hardware used:
Software/Hardware used:
ASKED:
June 26, 2009 10:54 AM
UPDATED:
July 14, 2009 4:11 PM
Answer Wiki:
Use expression instead of field.
For example,
<pre>issued: IIf([Issue Date] Is Not Null;'ISSUED')</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
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!
Brent, Sorry about not providing more details. Thanks for the reminder.
I am using Access 2003.
Thanks,
Gbullo
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.
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
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.
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.
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
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.
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
In my Access, the delimiter is semicolon.