Display all query fields even if criteria is not met

[rgwood86]

Hi all,

I have a query which has a field in it which returns a staff members name. I need a separate field which based on who it is that is named, will return either a specific text such as "New Sale" or a blank result if it is a different member of staff that is not listed in the criteria section.

I currently have the criteria on the field set to Not ([Sales Source]="John Smith"). So if the sales source is not John Smith, I still want the field to be visible, just blank

How do I achieve the blank result in the query?

 

[CJ_London]

your description is not clear, perhaps provide some example data and the required output. Criteria affects the rows returned, not whether or not to display a field

 

[rgwood86]

Sorry, so my query is based on a table that has a column in it names "Sales Source". This is the staff member who has processed a sale.

I want the query to display the Sales Source for each row, but based on the result of the sales source, display different results in another column.

For example, if I had 2 members of staff, by the names of John Smith and Peter Jones, I would want the query to show a different result another column, for example "New starter".

So the query result would look like this:

Sale Value Sales Source Location
£500 John Smith
£700 Peter Jones Birmingham
£400 Peter Jones Birmingham

By writing this I am already thinking of having a table with staff members in and linking it across, but would prefer not to this via a table method but more of a formula

 

[CJ_London]

sorry, more confused

I would want the query to show a different result another column, for example "New starter".

is the other column the location column? or a different one. Where is your example for new starter? - does it apply to John Smith or someone else?

 

[rgwood86]

Sorry to confuse you further CJ!

Ok, for the above example, just replace location with new starter, and Birmingham with "New Starter". Shouldn't have added location into the mix, just tried to use it as an example!

So if the answer is Peter Smith, "New Starter" would appear in the column. If the answer is John Smith, then the New Starter column would be blank.

 

[CJ_London]

I think you need to explain what you are really trying to do

show an example of the data you have and based on that example data, what you want it to look like

 

[bastanu]

Try IIF([Sales Source]<>"John Smith",NULL,[New Sale]) for the [New Sale] field.

Cheers,
Vlad

 

[Mark_]

For your first question

NewStarter: IIF([Sales Source]="John Smith", "New Sale","")

Something like this?

More important, as you are trying to display a value based on data in a field, please explain where this should truly come from.

 

[bastanu]

I was just trying to emulate what OP said in post #1

 

[JHB]

...
By writing this I am already thinking of having a table with staff members in and linking it across, but would prefer not to this via a table method but more of a formula

Using hard coded values could be a "pain" because you've to edit the query each time something change.
I've made an example for you in the attached database, one with hard coded values and one which use at table.

 

[arnelgp]

also this:

select [sales value],[sales source], iif(trim([location] & "")="","New Sales",[location]) As [Location]