Display AS

[goslincm]

Hi, I have the following query and what I need to do is find the right way to have the value of field [localunittype] do the following conversion: if user inputs 2 then display "CITY", if user inputs 3 display "VILLAGE"

I don't know how to do that with this:

SELECT dbo_localUnitAudit.CountyCd, dbo_localUnitAudit.LocalUnitType, dbo_localUnitAudit.LocalUnitCd, dbo_localUnitAudit.FiscalYear, dbo_localUnitAudit.AuditReceivedDate, Format(DateSerial([FiscalYear],[FiscalEndMM]+6,0),'mm/dd/yyyy') AS AuditDue, IIf([auditreceiveddate]>[auditdue],"LATE",IIf([auditreceiveddate]<=[auditdue],"TIMELY",IIf([auditreceiveddate] Is Null,"NOT FILED"))) AS AnnualAuditFiledTimely, dbo_LocalUnit.Name, dbo_LocalUnit.FiscalEndMM, dbo_Population.CensusYear, dbo_Population.Population

FROM dbo_Population INNER JOIN (dbo_LocalUnit INNER JOIN dbo_localUnitAudit ON (dbo_LocalUnit.CountyCd = dbo_localUnitAudit.CountyCd) AND (dbo_LocalUnit.LocalUnitType = dbo_localUnitAudit.LocalUnitType) AND (dbo_LocalUnit.LocalUnitCd = dbo_localUnitAudit.LocalUnitCd)) ON (dbo_Population.CountyCd = dbo_localUnitAudit.CountyCd) AND (dbo_Population.LocalUnitType = dbo_localUnitAudit.LocalUnitType) AND (dbo_Population.LocalUnitCd = dbo_localUnitAudit.LocalUnitCd)

WHERE (((dbo_localUnitAudit.LocalUnitType) Like [Enter Single Digit Unit Type Code] & "*") AND ((dbo_localUnitAudit.FiscalYear) Like "*2006" Or (dbo_localUnitAudit.FiscalYear) Like "*2005") AND ((Format(DateSerial([FiscalYear],[FiscalEndMM]+6,0),'mm/dd/yyyy')) Like "*2006") AND ((dbo_Population.CensusYear)="2000"));

 

[dhookom]

Add this calculated column to your query grid

Field: Switch([Enter Single Digit Unit Type Code]=2,"City",[Enter Single Digit Unit Type Code]=3,"Village")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[goslincm]

Thanks so much Duane!