Multiple if statements with dates

[DaniBoy]

Hello,
I want to make a iif statement where I can also check on a date value on an expression on a query, here is my code:

Adjustment: IIf([newcartsizecode]>[cartsizecode] And [datedelivered] Between "10/01/01" And "12/31/02","1.00",Null)

I get an error on this code, what am I doing wrong?

Thank you

Daniboy

 

[Pat Hartman]

Your delimiters are incorrect. Dates are delimited by pound signs (#) and numeric fields are not delimited by anything.

Adjustment: IIf([newcartsizecode]>[cartsizecode] And [datedelivered] Between #10/01/01# And #12/31/02#,1.00,Null)

 

[DaniBoy]

Thanks pat

Pat, I figure it out, but now my expression is to big. This is what am trying to do, I have three fields, [newcartsizecode] , [cartsizecode] , [datedelivered] and [Expr1]. I need to pull out a value from this fields depending on the case:

Cart size Codes= 0120,0135,0164,0196

Fiscal Year Quaters
FirstQ= 10/01/any to 12/31/any,
SecQ= 01/01/any to 3/31/any,
ThirdQ= 04/01/any to 06/30/any,
Fourthq= 07/01/any to 09/31/any.

Combination rates by Change

from To FirstQRate SecQRate ThirdQrate ForthQRate
0120 0135 1.00 0.96 0.91 0.87
0120 0164 1.00 0.91 0.82 0.73
0135 0164 1.00 0.95 0.89 0.84
0135 0196 1.00 0.91 0.82 0.73
0164 0196 1.00 0.95 0.91 0.86

I did it like this but I cant fit all into the query expression.

IIf([newcartsizecode]="0120" And [cartsizecode]="0135" And [datedelivered] Between #10/1/2001# And #12/31/2002#,"1.00",
IIf([newcartsizecode]="0120" And [cartsizecode]="0164" And [datedelivered] Between #10/1/2001# And #12/31/2002#,"1.00",
IIf([newcartsizecode]="0120" And [cartsizecode]="0196" And [datedelivered] Between #10/1/2001# And #12/31/2002#,"1.00",Null))))))))))

I just pasted part for you to see it. How else can I do this? I tried pasting it on the sql but kept giving me syntax erros.

Something else, I could not make the dates have a wildcard!!

Thanks
DaniBoy

 

[RV]

Why not defining a procedure using Select Case instead.
Another idea is to "split up" the conditions into separate queries and combine them queries using UNION.

What surprises me anyway is that you're hardcoding your conditions in your IIF function.
Sure the are relations between your entities Cart Size Codes, Fiscal Year Quarters, Combination Rates by Change and the attribute datedelivered.

Assuming you've stored the data you want to use in your conditions in tables, why not use them?

RV

 

[raskew]

I agree with RV's advice. However, it was a fun problem to program. Try this function:

Function CartOMatic(cartsizecode As String, _
newcartsizecode As String, datedelivered As Date)
'*******************************************
'Name:      CartOMatic (Function)
'Inputs:    ? cartomatic("0135","0164", #4/30/02#)
'Output:    0.89
'*******************************************

Dim x As Integer, intQtr As Integer
Dim strHold As String, fmt As String
Dim carthold As String, fyStart As Date

fmt = "0.00" 'to format 1st quarter result as 1.00
'determine which FY datedelivered was in
fyStart = DateSerial(Year(datedelivered) - IIf(Month(datedelivered) >= 10, 0, 1), 10, 1)
'determine which quarter
intQtr = Int(DateDiff("m", fyStart, datedelivered) / 3) + 1

'given your example there are 5 possible combinations
'of cartsizecode | newcartsizecode
'For brevity, we can add the old and new sizes together
'and use the value to determine further action necessary

x = Val(cartsizecode) + Val(newcartsizecode)

'your example showed nine possible return results
'0.96, 0.95, 0.91, 0.89, 0.87, 0.86, 0.84, 0.82, 0.73
'the characters in "LKGECB@>5" each represent the chr()
'values of the 9 possibilities - 20.  For example:
'chr(96 - 20) = "L"

carthold = Switch(x = 255, "LGC", x = 284 Or x = 331, "G>5", _
x = 299, "KE@", x = 360, "KGB")

If intQtr = 1 Then
   strHold = Format(1, fmt)
Else
   strHold = (20 + Asc(Mid(carthold, intQtr - 1, 1))) / 100
End If
CartOMatic = strHold
End Function