Using wildcard in Switch function

[kelsita_05]

I am trying to use a Switch expression as a column heading in a crosstab query to generate statistics for how much work is done per client (Requester). Each requester is referred to by acronym, so it may look like "DOD/OUSD/FTT/ATA/B", but I want that column to include everything that starts with DOD, instead of listing each instance separately. This is my expression so far:

Expr1: Switch([Requester]="DOD*","DOD",[Requester]="DS/ATA","ATAP")

When I run the query, the column "ATAP" shows up perfectly with all the correct info, but it's as if I never entered "DOD*". Nothing appears, and since I didn't add anything in the Switch function for what to do with other requesters, the second column shows up as <>.

Do wildcard *'s not work in Switch functions? I don't get any errors, but this is definitely not the information I'm looking for.

Is there any other way to include all Requesters that start with DOD in one column?

This is my SQl if that's helpful:
TRANSFORM Sum([Q and D Database].[Word Count]) AS [SumOfWord Count]
SELECT [Q and D Database].[Source/Target]
FROM [Q and D Database]
WHERE ((([Q and D Database].[Out Date]) Between #1/1/2006# And #12/31/2006#))
GROUP BY [Q and D Database].[Source/Target]
ORDER BY [Q and D Database].[Source/Target], Switch([Requester]="DOD*","DOD",[Requester]="DS/ATA","ATAP")
PIVOT Switch([Requester]="DOD*","DOD",[Requester]="DS/ATA","ATAP");

Thanks!!!

 

[Moniker]

Have you considered something like this:

Switch([Requester] Like "DOD*","DOD"....

I'll bet something like that would do it.

 

[kelsita_05]

Thanks, that did the trick!