BrentSheets   6535 pts.  |   Jun 26 2009  12:17PM GMT

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!

Gbullo   70 pts.  |   Jun 26 2009  12:44PM GMT

Brent, Sorry about not providing more details. Thanks for the reminder.

I am using Access 2003.

Thanks,

Gbullo

SbElectric   1625 pts.  |   Jun 26 2009  7:52PM GMT

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.

Gbullo   70 pts.  |   Jul 6 2009  12:05PM GMT

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

Tlsanders1   335 pts.  |   Jul 7 2009  3:44PM GMT

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.

SbElectric   1625 pts.  |   Jul 8 2009  8:10PM GMT

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.

Gbullo   70 pts.  |   Jul 11 2009  3:50PM GMT

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

SbElectric   1625 pts.  |   Jul 13 2009  11:52PM GMT

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  <a href="mailto:sbfinance@bellsouth.net" title="mailto:sbfinance@bellsouth.net">sbfinance at bellsouth.net</a>, for further explanation.
Best wishes.

Gbullo   70 pts.  |   Jul 14 2009  10:33AM GMT

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

Msi77   800 pts.  |   Jul 14 2009  4:11PM GMT

In my Access, the delimiter is semicolon.