Working with Valid date ranges


Board Regular
Aug 26, 2012
Hi All

Working on building a Freight Rate database in Access.

I have the attached so far, and for form (Test Form) works as intended, however it doesnt take into account the valid from/valid to fields on each record.

Basically need the below:
- FRT_Table, holds individual freight rates as records
- FRT_Additionals_Table holds individual additional costs that need to be added to the freight rates in FRT_Table
- Locals_Table holds individual local costs that need to be showing on the bottom of the form (test form) when a freight record is selected in the list box

- Each of the tables records have a valid from/valid to date
- The listbox in the Test Form, needs to show relevant freight records (from FRT_Table), and then for columns "20GP All In" / "40GP All In" / "40HC All In" it needs to add the Freight rates from FRT_Table + all the relevant additionals from FRT_Additionals_Table (BAF/GRI/PSS/MISC) to create a "All In" cost. So 20GP All In = 20GP Cost (FRT_Table) + 20GP BAF (FRT_Additionals_Table) + 20GP GRI (FRT_Additionals_Table) + 20GP PSS (FRT_Additionals_Table) + 20GP MISC (FRT_Additionals_Table)

- However it needs to check the valid from/to dates of the freight record and make sure it adds the correct FRT_Additionals based on their valid dates.

- The valid from/to dates on any of the tables can be any date, they are not set periods (e.g 1 month) it could be any set of dates.

Anyone able to help me on this?


Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN


Well-known Member
Jun 17, 2014
Make a form that has 2 text boxes....txtStartDate, txtEndDate.
The base query Q1 pulls this via:
Select * from table where [datefield] between forms!fMyForm!txtStartDate and forms!fMyForm!txtEndDate.

Then in another query, Q2,use Q1 joined to other tables to wet more data.


Board Regular
Aug 26, 2012
Thanks, not sure how this would work for this project.

The user form has two options currently.

All valid rates from today() onwards
All rates including expired rates

So the user doesnt enter in the dates they want.

If the userform shows a bunch of records can I somehow filter another query in the background based on each record shown in the listbox? And then pull the data needed from that query? But would need to be a query for each record shown I think which could be many many records.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...