testing for numeric ranges....

[M G Henry]

I am working on a database that will be basically a pricing tool for
salesmen to use, and the essential part of this database will be to
have the salesman input a single quantity and then that quantity will
fall within a quantity category for pricing purposes... let me
illustrate....

If a sales rep enters a quantity under 1000, the price will be a fixed
cost determined by the number of features he is specifying for the
particular item...

if the sales rep enters a quantiy betweeen 1001 and 5000, the price
will be based on the per thousand cost of the 1000 bracket...

for 5001 to 10000 it will be based on the 5000 bracket as a per m cost
for 10001 to 25000 it will be based on the 10000 bracket as a per m
cost and so on...

what I am racking my brain trying to do is some how user a set of
between.... and... statements to assign a fixed bracket number to the
quantity the sales rep enters... the field name for the qutoed qty
is quoteqty and I am thinking that the bracket would be one
field name bracket ....... but then again I may be
wrong...

Ideally I would like to have no limit to the number of brackets I
would set up, and I could set up and adjust the brackets within the
code for now, but I am just looking for the string of
between...and... statements or whatever it may be that will accomplish
what I want to do.

 

[Guest]

Hi M G Henry,

You could have a Brackets table like this:

tblBracket
BracketID (autonum)
BracketStart (number)
BracketEnd (number)

Then use dlookup or similar to find the BracketID that it belongs to when
you enter the QuotedQty, eg:

dlookup("BracketID", "tblBracket", me.QuotedQty & " between [BracketStart]
and [BracketEnd]")

Hope this helps.

Damian.

 

[M G Henry]

Hi M G Henry,

You could have a Brackets table like this:

tblBracket
BracketID (autonum)
BracketStart (number)
BracketEnd (number)

Then use dlookup or similar to find the BracketID that it belongs to when
you enter the QuotedQty, eg:

dlookup("BracketID", "tblBracket", me.QuotedQty & " between [BracketStart]
and [BracketEnd]")

Hope this helps.

Damian.

I am working on a database that will be basically a pricing tool for
salesmen to use, and the essential part of this database will be to
have the salesman input a single quantity and then that quantity will
fall within a quantity category for pricing purposes... let me
illustrate....

If a sales rep enters a quantity under 1000, the price will be a fixed
cost determined by the number of features he is specifying for the
particular item...

if the sales rep enters a quantiy betweeen 1001 and 5000, the price
will be based on the per thousand cost of the 1000 bracket...

for 5001 to 10000 it will be based on the 5000 bracket as a per m cost
for 10001 to 25000 it will be based on the 10000 bracket as a per m
cost and so on...

what I am racking my brain trying to do is some how user a set of
between.... and... statements to assign a fixed bracket number to the
quantity the sales rep enters... the field name for the qutoed qty
is quoteqty and I am thinking that the bracket would be one
field name bracket ....... but then again I may be
wrong...

Ideally I would like to have no limit to the number of brackets I
would set up, and I could set up and adjust the brackets within the
code for now, but I am just looking for the string of
between...and... statements or whatever it may be that will accomplish
what I want to do.- Hide quoted text -

- Show quoted text -

not sure if my reply from earlier today was successful...

Damian,

I am still a bit lost...

should I be using the dlookup in a query ? and should I build
relationships between the quotedqty field and the bracket table ??

M G Henry

 

[M G Henry]

Hi M G Henry,

You could have a Brackets table like this:

tblBracket
BracketID (autonum)
BracketStart (number)
BracketEnd (number)

Then use dlookup or similar to find the BracketID that it belongs to when
you enter the QuotedQty, eg:

dlookup("BracketID", "tblBracket", me.QuotedQty & " between [BracketStart]
and [BracketEnd]")

Hope this helps.

Damian.

I am working on a database that will be basically a pricing tool for
salesmen to use, and the essential part of this database will be to
have the salesman input a single quantity and then that quantity will
fall within a quantity category for pricing purposes... let me
illustrate....

If a sales rep enters a quantity under 1000, the price will be a fixed
cost determined by the number of features he is specifying for the
particular item...

if the sales rep enters a quantiy betweeen 1001 and 5000, the price
will be based on the per thousand cost of the 1000 bracket...

for 5001 to 10000 it will be based on the 5000 bracket as a per m cost
for 10001 to 25000 it will be based on the 10000 bracket as a per m
cost and so on...

what I am racking my brain trying to do is some how user a set of
between.... and... statements to assign a fixed bracket number to the
quantity the sales rep enters... the field name for the qutoed qty
is quoteqty and I am thinking that the bracket would be one
field name bracket ....... but then again I may be
wrong...

Ideally I would like to have no limit to the number of brackets I
would set up, and I could set up and adjust the brackets within the
code for now, but I am just looking for the string of
between...and... statements or whatever it may be that will accomplish
what I want to do.- Hide quoted text -

- Show quoted text -

sorry Damian, but I have never used the dlookup command, would I put
that into a query or would it be an event action ??

 

[M G Henry]

Hi M G Henry,

You could have a Brackets table like this:

tblBracket
BracketID (autonum)
BracketStart (number)
BracketEnd (number)

Then use dlookup or similar to find the BracketID that it belongs to when
you enter the QuotedQty, eg:

dlookup("BracketID", "tblBracket", me.QuotedQty & " between [BracketStart]
and [BracketEnd]")

Hope this helps.

Damian.

I am working on a database that will be basically a pricing tool for
salesmen to use, and the essential part of this database will be to
have the salesman input a single quantity and then that quantity will
fall within a quantity category for pricing purposes... let me
illustrate....

If a sales rep enters a quantity under 1000, the price will be a fixed
cost determined by the number of features he is specifying for the
particular item...

if the sales rep enters a quantiy betweeen 1001 and 5000, the price
will be based on the per thousand cost of the 1000 bracket...

for 5001 to 10000 it will be based on the 5000 bracket as a per m cost
for 10001 to 25000 it will be based on the 10000 bracket as a per m
cost and so on...

what I am racking my brain trying to do is some how user a set of
between.... and... statements to assign a fixed bracket number to the
quantity the sales rep enters... the field name for the qutoed qty
is quoteqty and I am thinking that the bracket would be one
field name bracket ....... but then again I may be
wrong...

Ideally I would like to have no limit to the number of brackets I
would set up, and I could set up and adjust the brackets within the
code for now, but I am just looking for the string of
between...and... statements or whatever it may be that will accomplish
what I want to do.

I am pretty sure that I have the dlookup function working properly in
a macro, I have run into a problem however with the functionality of
the form in creating the final calucatitons.

The form that the quotedqty field is on, also contains other fields
that factor into the final calculation. the dlookup returns the
correct bracket ID, but I built the tblbrackt to relate to the costs
that will be used as a result of the items that are selected. let me
illustrate...

The sales rep can enter the quantity he wants,
but he also can select the number of colors the piece will be by using
a combo box that loads that value into a field on the same query as
the quoteqty.

This number is used to multiply a dollar amount that varies by
quantity and this in turn is added along with the other factors that
the sales rep selects to come up with a total price.

The way I have it now is 3 tables,

1) one with 26 different quantity brackets (rows) each bracket
containing 16 different catagories( columns ) the first column being
an autonumber column that relates on a one to one basis with the
bracket ID column in the tblBracket basically a 16 x 26 matrix
chart.

2) the second table is the bracket chart with the first field being
the bracket ID autonumbe

3) the third table holds the results of the 13 various selections
from the form. This table will hold the input data from the form.

The problem I am having is finding a way to relate the first two
tables to the third table so that the calculations can be done on one
form.

I hope I am adequately explaining the situation.

M G Henry