One way to solve this in the query is to use the SWITCH function
Postage: Switch([X if 2oz]="X" AND [Date]<#5/14/2007#, .545,
[X if 2oz] Is Null AND [Date]<#5/14/2007#,.308,
[X if 2oz] = "X" AND [Date] <= #5/12/2008#,.459,
[X if 2oz] is Null AND [Date] <= #5/12/2008#,.334,
[X if 2oz] = "X" AND [Date] > #5/12/2008#,.471,
[X if 2oz] Is Null AND [Date] > #5/12/2008#,.346) * [Mailed Pieces]
A better way would be to use a VBA function where you pass the function the
date and the value of X If 2oz.
Public function fGetRate(TwoOunce, dteDate as Date)
IF [TwoOunce]="X" AND DteDate<#5/14/2007# Then
fGetRate = .545
ElseIF TwoOunce Is Null AND DteDate<#5/14/2007# Then
fGetRate = .308
ElseIF TwoOunce = "X" AND DteDate <= #5/12/2008# Then
fGetRate =.459
ElseIF TwoOunce is Null AND DteDate<= #5/12/2008# Then
fGetRate =.334,
ElseIF TwoOunce = "X" AND DteDate > #5/12/2008# Then
fGetRate =.471,
ElseIF TwoOunce Is Null AND DteDate > #5/12/2008# Then
fGetRate =.346)
End If
End Function
The best way would be to build a table with the dates, rates, and ounces and
then look up the rate from the table.
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Thank you Jerry.... The Field name is a typo. Can you give me an example of a
case statement, I am not really a big expert in access. I got this query to
work last year and now I have new Criteria that I need to add.
The Following is my Criteria:
Before 5/14/2007 and there is an "X" in the column (Source Data) rate = .545
Before 5/14/2007 and there is no "X" in the Column rate = .308
Between 5/14/2007 and 05/12/2008 and is an "X" in the Column rate = .459
Between 5/14/2007 and 5/12/2008 and there is no "X" in the column rate = .334
After 5/12/2008 and there is an "X" in the column rate = .471
After 5/12/2008 and there is no "X" in the Column rate = .346
The rate has to be multiplied by mailed peices.
I export this data from an excel spreadsheet which is updated daily and the
"X" column represents a 2 oz mail vs. no X, which means its a 1oz mailing.
Thank you,
Yula
Jerry Whittle said:
Nexted IIf statements start running out steam pretty fast plus are hard to
maintain. You might want to consider putting a Case statement in a module
then calling it from the query.
Also you have [x is 2ox] and [x is 2oz] fields listed. Is that correct or
one of them a typo?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
I have the following IIF statement in a Query and it is working just fine. I
am having trouble adding more conditions to the statment and I would greatly
appreciate any help I could get.
Postage: IIf([X if 2oz]="X",IIf([Date]<#5/14/2007#,0.545,0.459),IIf([X if
2oz] Is Null,IIf([Date]<#5/14/2007#,0.308,0.334),0))*[Mailed Pieces]
I need to add the following info to the conditions above:
If [x is 2ox] = x and after 5/14/2008, .471, iif [x is 2oz] is null, iif
date >=5/12/2008,.346.
I have tried to add it into the initial formula, but I can't get it to work.
Thanks for your help in advance,
Yula
