Switch Statement in Report Field

[poporacer]

I have a field that I need populated based on a couple criteria. The report is based on a query. I can get close. From my understanding, a Switch statement will return the first True value, but evaluate all expressions. I can't get it to work quite correctly. The query has a field VacBid that will contain either "No Bid", "Unsuccessful", or a number. The number corresponds to a field in another table that contains starting and ending dates. If VacBid contains "No Bid" or "Unsuccessful" then simply return "No Bid" or "Unsuccessful". If there is a number in the field then you have to evaluate what current [Rank] grouping and then return the appropriate value. If I do the following, the test works correctly:

=Switch([VacBid]="No Bid","No Bid",[VacBid]="Unsuccessful","Unsuccessful",[Rank]="Cook","Test")

This will return the correct results for the 3 tests. However, when I try to use Dlookup to return the correct date I get an #Error on the first two tests.
This is the code that I get an error on:

=Switch([VacBid]="No Bid","No Bid",[VacBid]="Unsuccessful" ,"Unsuccessful" ,[Rank]="Cook",
Format(DLookUp("StartDate","tblLtVacPeriods","WeekNum= " & [VacBid]),"m/d/yy"))

When the above code is run, if VacBid is "No Bid" or "Unsuccessful" it returns #Error otherwise it returns the correct value.
What am I missing?

 

[stopher]

Assuming VacBid is a text field (since you are using it for both text and numbers), then you must enclose it in quotes like this:

=Switch([VacBid]="No Bid","No Bid",[VacBid]="Unsuccessful" ,"Unsuccessful" ,[Rank]="Cook", Format(DLookUp("StartDate","tblLtVacPeriods","WeekNum= " & "'" & [VacBid] & "'"),"m/d/yy"))

hth
Chris

 

[poporacer]

Thanks... I changed the logic to IIF and it worked!