This is a HARD QUESTION!!!!

[DaniBoy]

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
0120 0196 1.00 0.92 0.85 0.72
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

 

[cogent1]

At a first glance, you can just lump all dates of the first quarter into one expression, since they all result in 1.00 regardless of cartsizecode. This saves a large number of characters.

So:

(IIF[DateDelivered] Between #10/1 /2001 #And #12/31/2001#,"1.00", IIF(.......

NB - you do mean to have 2001 in both dates, don't you?

 

[cogent1]

Also you can use DatePart("q", DateDelivered)=1(or 2,3 or4) instead of the long Date Between clauses

 

[DaniBoy]

Dont know how to put it on query!!

How would I put the datepart on the query? Do I still do the between date and date? Would you explain it to me!!

Thanks

DaniBoy

 

[DaniBoy]

I figure it out!!! But I Have a problem!!

I know this is a Access Forum, I have a problem with the DatePart("q", (Date)=1)(or 2,3,4) Well, we use fiscal year for our quaters and with this am getting regular calander year, jan-mar for quater 1 and so on. Is there a way of changing the default on windows?!!!

Thanks
DaniBoy

 

[cogent1]

IIf(Datepart("q",[DateEntered])=1,"1.00",IIF(datepart("q",[DateEntered]=2 AND NewCartSizecode="120 AND CartSizeCode="136",0.96, IIF(.....


Biut where are you using this expression?

 

[cogent1]

Well, you just put 4 for your First Quarter, and 1,2 3 for the rest in sequence. The result will still be correct..

 

[DaniBoy]

I thought so!!!

Yeah, thats what I had in mind but wasnt sure....

Thanks for your help Congent1

DaniBoy

 

[The_Doc_Man]

I would assume that your table of rates is pretty much constant given the From, To, Year, and Quarter values. So if that is a good assumption, I would approach it this way.

Make a table, call it "RateConvert," with fields

OldCSC - whatever your CartSizeCode values are.
NewCSC - whatever your NewCartSizeCode values are
Year - integer (not even a LONG INTEGER)
Quarter - integer (again, not even a LONG INTEGER)
Rate - Single or Double as appropriate.

Load it up with your data. Now to get your rate, do a DLookup when you need it.

The sample below assumes that OldCSC and New CSC are numbers. I broke it apart with hard <CAR.RTN> to emphasize the parts, but the string could be pretty small.

FinalValue = TempValue * DLookup( "[Rate]", "RateConvert",
"OldCSC=" & [CartSizeCode] &
" AND NewCSC = " & [NewCartSizeCode] &
" AND Year = " & DatePart( "yyyy", [DateDelivered]) &
" AND Quarter = " & DatePart( "q", [DateDelivered] ) )

This saves you computational time and gives you the flexibility to add rates for another year any time you wish, or edit rates if you have to. You can add new combinations that you didn't use before, or can remove combinations that you don't use now.

As to a primary key, I would use either NO key or the four-field grouping of OldCSC, NewCSC, Year, and Quarter. I would certainly allow for indexes on each of the OldCSC, NewCSC, Year, and Quarter fields, but of course as individual fields, they would require "Duplicates OK" for their attributes.

Given that the codes are probably either integer or long integer, this table would be pretty short. (Even if they are short text sequences, they could be pretty short.) For the case where the codes are all INTEGER (not LONG), one row would be 16 bytes (not counting any headers supplied by Access. 64 bytes per year per combo of old and new code values. This is not a bad thing. Short records usually lead to faster retrievals.