Find fee depending on date range

[Dueffeldoffel]

Hi! I hope this is not a stupid question but I could not find the answers via google. Probably because I do not know the correct wording for my question.

My problem is this:

I have a customer "A". He pays a monthly fee.
This monthly fee depends on membership duration. The longer they stay members, the lower their fee. The first half year they pay 2 Dollars, after that only one.
I have a table that says:

High fee = 2 $
Low fee = 1 $

And a table that says:



A subscribed on 2017.08.01 - Low fee start date = 2018.02.01


I would like to ask Access, how much money A has to pay on 2018.03.01. But I do not know how. It has to be 1 $ but I don't know how to write the query.

Thank you for any advice!

[aytee111]

High fee start date is: DateAdd("m",6,[Low fee start date])

If [select date] > [high fee start date] then amount to pay = high fee, otherwise it is low fee.

[Dueffeldoffel]

Thank you, aytee111!