Need help with BETWEEN/AND Parameters

[BardsSweetie]

You've all been very helpful ... so now I'm back for more help.

I have 7 different fees depending on a starting bid:

$0.25 for starting bids between $0.01 and $0.99
$0.35 for starting bids between $1.00 and $9.99
$0.60 for starting bids between $10.00 and $24.99
$1.20 for starting bids between $25.00 and $49.99
$2.40 for starting bids between $50.00 and $199.99
$3.60 for starting bids between $200.00 and $499.99
$4.80 for starting bids $500.00 and above

When I enter an amount in the StartingBid field I want it to automatically
enter the correct fee. I don't know if it would be a problem to have all that
information in one expression.

I don't know if I should be using BETWEEN and AND or if I should be using <
and >. If someone could give me a start for the first 2 expressions, it would
be great.

Thanks,

Kathleen

 

[Allen Browne]

Best to put these fees into a table where they are easy to manage.

1. Create a new table, with these 2 fields:
- MinAmount Currency primary key
- Fee Currency the fee for this amount upwards
Save it with the name tblFee.

2. Enter the records:
0 0.25
1 0.35
10 0.60
etc.

3. In the AfterUpdate event procedure of your StartingBid text box, add this
line of code:
Private Sub StartingBid_AfterUpdate()
Me.Fee = DMax("Fee", "tblFee", "MinAmount <= " &
Nz([StartingBid],0))
End Sub

 

[BardsSweetie]

Hi Allen,

Thanks so much. I may be picking your brain for other stuff soon...lol...

I did EXACTLY what you said, then tried it out but it didn't work.

I got a message that there was a SYNTAX error and the error was in the first
line:
Private Sub StartingBid_AfterUpdate(). It was highlighted in yellow.

What do I do now?

Thanks,

Kathleen

Best to put these fees into a table where they are easy to manage.

1. Create a new table, with these 2 fields:
- MinAmount Currency primary key
- Fee Currency the fee for this amount upwards
Save it with the name tblFee.

2. Enter the records:
0 0.25
1 0.35
10 0.60
etc.

3. In the AfterUpdate event procedure of your StartingBid text box, add this
line of code:
Private Sub StartingBid_AfterUpdate()
Me.Fee = DMax("Fee", "tblFee", "MinAmount <= " &
Nz([StartingBid],0))
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

You've all been very helpful ... so now I'm back for more help.

I have 7 different fees depending on a starting bid:

$0.25 for starting bids between $0.01 and $0.99
$0.35 for starting bids between $1.00 and $9.99
$0.60 for starting bids between $10.00 and $24.99
$1.20 for starting bids between $25.00 and $49.99
$2.40 for starting bids between $50.00 and $199.99
$3.60 for starting bids between $200.00 and $499.99
$4.80 for starting bids $500.00 and above

When I enter an amount in the StartingBid field I want it to automatically
enter the correct fee. I don't know if it would be a problem to have all
that
information in one expression.

I don't know if I should be using BETWEEN and AND or if I should be using
<
and >. If someone could give me a start for the first 2 expressions, it
would
be great.

Thanks,

Kathleen

 

[BardsSweetie]

Hi Allen (again),

I figured out what I did wrong, but it still didn't work.

I got another error message with the 2nd line of code highlighted this time:
Me.Fee = DMax("Fee", "tblFee", "MinAmount <= " &

Thanks,

Kathleen

 

[John Spencer]

The newsreader got you. It split the line of code into two lines.

'Everything between here ===================

Me.Fee = DMax("Fee", "tblFee", "MinAmount <= " & Nz([StartingBid],0))

' and here =========== should be on one line

 

[BardsSweetie]

I'm still getting an error on the 2nd line:

Me.Fee = DMax("Fee", "tblFee", "MinAmount <= " & Nz([StartingBid],0))

The error that's highlighted is:

..Fee =

Very strange ...

Kathleen

 

[John Spencer]

So, do you have a control on the form that is named "Fee"?

What is the error? Is there any message associated with the error?

 

[BardsSweetie]

I do have a control named Fee.

This is the error:

Compile error:
Method or data member not found

Thanks,

Kathleen

 

[Allen Browne]

Do you have a control named StartingBid (without a space)?

 

[BardsSweetie]

Good Morning Allen ...

I made it exactly like you told me to.

2 fields MinAmount and Fee.

Was I supposed to make a FORM from the Table?

Where would I put the Control named StartingBid?

Thanks, you are such a GREAT help!!

Kathleen

 

[Allen Browne]

Hi Kathleen

In your original post, you said:
When I enter an amount in the StartingBid field ...

I assume you already have a StartingBid control somewhere in this form?