Link to home
Start Free TrialLog in
Avatar of ReneeM787
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.
Avatar of DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)
Flag of United States of America image

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
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.
Avatar of ReneeM787
ReneeM787

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.
is this any help:

fldDT = #2010-11-29 17:25:22#
? MonthName(Month(fldDT),True) & "Rate"
NovRate
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
ASKER CERTIFIED SOLUTION
Avatar of ReneeM787
ReneeM787

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Solution coming tomorrow, hang in there.
Which query contains the field service_mth ?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial