Please help me understand looking up a record based on a date

[June]

I am having trouble grasping the concept of querying a table for a fee
I have a form - like an order form
I want to lookup a fee that is in a table with a set date
i.e. Test charge $6.50 01/01/05
chen that charge changes the record is
test charge $7.50 05/05/05
- if the fee is changed I want a new record with the date of the change.
My form has a date and I want it to lookup and calculate the fee that was
applicable for that date.
Can someone help me with an expression that looks up the record and finds
the fee for that date.

 

[June]

I guess I am not at all sure what your current table design is, or
exactly what you want to do.

I will start with a question or two.

Does the table have one field for the amount (likely currency type) and
another field for the date, hopefully a date type field?

You then want to search for a date in that date field. Do you want to
do that while you are in a form and then display that form with the
information for that date? Have you considered the problem of more than one
record with the same date?

Is your problem searching for the date?

Hi Joseph
Thanks for your time
I always find it hard to explain myself -

answer1/

yes a currency field and a short date field

hopefully to answer your next question

on my "order form" I want to look up the fees that applied on the date of
that order.

- if the fees change and I have an old order or I look up the history of an
order I want it still to look up the fee that applied on the old date.

So I am not searching for an exact date or even the most current date

Hope this explains it furthur

Thanks Lyn

 

[June]

OK I think I understand what you need now, the problem is I have never
personally worked with date ranges and I am not sure how it would be
possible to get what you want assuming there was not a specific record for
that date.

Hopefully someone who has worked in dates a lot more than I will be able
to help.

--
Joseph Meehan

Dia duit

Thanks for looking Joseph - Sorry to take so long to get back to you,

It was after midnight this side of the world.
Hope someone else has some ideas
June