Report sorting question

[Lucy R]

Hi

In a report I have a group header that sorts the report information into 2 sections depending if the the project is ordered or not - =IIf([Ordered]="Yes","ORDERED","FORECAST")

In the 'Project Status' column I want it to show 'Project Stage if the product is ordered and to show 'sales stage' if it is not ordered. Both these stages are listed in the same table - 'Projects' that the report/query is based on.

Any info greatly appreciated.

Cheers, Lucy

PS: I am a relatively new user and am not a computer genius!

 

[nishantd]

Lucy,

I'm not quite sure I understand your question fully, but I'll give it a try.

So you have two column titles (or headers of columns): 'Ordered' and 'Forecast'.

In addition, you have in a table somewhere a value for 'Project Status' and this value can be either 'ordered' or 'not ordered' (it could just as well be a true/false value or yes/no or 0/1 as well).

If you have a text box on your report to display the 'Project Status', then you could set the control source of the text box to:

=IIf([PROJECTSTATUS]="ordered", "Project Stage","Sales Stage")

Additionally, if you have the 'Project Status' field set to a true/false value:

=IIf([PROJECTSTATUS],"Project Stage","Sales Stage")

Hope this helps. If not, please give me some more detail...

Nishant

 

[Lucy R]

Hi Nishant

Thanks for the advice. I have tried but with no success, here is some more detail and a clearer explanation (I hope!!);

I have a column in the table which is a text box named 'Ordered' in which I type 'Yes' or 'No', depending on whether the project is ordered or not.

In the same table I have another two columns; 'Project Stage' and 'Sales Stage'. These are List boxes with a value List but the data type is text (i.e. I have a choice of 5 listed values (eg; for Project Stage the row source is "Ready to Plan";"Ready to Start";"Ready to Implement";"Ready to Release";"Project Closed") on the scroll down box when i input data into the table).

Now on the report I have a column 'Project Status', which I want to list the value (so one of the 5 listed values; eg Project Closed or Ready to Plan) from 'Project Stage' if the product is ordered and if it is not ordered I want the 'Sales Stage' value.

I tried in the Project Status Text box in the expression
=IIf([Ordered]="Yes","Project Stage","Sales Stage")
but this only inserted the actual words 'Project Stage' and 'Sales Stage' AND not the values.

How do I get the values of 'Project Stage' and 'Sales Stage'into teh report instead of the actual words?

Many thnaks and I hope that this is clearer.

Cheers, Lucy

 

[nishantd]

Lucy,

Ok. I believe i understand what you are trying to ask now. So you have a table with the following fields:
Ordered (type "yes/no")
"Project Stage" (type text)
"Sales Stage" (type text)

On a side note, if you do not have a Primary key, I would go ahead and create one for this table. You can create a new field and name it "ID" (or something else if you wish) and set it's type value to "Autonumber".

So, you need the following to display the stored value in the "Project stage" field for a row whose ordered value is set to "yes" (or -1 which is "yes" in Access; the value 0 represents false in Access).

=IIf([Ordered]=-1,[Project Stage],[Sales Stage])

This says if the Ordered value is "yes", then display the value stored in the "Project Stage" field for this record, otherwise display the value stored in the "Sales Stage" field.

Hope this helps,
Nishant

 

[Lucy R]

Thank you Nishant, it worked perfectly! The -1 is a great thing to know. In fact it helped me solve a couple of issues.

Can you help me on this question?
I have a table with Employees names and their specialist subjects, however some employees do not have a specialised subject so i dont want them showing on the report.

Employee name is a text box
Specialist subject is a combo box, type text (the info - specialist subject comes from another table)

I have a query for the report but am unsure what to do type to limit the criteria. If this is even the way to go?

Can you help on this one. Promise its my last question!!!

Cheers, Lucy

 

[nishantd]

If you would like to essentially filter out those items that have no value for 'specialised subject', then you would want to create a query that only selects those records that have a value for 'specialized subject':

SELECT * FROM tableName WHERE [Specialized Subject] <> null

(Granted that you have the employee name and the 'specialised subject' in one table)

If you have a button that is opening a report, you could view the source code for the button (right click and select Properties and then go to the Events tab and view the code for OnClick) and modify it such that you send a filter parameter to the report (code that I've added is colored red):

    Dim stDocName As String
[COLOR=DarkRed]    Dim stFilter As String[/COLOR]
    [COLOR=DarkRed]stFilter = "[Specialised Subject] <> Null"[/COLOR]
    stDocName = "NAME OF REPORT"
    DoCmd.OpenReport stDocName, acPreview[COLOR=DarkRed], stFilter[/COLOR]

What this says is to only show those records that have a non null value for the [Specialised Subject] field.

 

[Lucy R]

Hi

Still having troubles. I dont have a command button so I opted for the query option.

I inputted:
(SELECT * FROM [Resources] WHERE [Specialist Subject] <> null)
into the criteria box but then I had pop-up help message saying the following:
"You've written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field"

What does this mean?

NB: Resources = The Table that the employee name and specialist subject comes from

Once again, any help is very appreciated