using dlookup to automatically fill in the discount rate in a form

[Guest]

Hi!

I am in the middle of creating a form for a database that acts as an order
processing system for a boat company (part of a uni assignment). i'm using
access 2k3 btw. so far, i have the following on my form:

Booking ID (Autonumber)
Customer ID(Autonumber)
Booking Taken By (Staff enter ID)
Boat id (Staff selects boat for hire from lookup)
Hire Start Date (staff enters date)
Hire End Date (staff enters date)
Number of Days (Datediff function)

Now the problem is that the company applies a discount if certain criteria
are met. for example if a boat is hired for more than 7 days, they get a 10%
discount. the data for discount rates is in a table called "Hire Discount",
which look like this:

From (Day) To (Day) Discount (%)
0 6 0
7 10 10 etc

Question: How do i get the form to lookup the number of days in the table
and work out the appropriate discount value?

Thanks

Shaheed

 

[Guest]

Hi, Shaheed.

DLookup requires the passed Criteria expression to equal a specific value in
the lookup table. With your current table structure, this is not possible.
However, if you changed your Hire Discount to:

NumDays Discount
------ -----------
1 0
2 0
3 0
4 0
5 0
6 0
7 10
8 10
9 10
etc.

you could use DLookup:

=DLookUp("[Discount]","Hire Discount"," [NumDays] = " & [MyDays])

You would also need to check for Nulls and for values over the last entered
record to then assign your maximum discount.

I generally prefer storing data in tables rather than burying them in code,
but to my mind, since your discount structure is unlikely to change much over
time, I think this becomes easier with a custom function:

Function MyDiscount(intNumDays As Integer) As Single
Select Case intNumDays
Case Null
MyDiscount = 0
Case Is 1 to 6
MyDiscount = 0
Case Is 7 to 13
MyDiscount = .1
Case Else ' Maximum Discount
MyDiscount = .2
End Select
End Function

Then your discount's ControlSource becomes:

=MyDiscount([Number of Days])

I don't have much experience working with recordsets, but you could also
obtain your visibility of storing the data in a table with my flexibility by
changing the data to:

MinDays Discount
---------- ----------
7 .1
14 .2

Then your function could read the recordset data into a dynamic array. If
you wish to take this approach, you will need to repost, as I've never had
the occasion to do this.

Hope that helps.
Sprinks