calculated fields and iff functions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi my name is Pete and i'm new to access in general. up to now evereything
was going smoothly but i just can't figure out how to do this.

I'm in the query design view and i'm supose to:
Add a new calculated field, with caption property value of it's name, that
adds the values of two fields( OrderAmt and DeliveryCharge). then I'm asked
to use the IIf function to add a additional $2 for a specific city name ...

I'm realy stuck on this one so please help out
Pete
 
Try this:

IIF(CityName = “New York†Or CityName = “Denverâ€, OrderAmt +
DeliveryCharge+2, OrderAmt + DeliveryCharge)
 
Thx for trying luke but sorry that didnt work, all i'm getting when I try to
open the normal view is a box that says Bill Totals ...
Let me write word for word what they're asking me to do because I think i'm
asking this question in the wrong way.

"Add a calculated field named BillTotals ( caption property value of Bill
Total) that adds the OrderAmt and DeliveryCharge fields; use the IIf function
to add an additional $2 to the BillTotal field if the City field value is
Naples."
 
Post your SQL, will have better idea

Pete said:
Thx for trying luke but sorry that didnt work, all i'm getting when I try to
open the normal view is a box that says Bill Totals ...
Let me write word for word what they're asking me to do because I think i'm
asking this question in the wrong way.

"Add a calculated field named BillTotals ( caption property value of Bill
Total) that adds the OrderAmt and DeliveryCharge fields; use the IIf function
to add an additional $2 to the BillTotal field if the City field value is
Naples."
Click to expand...
 
this is the SQL statment from the RestaurantOrders query i'm working in.

SELECT Order.OrderID, Order.OrderDate, Order.OrderAmt,
Restaurant.RestaurantID, Restaurant.RestaurantName, Restaurant.Street,
Restaurant.City, Restaurant.State, Restaurant.Zip, Restaurant.OwnerFirstName,
Restaurant.OwnerLastName, Restaurant.Phone, Restaurant.Website,
Restaurant.TakeoutOnly, Restaurant.DeliveryCharge, Restaurant.MaxDistance,
Restaurant.RestaurantName
FROM Restaurant INNER JOIN [Order] ON
Restaurant.RestaurantID=Order.RestaurantID
GROUP BY Order.OrderID, Order.OrderDate, Order.OrderAmt,
Restaurant.RestaurantID, Restaurant.RestaurantName, Restaurant.Street,
Restaurant.City, Restaurant.State, Restaurant.Zip, Restaurant.OwnerFirstName,
Restaurant.OwnerLastName, Restaurant.Phone, Restaurant.Website,
Restaurant.TakeoutOnly, Restaurant.DeliveryCharge, Restaurant.MaxDistance,
Restaurant.RestaurantName;

I know it's kinda big.
can you explain why u'd need the SQL statment?
essentialy all i need is the code for adding a new calculated field with the
name BillTotals, that adds the OrderAmt and DeliveryCharge fields.


thx in advance for any help you can give me
Pete
 
Pete,

The If statement should work fine. What kind of error you get when you use
it? What data types are the 2 fields (OrderAmt, DeliveryCharge)? It may be
something else, like formatting, null values.

BillTotals: IIF(CityName = “Naplesâ€, OrderAmt + DeliveryCharge+2, OrderAmt +
DeliveryCharge)

Luke
 
Hi Pete
The code that Luke gave you should work, try that

SELECT Order.OrderID, Order.OrderDate, Order.OrderAmt,
Restaurant.RestaurantID, Restaurant.RestaurantName, Restaurant.Street,
Restaurant.City, Restaurant.State, Restaurant.Zip,
Restaurant.OwnerFirstName,
Restaurant.OwnerLastName, Restaurant.Phone, Restaurant.Website,
Restaurant.TakeoutOnly, Restaurant.DeliveryCharge, Restaurant.MaxDistance,
Restaurant.RestaurantName,
IIF(CityName in( “New York†,“Denverâ€), OrderAmt +
DeliveryCharge+2, OrderAmt + DeliveryCharge) as TotalAmount
FROM Restaurant INNER JOIN [Order] ON
Restaurant.RestaurantID=Order.RestaurantID
GROUP BY Order.OrderID, Order.OrderDate, Order.OrderAmt,
Restaurant.RestaurantID, Restaurant.RestaurantName, Restaurant.Street,
Restaurant.City, Restaurant.State, Restaurant.Zip,
Restaurant.OwnerFirstName,
Restaurant.OwnerLastName, Restaurant.Phone, Restaurant.Website,
Restaurant.TakeoutOnly, Restaurant.DeliveryCharge, Restaurant.MaxDistance,
Restaurant.RestaurantName,IIF(CityName in( “New York†,“Denverâ€), OrderAmt +
DeliveryCharge+2, OrderAmt + DeliveryCharge)


Pete said:
this is the SQL statment from the RestaurantOrders query i'm working in.

SELECT Order.OrderID, Order.OrderDate, Order.OrderAmt,
Restaurant.RestaurantID, Restaurant.RestaurantName, Restaurant.Street,
Restaurant.City, Restaurant.State, Restaurant.Zip, Restaurant.OwnerFirstName,
Restaurant.OwnerLastName, Restaurant.Phone, Restaurant.Website,
Restaurant.TakeoutOnly, Restaurant.DeliveryCharge, Restaurant.MaxDistance,
Restaurant.RestaurantName
FROM Restaurant INNER JOIN [Order] ON
Restaurant.RestaurantID=Order.RestaurantID
GROUP BY Order.OrderID, Order.OrderDate, Order.OrderAmt,
Restaurant.RestaurantID, Restaurant.RestaurantName, Restaurant.Street,
Restaurant.City, Restaurant.State, Restaurant.Zip, Restaurant.OwnerFirstName,
Restaurant.OwnerLastName, Restaurant.Phone, Restaurant.Website,
Restaurant.TakeoutOnly, Restaurant.DeliveryCharge, Restaurant.MaxDistance,
Restaurant.RestaurantName;

I know it's kinda big.
can you explain why u'd need the SQL statment?
essentialy all i need is the code for adding a new calculated field with the
name BillTotals, that adds the OrderAmt and DeliveryCharge fields.


thx in advance for any help you can give me
Pete

Ofer said:
Post your SQL, will have better idea
Click to expand...
Click to expand...
 
well there are two ways for writting this been that i'm a newbie to the
program just puttin in the statement into the query seems much easier then
writtin the whole query in sql beacuse i havent used that method often.
the if statement Luke gave almost worked and it most defently send me on the
right path though. i'm not to good with writtin this in sql. havent done that
to many times since i'm new to the program the if statement is much easier to
work with or atleast for me it is.
this is the if statement that worked out for me:

BillTotal:
IIf([City]="Naples",[OrderAmt]+[DeliveryCharge]+2,[OrderAmt]+[DeliveryCharge])

I placed this into the FIELD table and TOTAL this by expression and it
showed me the new colum named Billtotal adding OrderAmt, DeliveryCharge
fields and $2 together for the city naples and OrderAmt and DeliveryCharge
for all the rest.
Luke the if statement helped alot if defently send me in the right direction
thx
Ofer i used sql statement writtin only few times before hand and quite
honestly i got lost tryin to place it in. But u're method would have worked
fine ... for those who prefer writtin the whole query in sql instead, this
is the code:


SELECT Order.OrderID, Order.OrderDate, Order.OrderAmt,
Restaurant.RestaurantID, Restaurant.RestaurantName, Restaurant.Street,
Restaurant.City, Restaurant.State, Restaurant.Zip, Restaurant.OwnerFirstName,
Restaurant.OwnerLastName, Restaurant.Phone, Restaurant.Website,
Restaurant.TakeoutOnly, Restaurant.DeliveryCharge, Restaurant.MaxDistance,
Restaurant.RestaurantName,
IIf([City]="Naples",[OrderAmt]+[DeliveryCharge]+2,[OrderAmt]+[DeliveryCharge]) AS BillTotal
FROM Restaurant INNER JOIN [Order] ON Restaurant.RestaurantID =
Order.RestaurantID
GROUP BY Order.OrderID, Order.OrderDate, Order.OrderAmt,
Restaurant.RestaurantID, Restaurant.RestaurantName, Restaurant.Street,
Restaurant.City, Restaurant.State, Restaurant.Zip, Restaurant.OwnerFirstName,
Restaurant.OwnerLastName, Restaurant.Phone, Restaurant.Website,
Restaurant.TakeoutOnly, Restaurant.DeliveryCharge, Restaurant.MaxDistance,
Restaurant.RestaurantName;



thx to the both of you cause u did help out in this A LOT ...
 
Back
Top