Lookup? or ?

[hotrod1952 via AccessMonster.com]

I am looking for an easy way to do this:
I have written a query which provides me with a calculated value I will call
IntMult.
I would like to use that value as follows:
IntMult = 0 to 7 return value 7
IntMult = 8 to 30 return value 10
IntMult = 31-60 return value 20
IntMult = 61-90 return value 30
IntMult = 91-180 return value 40
IntMult = 181 & > return value 60

I will use the returned values to add to a created date in WO table and
update a due date in the same table.
Should I set up another table and do a look-up or ?

 

[Michel Walsh]

You need a table to maintain all these numbers anyhow. You don't want end
users to play in *your* code to change values, isn't it? That is what they
will have to if it is embedded in some code, rather than in a table!

Since the result is always increasing as intMult range increase, you can use
a table like:

RangedResults 'table
MinValue Result ' fields
0 7
8 10
31 20
.... 'data


then

DMAX("result", "RangedResults", "MinValue<= " & intMult )


return the desired result, given a intMult value.





Hoping it may help,
Vanderghast, Access MVP

 

[hotrod1952 via AccessMonster.com]

Thanks Michel:
I am close to making this work. One more question.
my formula in my query is : Expr2: DMax([MAINTPmduemultiplier]![PmMult],"
[MAINTPmduemultiplier]",[MAINTPmduemultiplier]![Range]<=[MAINTDATEPM]![Expr1])

Expr1 is IntMult in my MAINTDATEPM query.
My result returns a null and... the 7 as a value larger than 10 or 20 or....
I got rid of the null in the query using "is not null" and I am guessing the
7 is being seen as text? But the table is set up as fixed integer o decimals.
Any ideas?
Again thanks for your help.

You need a table to maintain all these numbers anyhow. You don't want end
users to play in *your* code to change values, isn't it? That is what they
will have to if it is embedded in some code, rather than in a table!

Since the result is always increasing as intMult range increase, you can use
a table like:

RangedResults 'table
MinValue Result ' fields
0 7
8 10
31 20
... 'data

then

DMAX("result", "RangedResults", "MinValue<= " & intMult )

return the desired result, given a intMult value.

Hoping it may help,
Vanderghast, Access MVP

I am looking for an easy way to do this:
I have written a query which provides me with a calculated value I will

[quoted text clipped - 11 lines]

update a due date in the same table.
Should I set up another table and do a look-up or ?

 

[Michel Walsh]

The first argument of Dxxx function is a STRING which represent a field name
or an expression to be returned. Instead of

DMax([MAINTPmduemultiplier]![PmMult], ... , ... )

try

DMax( "[PmMult]", ..., ... )


Repeating the table name is irrelevant, in the first argument, since any
field has to come from the table name supplied as second argument.


Vanderghast, Access MVP

Thanks Michel:
I am close to making this work. One more question.
my formula in my query is : Expr2: DMax([MAINTPmduemultiplier]![PmMult],"
[MAINTPmduemultiplier]",[MAINTPmduemultiplier]![Range]<=[MAINTDATEPM]![Expr1])

Expr1 is IntMult in my MAINTDATEPM query.
My result returns a null and... the 7 as a value larger than 10 or 20
or....
I got rid of the null in the query using "is not null" and I am guessing
the
7 is being seen as text? But the table is set up as fixed integer o
decimals.
Any ideas?
Again thanks for your help.

You need a table to maintain all these numbers anyhow. You don't want end
users to play in *your* code to change values, isn't it? That is what
they
will have to if it is embedded in some code, rather than in a table!

Since the result is always increasing as intMult range increase, you can
use
a table like:

RangedResults 'table
MinValue Result ' fields
0 7
8 10
31 20
... 'data

then

DMAX("result", "RangedResults", "MinValue<= " & intMult )

return the desired result, given a intMult value.

Hoping it may help,
Vanderghast, Access MVP

I am looking for an easy way to do this:
I have written a query which provides me with a calculated value I will

[quoted text clipped - 11 lines]

update a due date in the same table.
Should I set up another table and do a look-up or ?