ReneeM787
asked on
MS Access Switch() or alternative
For example, I have records I need to tie to data in a crosstab query. In the query there is a conversion table that converts date to "OctRate". I'd like to use Switch("OctRate", [OctRate]). But that doesn't want to work.
Any suggestions to return the value in a field in an unrelated table where the query refers to the column name? Thank you.
Any suggestions to return the value in a field in an unrelated table where the query refers to the column name? Thank you.
In the query, you can just do a subquery (SELECT OctRate FROM tblOctRate WHERE DatetoConvert = DateToCheck)
Can you give us the range of dates and dates and the range of rates - I like mx am having difficulty 'visualizing the problem.
ASKER
More info to help clarify the issue.
I am receiving data that is formatted like it came from a Crosstab query. There are multiple fields for each month. Using Rate as the example field: OctRate, NovRate, DecRate, etc. I am trying to figure out how to get that data into a query for a given reporting month. tblMonthConv converts 10/1/2010 0:00 to a text value: OctRate. I'm trying to figure out the best way to make a calculated field pick up the Rate for each month without having to create an Iif statement with 11 conditions.
I hope that helps.
I am receiving data that is formatted like it came from a Crosstab query. There are multiple fields for each month. Using Rate as the example field: OctRate, NovRate, DecRate, etc. I am trying to figure out how to get that data into a query for a given reporting month. tblMonthConv converts 10/1/2010 0:00 to a text value: OctRate. I'm trying to figure out the best way to make a calculated field pick up the Rate for each month without having to create an Iif statement with 11 conditions.
I hope that helps.
is this any help:
fldDT = #2010-11-29 17:25:22#
? MonthName(Month(fldDT),Tru e) & "Rate"
NovRate
fldDT = #2010-11-29 17:25:22#
? MonthName(Month(fldDT),Tru
NovRate
ASKER
GRayL, I tried your formula as the field in the design grid and Access wouldn't accept it. Although, if it had, my understanding is that I'd still need a condition to test for each month. So, I don't think it would save me anything. Let me know if I'm misunderstanding your intention.
You're right. You need something like the following for Access:
SELECT fldVal, Switch(Month(fldDate)=1, JanRate, Month(fldDate)=2, FebRate, Month(fldDate)=3, MarRate, Month(fldDate)=4, AprRate, Month(fldDate)=5, MayRate, Month(fldDate)=6, JunRate, Month(fldDate)=7, JulRate, Month(fldDate)=8, AugRate, Month(fldDate)=9, SepRate, Month(fldDate)=10, OctRate, Month(fldDate)=11, NovRate, Month(fldDate)=12, DecRate) * fldVal as Product from myTable
SELECT fldVal, Switch(Month(fldDate)=1, JanRate, Month(fldDate)=2, FebRate, Month(fldDate)=3, MarRate, Month(fldDate)=4, AprRate, Month(fldDate)=5, MayRate, Month(fldDate)=6, JunRate, Month(fldDate)=7, JulRate, Month(fldDate)=8, AugRate, Month(fldDate)=9, SepRate, Month(fldDate)=10, OctRate, Month(fldDate)=11, NovRate, Month(fldDate)=12, DecRate) * fldVal as Product from myTable
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Solution coming tomorrow, hang in there.
Which query contains the field service_mth ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Swithc () works like this:
Switch (<If Condition 1 = True>, <Result 1> , <If Condition 2 = True>, <Result 2> , <If Condition 3 = True>, <Result 3>)
mx