• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

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.
0
ReneeM787
Asked:
ReneeM787
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Not quite following:

Swithc () works like this:

Switch (<If Condition 1 = True>, <Result 1> , <If Condition 2 = True>, <Result 2> , <If Condition 3 = True>, <Result 3>)

mx
0
 
lluddenCommented:
In the query, you can just do a subquery (SELECT OctRate FROM tblOctRate WHERE DatetoConvert = DateToCheck)
0
 
GRayLCommented:
Can you give us the range of dates and dates and the range of rates - I like mx am having difficulty 'visualizing the problem.
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
ReneeM787Author Commented:
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.
0
 
GRayLCommented:
is this any help:

fldDT = #2010-11-29 17:25:22#
? MonthName(Month(fldDT),True) & "Rate"
NovRate
0
 
ReneeM787Author Commented:
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.
0
 
GRayLCommented:
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
0
 
ReneeM787Author Commented:
GrayL, I was able to make an iif statment do the job.  I'm posting the SQL below which will make it easier to see what my goal is.  This is long and messy, but it works.  I'm going to leave this open for a couple of days.  If nothing simpler is posted that works, I'll accept my own solution to close the question.

I was hoping SWITCH (or alternative) would let me specify what field would be used As CurRate.   Thanks!

SELECT DISTINCT qrya13_M_SingleParticipation.YTDYear, qrya13_M_SingleParticipation.Measure, qrya13_M_SingleParticipation.wda_no, FormatPercent(IIf(CStr([service_mth])="10/1/2010",[qrya13SinglePartBCYCalcBasis]![OctRate],IIf(CStr([service_mth])="11/1/2010",[qrya13SinglePartBCYCalcBasis]![NovRate],IIf(CStr([service_mth])="12/1/2010",[qrya13SinglePartBCYCalcBasis]![DecRate],IIf(CStr([service_mth])="1/1/2011",[qrya13SinglePartBCYCalcBasis]![JanRate],IIf(CStr([service_mth])="2/1/2011",[qrya13SinglePartBCYCalcBasis]![FebRate],IIf(CStr([service_mth])="3/1/2011",[qrya13SinglePartBCYCalcBasis]![MarRate],IIf(CStr([service_mth])="4/1/2011",[qrya13SinglePartBCYCalcBasis]![AprRate],IIf(CStr([service_mth])="5/1/2011",[qrya13SinglePartBCYCalcBasis]![MayRate],IIf(CStr([service_mth])="6/1/2011",[qrya13SinglePartBCYCalcBasis]![JunRate],IIf(CStr([service_mth])="7/1/2011",[qrya13SinglePartBCYCalcBasis]![JulRate],IIf(CStr([service_mth])="8/1/2011",[qrya13SinglePartBCYCalcBasis]![AugRate],[qrya13SinglePartBCYCalcBasis]![SepRate]))))))))))),2) AS CurRate, FormatNumber(IIf(CStr([service_mth])="10/1/2010",[qrya13SinglePartBCYCalcBasis]![OctNum],IIf(CStr([service_mth])="11/1/2010",[qrya13SinglePartBCYCalcBasis]![NovNum],IIf(CStr([service_mth])="12/1/2010",[qrya13SinglePartBCYCalcBasis]![DecNum],IIf(CStr([service_mth])="1/1/2011",[qrya13SinglePartBCYCalcBasis]![JanNum],IIf(CStr([service_mth])="2/1/2011",[qrya13SinglePartBCYCalcBasis]![FebNum],IIf(CStr([service_mth])="3/1/2011",[qrya13SinglePartBCYCalcBasis]![MarNum],IIf(CStr([service_mth])="4/1/2011",[qrya13SinglePartBCYCalcBasis]![AprNum],IIf(CStr([service_mth])="5/1/2011",[qrya13SinglePartBCYCalcBasis]![MayNum],IIf(CStr([service_mth])="6/1/2011",[qrya13SinglePartBCYCalcBasis]![JunNum],IIf(CStr([service_mth])="7/1/2011",[qrya13SinglePartBCYCalcBasis]![JulNum],IIf(CStr([service_mth])="8/1/2011",[qrya13SinglePartBCYCalcBasis]![AugNum],[qrya13SinglePartBCYCalcBasis]![SepNum]))))))))))),2) AS CurNum, FormatNumber(IIf(CStr([service_mth])="10/1/2010",[qrya13SinglePartBCYCalcBasis]![OctDen],IIf(CStr([service_mth])="11/1/2010",[qrya13SinglePartBCYCalcBasis]![NovDen],IIf(CStr([service_mth])="12/1/2010",[qrya13SinglePartBCYCalcBasis]![DecDen],IIf(CStr([service_mth])="1/1/2011",[qrya13SinglePartBCYCalcBasis]![JanDen],IIf(CStr([service_mth])="2/1/2011",[qrya13SinglePartBCYCalcBasis]![FebDen],IIf(CStr([service_mth])="3/1/2011",[qrya13SinglePartBCYCalcBasis]![MarDen],IIf(CStr([service_mth])="4/1/2011",[qrya13SinglePartBCYCalcBasis]![AprDen],IIf(CStr([service_mth])="5/1/2011",[qrya13SinglePartBCYCalcBasis]![MayDen],IIf(CStr([service_mth])="6/1/2011",[qrya13SinglePartBCYCalcBasis]![JunDen],IIf(CStr([service_mth])="7/1/2011",[qrya13SinglePartBCYCalcBasis]![JulDen],IIf(CStr([service_mth])="8/1/2011",[qrya13SinglePartBCYCalcBasis]![AugDen],[qrya13SinglePartBCYCalcBasis]![SepDen]))))))))))),2) AS CurDen
FROM qrya13_M_SingleParticipation, qrya13SinglePartBCYCalcBasis;
0
 
GRayLCommented:
Solution coming tomorrow, hang in there.
0
 
GRayLCommented:
Which query contains the field service_mth ?
0
 
GRayLCommented:
Assuming that field is in the second query try this:

SELECT DISTINCT a.YTDYear, a.Measure, a, FormatPercent(
Switch(Month(b.Service_Mth)=1,JanRate, Month(b.Service_Mth)=2,FebRate, Month(b.Service_Mth)=3,MarRate, Month(b.Service_Mth)=4,AprRate, Month(b.Service_Mth)=5,MayRate, Month(b.Service_Mth)=6,JunRate, Month(b.Service_Mth)=7,JulRate, Month(b.Service_Mth)=8,AugRate, Month(b.Service_Mth)=9,SepRate, Month(b.Service_Mth)=10,OctRate, Month(b.Service_Mth)=11,NovRate, Month(b.Service_Mth)=12,DecRate),2) AS CurRate,
FormatNumber(
Switch(Month(b.Service_Mth)=1,JanNum, Month(b.Service_Mth)=2,FebNum, Month(b.Service_Mth)=3,MarNum, Month(b.Service_Mth)=4,AprNum, Month(b.Service_Mth)=5,MayNum, Month(b.Service_Mth)=6,JunNum, Month(b.Service_Mth)=7,JulNum, Month(b.Service_Mth)=8,AugNum, Month(b.Service_Mth)=9,SepNum, Month(b.Service_Mth)=10,OctNum, Month(b.Service_Mth)=11,NovNum, Month(b.Service_Mth)=12,DecNum),2) AS CurNum,
FormatNumber(
Switch(Month(b.Service_Mth)=1,JanDen, Month(b.Service_Mth)=2,FebDen, Month(b.Service_Mth)=3,MarDen, Month(b.Service_Mth)=4,AprDen, Month(b.Service_Mth)=5,MayDen, Month(b.Service_Mth)=6,JunDen, Month(b.Service_Mth)=7,JulDen, Month(b.Service_Mth)=8,AugDen, Month(b.Service_Mth)=9,SepDen, Month(b.Service_Mth)=10,OctDen, Month(b.Service_Mth)=11,NovDen, Month(b.Service_Mth)=12,DecDen),2) AS CurDen  
FROM qrya13_M_SingleParticipation a, qrya13SinglePartBCYCalcBasis b;
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now