Code expression for multiple combinations

[BethMc]

In a form for renting jeeps, I have an unbound text box (label: Jeep Cost)
that needs to show the cost of the jeep, before options and taxes. There are
six possible combinations of Jeep Type (combo box) and Unit (option group).
I need something like...

Iif [JeepType]=1 And [Unit]=4, =[NumberDays]*149.99
Iif [JeepType]=1 And [Unit]=5, =[NumberDays]*79.99
Iif [JeepType]=2 And [Unit]=4, =[NumberDays]*189.99
etc., where the JeepTypes can be 1, 2 or 3 and the Unit can be 4 (full
day) or 5 (half day).

This, with the correct syntax, would be an expression used as the Control
Source for the unbound textbox labeled Jeep Cost. I think. And is it an Iif
expression at all, or do I need something using a Where condition?

Should I have gone with both combo boxes, or both option groups, or is it OK
to mix them?

All assistance rewarded with grateful prayers in your name! Thanks in
advance.

 

[Allen Browne]

Beth, the underlying question is how you have your tables set up.

Prices can change over time, so I think you will need a table of the current
rates. This Price table will have fields like this:
JeepType
Unit
DailyRate Currency
The combination of JeepType + Unit could be primary key.

Now your Hire table will have fields like this:
JeepType
Unit
NumberDays Number
DailyRate Currency

You could then use the AfterUpdate event procedure of both the JeepType and
the Unit field to look up the DailyRate in the Price table, and drop it into
the DailyRate field in your form. The code will be something like this:

Private Sub JeepType_AfterUpdate()
Dim strWhere As String
If Not (IsNull(me.JeepType) OR IsNull(me.Unit)) Then
strWhere = "(JeepType = " & Me.JeepType & ") AND (Unit = " & Me.Unit
& ")"
MeDailyRate = DLookup("DailyRate", "Price", strWhere)
End If
End Sub

Private Sub Unit_AfterUpdate()
Call JeepType_AfterUpdate
End Sub

 

[John W. Vinson/MVP]

Iif [JeepType]=1 And [Unit]=4, =[NumberDays]*149.99
Iif [JeepType]=1 And [Unit]=5, =[NumberDays]*79.99
Iif [JeepType]=2 And [Unit]=4, =[NumberDays]*189.99
etc., where the JeepTypes can be 1, 2 or 3 and the Unit can be 4 (full
day) or 5 (half day).

This, with the correct syntax, would be an expression used as the Control
Source for the unbound textbox labeled Jeep Cost. I think. And is it an Iif
expression at all, or do I need something using a Where condition?

Should I have gone with both combo boxes, or both option groups, or is it OK
to mix them?

Do follow Allen's advice. His suggested design is much better than
burying the rates deep in an obscure expression in a query. A very
simple JOIN will get your current price.

If you *do* want to use the current structure, you can use the
Switch() function instead. Set the control source of the textbox to

=[NumberDays] * Switch([JeepType] = 1 AND [Unit] = 4, 149.99,
[Jeeptype] = 1 AND [Unit] = 5, 79.99,
[Jeeptype] = 2 AND [Unit] = 4, 189.99,
<etc etc>

 

[BethMc]

I'm floundering. When I tried Allen's revised table structure, I got lost -
it seems the whole matter of queries escapes me. Queries make perfectly good
sense for pulling data & reports out of an accumulated database - but as the
base for a form that's collecting the data? Somehow I can't wrap my mind
around any sense of how it works. (And as for a "very simple JOIN"... Huh?
(Sorry.))
Trying to use the Switch function, I'm missing something. I no longer
get an error message in the unbound text box that's supposed to show Jeep
Cost - but the price doesn't come up, either. It's just empty. I show the
following code in the Control Source property:

=[Days]*Switch([JeepType]=1 And [Unit]=4,149.99,[JeepType]=1 And
[Unit]=5,79.99,[JeepType]=2 And [Unit]=4,189.99,[JeepType]=2 And
[Unit]=5,99.99,[JeepType]=3 And [unit]=4,189.99,[JeepType]=3 And
[Unit]=5,99.99)

Note, this is NOT what I typed in! Every time I save it, whether it's typed
directly in the Control Source property box or in the ... Code Builder for
that box, most of the spacing I copied from John's code, and the all-capital
letters in the AND's, just vanish. Is this supposed to happen?

Your patience is appreciated. This program is a real lesson in humility.
Even the "for DUMMIES" book isn't enough. (No more whining. Stop it. Stop
it!)
--
Beth McCalley

Iif [JeepType]=1 And [Unit]=4, =[NumberDays]*149.99
Iif [JeepType]=1 And [Unit]=5, =[NumberDays]*79.99
Iif [JeepType]=2 And [Unit]=4, =[NumberDays]*189.99
etc., where the JeepTypes can be 1, 2 or 3 and the Unit can be 4 (full
day) or 5 (half day).

This, with the correct syntax, would be an expression used as the Control
Source for the unbound textbox labeled Jeep Cost. I think. And is it an Iif
expression at all, or do I need something using a Where condition?

Should I have gone with both combo boxes, or both option groups, or is it OK
to mix them?

Do follow Allen's advice. His suggested design is much better than
burying the rates deep in an obscure expression in a query. A very
simple JOIN will get your current price.

If you *do* want to use the current structure, you can use the
Switch() function instead. Set the control source of the textbox to

=[NumberDays] * Switch([JeepType] = 1 AND [Unit] = 4, 149.99,
[Jeeptype] = 1 AND [Unit] = 5, 79.99,
[Jeeptype] = 2 AND [Unit] = 4, 189.99,
<etc etc>

 

[John W. Vinson/MVP]

I'm floundering. When I tried Allen's revised table structure, I got lost -
it seems the whole matter of queries escapes me. Queries make perfectly good
sense for pulling data & reports out of an accumulated database - but as the
base for a form that's collecting the data? Somehow I can't wrap my mind
around any sense of how it works.

Um? A Query is just a way of viewing - or editing - the data in a
Table. You can base a form on a Table, or you can base a form on an
updateable Query; it works *exactly the same*.

(And as for a "very simple JOIN"... Huh?
(Sorry.))

Without knowing more about your table structure i can't be specific
but any time you create a Query with two tables you will have JOIN;
in the graphical query builder it's represented by a line between the
tables.

Trying to use the Switch function, I'm missing something. I no longer
get an error message in the unbound text box that's supposed to show Jeep
Cost - but the price doesn't come up, either. It's just empty. I show the
following code in the Control Source property:

=[Days]*Switch([JeepType]=1 And [Unit]=4,149.99,[JeepType]=1 And
[Unit]=5,79.99,[JeepType]=2 And [Unit]=4,189.99,[JeepType]=2 And
[Unit]=5,99.99,[JeepType]=3 And [unit]=4,189.99,[JeepType]=3 And
[Unit]=5,99.99)

Note, this is NOT what I typed in! Every time I save it, whether it's typed
directly in the Control Source property box or in the ... Code Builder for
that box, most of the spacing I copied from John's code, and the all-capital
letters in the AND's, just vanish. Is this supposed to happen?

Yep. Spacing and capitalization are not preserved and are not
essential.

Are there controls on your form named Days, Unit, and JeepType? If
not, replace these values with the names of the controls (not the
names of the fields in the table to which the controls are bound). And
are the Days, JeepType and Unit fields in fact filled in? If any one
of them is NULL, the result of your entire expression will also be
NULL.

Your patience is appreciated. This program is a real lesson in humility.
Even the "for DUMMIES" book isn't enough. (No more whining. Stop it. Stop
it!)

The For Dummies book is actually pretty good... to teach folks how to
use an existing database. It's really not designed or intended for
more advanced work like you're doing!