Looking for help with an expression

[CoachBarker]

I have a simple search query I am using in Access 2003 and running in a vb.net appliction. For most of the time I can manipulalte what is shown in a form field based on 1 or 0(Yes or No). But in one spot I return a dataset to a DataGridView and can not get the Yes or No value I am looking for. So instead of writing a lot of code I would just like to add an expression to the query.



The last field is an integer and I would like to add an expression that if tblCustomers.Active = 1 then CurrentlyRenting 0 Yes else CurrentlyRenting = No. From the expression column I can get the data I need.

 
SELECT tblCustomers.CustomerID, tblRentals.RentalID, tblCustomers.FirstName AS [First Name], tblCustomers.MiddleInitial AS MI, tblCustomers.LastName AS [Last Name], tblRentals.StorageShedID AS [Unit Number], tblStorageUnits.BuildingNumber AS [Building Number], tblCustomers.Active, IIf([Active]=1,"Yes",IIf([Active]=0,"No")) AS CurrentlyRenting
FROM tblStorageUnits INNER JOIN (tblCustomers INNER JOIN tblRentals ON tblCustomers.CustomerID = tblRentals.CustomerID) ON tblStorageUnits.StorageShedID = tblRentals.StorageShedID
WHERE (((tblCustomers.LastName)="carinci"))
ORDER BY tblCustomers.FirstName;

Just came up with this and it seems to work. Any suggestions?
Thanks
CoachBarker

[pbaldy]

A little simpler:

IIf([Active]=1, "Yes", "No")

[CoachBarker]

Thanks, always seems to be a simpler way.

[CR08RTS]

hi, i need help regarding what expression i need to type into the expression builder.
i have created a query that has calculated the age from the date of birth. in my table i have the 'date of birth' field then created the query with the formula;
Age: DateDiff("yyyy",[Date of Birth],Now())+int(format(Now(),"mmdd")<Format([Date of Birth,"mmdd"))
to automaticaly calculate the age. the problem now lies with the fact i want to continue building my table but need to add the calculating 'Age' field into the table first. i no i need to add a calculating field in the desig view option of my table but the question is: I DO NOT KNOW WHAT EXPRESSION I NEED TO ADD?