How can I add static text to my drop down project name field?

How can I add text to my drop down query to add "Compl" to the project Name if Status is Completed?

In my query design:
Table:
Projects
Fields:
Project Name
Status

Projects have a status of "working" or "completed".
If a project has a status of "completed" how can I add "Compl-" to the project name:
Example:
Compl - Replace Shingles on North Side

My attempt: (added statement in criteria in query design view under the ProjectName field)
IIf([Status]="Completed",(&"Compl-"&[Projects]![ProjectName]),([Projects]![ProjectName]) ) ---not working

Also, how can I put the projects with "Compl-" in the bottom of the list?
DJPr0Asked:
Who is Participating?
 
PatHartmanCommented:
Where are you using the IIf()?  It should be in the query used as the RowSource for the combo.

Select SomeID, IIf(Status = "Completed",  ProjectName & "-Comp", ProjectName) As FormattedProjName, fld3, fld4, ...
From YourTable
Order By ProjectName;

I moved the -Comp to the end of ProjectName so it wouldn't interfere with type ahead in the combo.
0
 
DJPr0Author Commented:
Thanks Pat,

Can you format for the query builder?
Under what field do I input your IIf statement?
0
 
Fabrice LambertFabrice LambertCommented:
Can you format for the query builder?
Under what field do I input your IIf statement?
Just switch from the query builder mode to SQL mode (first icon in the ribbon), copy / paste the query and switch back to query builder mode.
0
 
PatHartmanCommented:
Don't forget to use the correct names for the columns.  Just eliminate the extra fields if you only need the two named ones.
0
 
DJPr0Author Commented:
Worked Great, Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.