Switch in a Query

[HollyH]

I have a query in which I need to switch a code for a value.

example
(working/currently operational)
txtFEERATE: Switch
([Time Sheets DB]![DIVISION]="1.M.0"," 0.50",
[Time Sheets DB]![DIVISION]="1.2.1","225",
[Time Sheets DB]![DIVISION]="1.3.1","290",
True,[FEE RATE])

I am now trying to do the same thing drawing the value to
change to from another table (single entry table) so that I
don't have to go to the code everytime we have a rate change.

(not working)
txtFEERATE: Switch
([Time Sheets DB]![DIVISION]="1.M.0",[Rates]![Milage],
[Time Sheets DB]![DIVISION]="1.2.1",[Rates]![SEM],
[Time Sheets DB]![DIVISION]="1.3.1",[Rates]![EDS],
True,[FEE RATE])

This table ([Rates]) will never have more than one record.
[Rates]![Milage] = 0.50
[Rates]![SEM] = 225
[Rates]![EDS] = 290

Any idea why it doesn't work?

 

[[MVP] S.Clark]

I think you need the word "Forms!" in your parameters.

(Forms![Time Sheets DB]![DIVISION]="1.M.0"," 0.50",
Forms![Time Sheets DB]![DIVISION]="1.2.1","225",
Forms![Time Sheets DB]![DIVISION]="1.3.1","290", ...

 

[Marshall Barton]

I have a query in which I need to switch a code for a value.

example
(working/currently operational)
txtFEERATE: Switch
([Time Sheets DB]![DIVISION]="1.M.0"," 0.50",
[Time Sheets DB]![DIVISION]="1.2.1","225",
[Time Sheets DB]![DIVISION]="1.3.1","290",
True,[FEE RATE])

I am now trying to do the same thing drawing the value to
change to from another table (single entry table) so that I
don't have to go to the code everytime we have a rate change.

(not working)
txtFEERATE: Switch
([Time Sheets DB]![DIVISION]="1.M.0",[Rates]![Milage],
[Time Sheets DB]![DIVISION]="1.2.1",[Rates]![SEM],
[Time Sheets DB]![DIVISION]="1.3.1",[Rates]![EDS],
True,[FEE RATE])

This table ([Rates]) will never have more than one record.
[Rates]![Milage] = 0.50
[Rates]![SEM] = 225
[Rates]![EDS] = 290

Can't tell without seeing the query's FROM clause, but I'll
guess that you did not include the Rates table in the query.

How you would specify the Join type, depends on how the two
tables are related.

If the tables are not related (likely in this case), your
special situation should allow you to just add the table
without a join line.

If that produces something weird, then use (much slower)
DLookup to retrieve the values. E.g.

...., [Time Sheets DB]!DIVISION = "1.3.1",
DLookup("EDS","Rates"), ...

 

[Albert D. Kallal]

First of all, using the switch can make sense when you are stuck, or as you
mention are will to hard code some values into your application.

However, using switch is generally a band aid..and should only be used to
dig
you out of trouble.

In you 2nd case, you actually have another table with values you need to
lookup. We have sql and the query builder at our disposal. The very essence
of the query system is that it allows you to drop in additional tables..and
join your data. This is most certainly a case here switch should not be
consider, nor used at all.

Sql and the query builder is DESIGNED to join together data and pull things
from other tables with great easy.

You don't need to do a bunch of funny typing, or difficult syntax. (you
already a good design idea by moving the values into another table).
However, instead of one record with 3 fields, what you really need, is

KeyID Value
Or, in your case:

DivsionCode DivValue DivType
1.M.0 0.50 Milage
1.2.1 225 SEM
1.3.1 290 EDS

Now, all you do is fire up the query builder, and drop in your first table,
and then drop in the above table. You simply draw a join link from your
Division field to the DivsionCode in the above table. you also do need to
double click on the join line, and do a left join. That reads

Include ALL records from "Parent Table (time sheets?)" and only those
records from
"tblDivsionCodes (our lookup table)" where the joined fields are equal

As a side note...you can drop in as many additional "lookup" tables you have
into he query builder and simply draw those join lines. This approach is
more flexile, easier to write, teaches you how to use sql to solve this
problem, and you get to lay back drinking coffee and use the mouse. No funny
typing, you don't even have to know that the switch command exists. You just
use the mouse..and related data tables...and that is what database systems
are all about.......