using IIF and the AND statement

[base836ball]

OK what I have is a date field and a total field(which adds the total of 3 other text fields)

What I am trying to do is if stock # is 1799512 then only add 2.5, and if the date is between 9/9/2005 and 11/9/2005 add 2.5, and if greater than 11/9/2005 then add 4.5. Here is what I have

=IIf([Stock]=1799514,[Hauling]+[Storage]+[Additional]+[Towing]+2.5,Switch([Date]>="09/09/2005"
And
[Date]<="11/07/2005",[Hauling]+[Storage]+[Additional]+[Towing]+2.5,[Date]>"11/07/2005",[Hauling]+[Storage]+[Additional]+[Towing]+4.5))


And it doesnt work

It adds 4.5 to everything. If set [Date] = "09/09/2005" then it add 2.5 to that day only, but if i use AND it adds 4.5 to everything

 

[lespaul]

This doesn't work for you?

iif([Stock] = 1799514 AND [Date] >= #11/09/2005#, [Hauling]+[Storage]+[Additional]+[Towing]+4.5, [Hauling]+[Storage]+[Additional]+[Towing]+2.5)



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[base836ball]

That makes everything but 1799514, 2.5. I guess the date is the problem. I will fool with it, see what i can do.

 

[base836ball]

Ok i just ran a test and when i get rid of the
[Stock] = 1799514 AND

the date then works.

so my code looks like this

iif([Date] >= #11/09/2005#, [Hauling]+[Storage]+[Additional]+[Towing]+4.5, [Hauling]+[Storage]+[Additional]+[Towing]+2.5)

Now how do I get the Stock lol

 

[PHV]

=[Hauling]+[Storage]+[Additional]+[Towing]+IIf([Stock]=1799512 OR ([Date] Between #2005-09-09# And #2005-11-08#), 2.5, IIf([Date]>=#2005-11-09#, 4.5, 0))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886