Working with Valid date ranges

Stildawn

Board Regular
Joined
Aug 26, 2012
Messages
188
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 total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

ranman256

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

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,101
Messages
5,509,244
Members
408,717
Latest member
Bea2136

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top