Adding text to a query field under certain condition.

[Lkwdmntr]

I have a field named action and there are sometimes actions with the same name, but different indexes. I would like to add the words "Start Time" to actions that have an index between 200 and 300. I tried something like this --- IIf(PlanAndActPointUNION.Action between 200 AND 300, PlanAndActPointUNION.Action & "Start Time", PlanAndActPointUNION.Action) --- but it didn't work. Any suggestions? Here is the actual query I'm using.

SELECT PlanAndActPointsUNION.UserID, PlanAndActPointsUNION.WeekNumber, PlanAndActPointsUNION.Index, PlanAndActPointsUNION.Action, ((PlanAndActPointsUNION.[PlannedMon])+(PlanAndActPointsUNION.[PlannedTue])+(PlanAndActPointsUNION.[PlannedWed])+(PlanAndActPointsUNION.[PlannedThu])+(PlanAndActPointsUNION.[PlannedFri])+(PlanAndActPointsUNION.[PlannedSat])+(PlanAndActPointsUNION.[PlannedSun])) AS Planned, ((PlanAndActPointsUNION.[ActualMon])+(PlanAndActPointsUNION.[ActualTue])+(PlanAndActPointsUNION.[ActualWed])+(PlanAndActPointsUNION.[ActualThu])+(PlanAndActPointsUNION.[ActualFri])+(PlanAndActPointsUNION.[ActualSat])+(PlanAndActPointsUNION.[ActualSun])) AS Actual
FROM PlanAndActPointsUNION
WHERE (((PlanAndActPointsUNION.UserID)=2) AND ((PlanAndActPointsUNION.WeekNumber)<13) AND (((([PlanAndActPointsUNION].[PlannedMon])+([PlanAndActPointsUNION].[PlannedTue])+([PlanAndActPointsUNION].[PlannedWed])+([PlanAndActPointsUNION].[PlannedThu])+([PlanAndActPointsUNION].[PlannedFri])+([PlanAndActPointsUNION].[PlannedSat])+([PlanAndActPointsUNION].[PlannedSun])))>0))
ORDER BY PlanAndActPointsUNION.UserID, PlanAndActPointsUNION.Index, PlanAndActPointsUNION.WeekNumber;

 

[plog]

I tried something like...

No, tell us exactly what you tried. You went through the trouble of posting code but didn't include the part we need to look at.

Further, "it didn't work" isn't helpful either--you're here posting about it--that part was evident. Tell us how it didn't work. Did it produce no results? Did it produce unexpected results? Did it give an error?

 

[namliam]

SELECT PlanAndActPointsUNION.UserID
     , PlanAndActPointsUNION.WeekNumber
     , PlanAndActPointsUNION.Index
     , PlanAndActPointsUNION.Action
     , ((PlanAndActPointsUNION.[PlannedMon])
       +(PlanAndActPointsUNION.[PlannedTue])
       +(PlanAndActPointsUNION.[PlannedWed])
       +(PlanAndActPointsUNION.[PlannedThu])
       +(PlanAndActPointsUNION.[PlannedFri])
       +(PlanAndActPointsUNION.[PlannedSat])
       +(PlanAndActPointsUNION.[PlannedSun])) AS Planned
     , ((PlanAndActPointsUNION.[ActualMon])
       +(PlanAndActPointsUNION.[ActualTue])
       +(PlanAndActPointsUNION.[ActualWed])
       +(PlanAndActPointsUNION.[ActualThu])
       +(PlanAndActPointsUNION.[ActualFri])
       +(PlanAndActPointsUNION.[ActualSat])
       +(PlanAndActPointsUNION.[ActualSun])) AS Actual
FROM PlanAndActPointsUNION
WHERE (((PlanAndActPointsUNION.UserID)=2)
   AND ((PlanAndActPointsUNION.WeekNumber)<13)
   AND (((([PlanAndActPointsUNION].[PlannedMon])
         +([PlanAndActPointsUNION].[PlannedTue])
         +([PlanAndActPointsUNION].[PlannedWed])
         +([PlanAndActPointsUNION].[PlannedThu])
         +([PlanAndActPointsUNION].[PlannedFri])
         +([PlanAndActPointsUNION].[PlannedSat])
         +([PlanAndActPointsUNION].[PlannedSun])))>0))
ORDER BY PlanAndActPointsUNION.UserID, PlanAndActPointsUNION.Index, PlanAndActPointsUNION.WeekNumber;

Atleast I made your query somewhat readable....

Agree with @plog here, why did this not work?

IIf(PlanAndActPointUNION.Action between 200 AND 300, PlanAndActPointUNION.Action & "Start Time", PlanAndActPointUNION.Action)

You are talking of some sort of index between 200 and 300, where here you are using the same Action field 3 times?

A more common and/or more readable way of doing this is, als implemented a small fix to actually use the Index field as you are describing.

PlanAndActPointUNION.Action & IIf(PlanAndActPointUNION.Index between 200 AND 300, " Start Time", "")

 

[Lkwdmntr]

Thanks guys, I got it.

IIf(PlanAndActPointsUNION.Index between 200 AND 300, PlanAndActPointsUNION.Action & " Start Time", PlanAndActPointsUNION.Action)

 

[namliam]

it is hard to copy paste properly

Why use this sub-optimal solution over my suggestion which is more readable and best practice?

/sigh