Working with Valid date ranges

Stildawn

Board Regular
Joined
Aug 26, 2012
Messages
187
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?

Thanks
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

ranman256

Well-known Member
Joined
Jun 17, 2014
Messages
1,895
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.
 

Stildawn

Board Regular
Joined
Aug 26, 2012
Messages
187
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

Threads
1,102,351
Messages
5,486,358
Members
407,541
Latest member
Emilybuhman

This Week's Hot Topics

Top