Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Maybe I'm looking at this wrong.

    I could replace the whole query in the listbox idea, and just dynamically create the listbox data using VBA and DLookups into the rowsource? Is that possible.

    What if I had one listbox1, using a query that just grabs the ID / POL / POD / Carrier / Contact Type / Contract / Valid From / Valid To fields.

    Then put another listbox2 right next to is so it looks like the same listbox, and the columns in listbox2 have 20GP All In / 40GP All In / 40HC All In, but the rows are dynamically built in VBA by cycling through listbox1 rows and manually checking the dates via VBA and putting in the correct values to line up with the rows in listbox1....

    Would that work? It would be a lot of code but I think I can manage it, I'm fairly good at VBA just not overly good at Access

  2. #17
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    The Great Land
    Certainly the coding is possible. Is it worth it? Only you know.
    How to attach file: To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #18
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    I smashed this up really quick on a test form to see if the concept works:

    Private Sub List3_AfterUpdate()
    If List3.ListCount > 0 Then
        ListArrayRowSource = "20GP All In;40GP All In;40HC All In;"
        For i = 1 To List3.ListCount - 1
            ValidFromDate = SQLDate(List3.Column(6, i))
            ValidToDate = SQLDate(List3.Column(7, i))
            List3RowID = DLookup("[ID]", "FRT_Additionals_Table", "[Carrier] = '" & List3.Column(3, i) & "' AND " & ValidFromDate & " Between [Valid From] AND [Valid To]")
            Found20FRTValue = DLookup("[20GP Cost]", "FRT_Table", "[ID] = " & List3.Column(0, i))
            Found20BAFValue = DLookup("[20GP BAF]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
            Found20GRIValue = DLookup("[20GP GRI]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
            Cal20GPAllInValue = Found20FRTValue + Found20BAFValue + Found20GRIValue
            Found40FRTValue = DLookup("[40GP Cost]", "FRT_Table", "[ID] = " & List3.Column(0, i))
            Found40BAFValue = DLookup("[40GP BAF]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
            Found40GRIValue = DLookup("[40GP GRI]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
            Cal40GPAllInValue = Found40FRTValue + Found40BAFValue + Found40GRIValue
            Found40HCFRTValue = DLookup("[40HC Cost]", "FRT_Table", "[ID] = " & List3.Column(0, i))
            Found40HCBAFValue = DLookup("[40HC BAF]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
            Found40HCGRIValue = DLookup("[40HC GRI]", "FRT_Additionals_Table", "[ID] = " & List3RowID)
            Cal40HCAllInValue = Found40HCFRTValue + Found40HCBAFValue + Found40HCGRIValue
            ListArrayRowSource = ListArrayRowSource & Cal20GPAllInValue & ";" & Cal40GPAllInValue & ";" & Cal40HCAllInValue & ";"
        Next i
        List5.RowSource = ListArrayRowSource
    End If
    SelectedValue = List3.ListIndex + 1
    List5.Selected(SelectedValue) = True
    End Sub
    Seems to work, just need to add formating in etc.

    But I need to working on the date selection part, to see if I can get what I want out of it.

    I'm surprised that Access doesnt have a more elegant want to handle this sort of thing though, surely I'm not the first in the world to want to apply values based on valid dates.

  4. #19
    Cottonshirt is offline Advanced Beginner
    Windows 8 Access 2007
    Join Date
    Feb 2018
    currently my query ignores the dates, thats what I need to add in somehow.
    the difficulty you are experiencing is caused by your process being back to front. you have written your query and are trying to "add in" the consideration of dates, whereas the correct process is to consider dates first, and treat them as a fundamental aspect of your database design.

    step 1. SELECT records by the date range of interest
    step 2. process them

    when you have date ranges, the solution I have found that works is to have a table that:

    1. names each date range. call it a period, term, financialmonth, call it whatever you like but each period has a name. financial years can be named 2015, 2016, 2017, school terms have names, and so forth.

    2. the table specifies a start and end date for each period. so your table needs a minimum of three fields.

    here is an example from a budgeting database I use.

    tbl_period has three fields, tbl_period.period, tbl_period.perstart, tbl_period.perend

    period..... perstart................. perend
    2013.......01 Apr 2013.......... 31 Mar 2014
    2014.......01 Apr 2014.......... 31 Mar 2015
    2015.......01 Apr 2015.......... 31 Mar 2016
    2016.......01 Apr 2016.......... 31 Mar 2017

    the first step in any data processing then becomes adding the period name as a field in the query, so that each record has the name of the period to which it applies. analysis then becomes a trivially easy AGGREGATE function with GROUP BY on the period name.

    to check whether the date on each record falls within the date range, I find BETWEEN is most useful here, and a typical query will look like this:

    SELECT tbl_period.period, tbl_costs.effdate, other fields as required...
    FROM tbl_period, tbl_costs 
    WHERE (((tbl_costs.effdate) Between [tbl_period].[perstart] And [tbl_period].[perend]));
    surprisingly, there is no need to join the two tables for this to work, which means the dates do not have to be an exact match, they just need to be within the range defined by your table.

    good luck with your project,


  5. #20
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Thanks for your help.

    The issue I see above is that there are not set periods per see, for example, the FRT rate entered could have any start and any end date, it could be 1 month, it could be a whole quarter, it could be a few weeks.

    So one record might be valid 1/5/20 to 30/5/20, but another could be 1/5/20 to 14/5/20, or 1/5/20 to 30/06/20, they could be anything.

    In regards the additionals table, they are usually whole months, but again could literally be anything as we have no idea what the suppliers will dictate beforehand.

    Does the table for periods still work?

    So the FRT table date ranges will likely straddle a few of the FRT_Additionals date ranges, how would that work in regards to a period table?

  6. #21
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    New database attachment
    Attached Files Attached Files

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-21-2017, 03:07 PM
  2. Replies: 10
    Last Post: 12-15-2016, 05:52 PM
  3. Replies: 4
    Last Post: 04-16-2015, 05:01 AM
  4. Query Date Range Based on Calculated Fields
    By wilsoa in forum Queries
    Replies: 6
    Last Post: 03-08-2012, 02:41 PM
  5. Date range not working
    By victoria61485 in forum Queries
    Replies: 4
    Last Post: 09-08-2011, 08:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums