Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    114

    Query working with a date range, combining fields between two tables

    Hi All



    I have a form with a listbox that shows a query, the query builds from two tables (FRT_Table & FRT_Additionals_Table). The current query SQL is below:

    Code:
    SELECT FRT_Table.ID, FRT_Table.[POL Name], FRT_Table.[POD Name], FRT_Table.Carrier, FRT_Table.[Contract Type], FRT_Table.Contract, [FRT_Table].[20GP Cost]+[FRT_Additionals_Table].[20GP BAF]+[FRT_Additionals_Table].[20GP GRI]+[FRT_Additionals_Table].[20GP PSS]+[FRT_Additionals_Table].[20GP MISC] AS [20GP All In], [FRT_Table].[40GP Cost]+[FRT_Additionals_Table].[40GP BAF]+[FRT_Additionals_Table].[40GP GRI]+[FRT_Additionals_Table].[40GP PSS]+[FRT_Additionals_Table].[40GP MISC] AS [40GP All In], [FRT_Table].[40HC Cost]+[FRT_Additionals_Table].[40HC BAF]+[FRT_Additionals_Table].[40HC GRI]+[FRT_Additionals_Table].[40HC PSS]+[FRT_Additionals_Table].[40HC MISC] AS [40HC All In], FRT_Table.[Valid From], FRT_Table.[Valid To], FRT_Table.TransitFROM FRT_Table INNER JOIN FRT_Additionals_Table ON FRT_Table.Carrier = FRT_Additionals_Table.Carrier
    WHERE (((FRT_Table.[POL Name])=Forms![Test Form]!POLCombo Or Forms![Test Form]!POLCombo Is Null) And ((FRT_Table.[POD Name])=Forms![Test Form]!PODCombo Or Forms![Test Form]!PODCombo Is Null) And ((FRT_Table.Carrier)=Forms![Test Form]!CarrierCombo Or Forms![Test Form]!CarrierCombo Is Null) And ((FRT_Table.[Valid To])>=Date()));
    This is working so far and shows the right data etc in the listbox.

    The next step however is working with valid dates, each table has a field for "Valid From" & "Valid To" this forms a date range where the record is valid.

    Snippets of the two tables below for reference:

    Click image for larger version. 

Name:	FRT Table.PNG 
Views:	16 
Size:	17.9 KB 
ID:	41887
    Click image for larger version. 

Name:	FRT Additionals Table.PNG 
Views:	16 
Size:	15.2 KB 
ID:	41888

    In the query (which shows on a listbox) I have three columns that combine three sets of values together:
    • 20GP All In: [FRT_Table].[20GP Cost]+[FRT_Additionals_Table].[20GP BAF]+[FRT_Additionals_Table].[20GP GRI]+[FRT_Additionals_Table].[20GP PSS]+[FRT_Additionals_Table].[20GP MISC]
    • 40GP All In: [FRT_Table].[40GP Cost]+[FRT_Additionals_Table].[40GP BAF]+[FRT_Additionals_Table].[40GP GRI]+[FRT_Additionals_Table].[40GP PSS]+[FRT_Additionals_Table].[40GP MISC]
    • 40HC All In: [FRT_Table].[40HC Cost]+[FRT_Additionals_Table].[40HC BAF]+[FRT_Additionals_Table].[40HC GRI]+[FRT_Additionals_Table].[40HC PSS]+[FRT_Additionals_Table].[40HC MISC]


    Currently the listbox will show duplicate rows for each date range in FRT_Additionals_Table (since I added in extra FRT_Additionals months), a snippet of the listbox below:

    Click image for larger version. 

Name:	Listbox.PNG 
Views:	16 
Size:	26.3 KB 
ID:	41889

    The result I need however is 1 row based on FRT Valid From/Valid To, with the correct FRT_Additionals added in. So the correct result from the above listbox would be as below mock up:

    POL Name POD Name Carrier Contract Type Contract 20GP All In 40GP All In 40HC All In Valid From Valid To
    Singapore Sydney ANL FAK April Test 204 408 462 1/4/20 30/4/20
    Singapore Sydney ANL FAK May Test 240 480 570 1/5/20 31/5/20
    Singapore Sydney ANL FAK June Test 500 1000 1500 1/6/20 30/6/20

    The above mock up combines the right FRT cost (in date range) with the right Additionals (in the date range)

    Hope that explains it well enough.

    I assume something needs to happen in the custom fields of the query to get for both date ranges? Any help / pointers would be greatly appreciated.

    I have also attached my access file if you want to take a look at the whole thing.

    Cheers
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,520
    Why is 204 more correct than 240 or 600 in 20GP field and similarly for 40GP and 40HC ?

    What is the criteria for determining which record to display?

    Why does table sample not show same data in Contract field as listbox?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    114
    Quote Originally Posted by June7 View Post
    Why is 204 more correct than 240 or 600 in 20GP field and similarly for 40GP and 40HC ?
    Because the listbox above is generating the wrong result.

    $204 is correct for date range 1/4/20 to 30/4/20 because the BAF/GRI/PSS/MISC (from FRT_Additionals_Table) for 1/4/20 to 30/4/20 is $1/$1/$1/$1 - So FRT Cost (1/4/20 to 30/4/20) = $200 + BAF/GRI/PSS/MISC (FRT_Additionals_Table for 1/4/20 to 30/4/20) = $204 combined total (or "All In" as the listbox column name).

    Hope that makes sense, currently my query ignores the dates, thats what I need to add in somehow.

    Quote Originally Posted by June7 View Post
    What is the criteria for determining which record to display?
    So listbox is based on FRT_Table records + the values from FRT_Additionals_Table. So the valid from / valid to from FRT_Table (and thus the listbox) needs to be the date range of values grabbed from FRT_Additionals_Table if that makes sense.

    So I guess if I was to say it out loud the statement would be:

    "For the record in FRT_Table, please find and add in the values from FRT_Additionals_Table that are valid within the valid from / valid to dates from FRT_Table"

    Quote Originally Posted by June7 View Post
    Why does table sample not show same data in Contract field as listbox?
    Sorry my bad, I changed the contact field values after I took the snippet, so the listbox etc would make more sense to people reading it. You can ignore contact field, that is working as needed.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,520
    Well, try compound join in query on Carrier, ValidFrom, ValidTo fields. Definitely get different output.

    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    114
    Sorry not familar with this?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,520
    Just link tables in query designer on multiple field pairs. Did you do any research on this topic?

    Do you know how to link fields in query designer?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    114
    Quote Originally Posted by June7 View Post
    Just link tables in query designer on multiple field pairs. Did you do any research on this topic?

    Do you know how to link fields in query designer?
    Ah do you mean relationships? Or at least thats what I thought they were called?

    This is the current link/relationship below based on carrier:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	13 
Size:	10.4 KB 
ID:	41892

    If yes, are you saying I just need to link both the valid from and valid to fields in each table as well?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,520
    Don't have to set it in Relationships although probably wouldn't hurt.

    I am talking about links in the query designer window. And yes, link the date fields.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    114
    Well thanks heaps that seems to have worked like magic. Sorry the last time I did access was like 10 years ago so very very rusty and searching for specific problems is very difficult.

    Can you explain just how what we have done works though? My worry is that they need to be exact matches (the dates I mean), but often the FRT valid dates will be different to the FRT_Additional valid dates but "fall inside the range".

    Further to the above, I have VBA that grabs BAF costs and puts it into textboxes based on user selection in the above listbox, however when I edit this to include dates I get a data type mistmatch error and dont know why:

    Code:
    DLookup("[20GP BAF]", "FRT_Additionals_Table", "[Carrier] = '" & List8.Column(3) & "' And [Valid From] = '" & List8.Column(9) & "' And [Valid To] = '" & List8.Column(10) & " '")
    The old code:

    Code:
    DLookup("[20GP BAF]", "FRT_Additionals_Table", "[Carrier] ='" & List8.Column(3) & "'")
    And:

    Code:
    DLookup("[20GP THC]", "Locals_Table", "[Carrier] = '" & List8.Column(3) & "' And [POD Name] = '" & List8.Column(2) & " '")
    Works just fine, I'm just adding the extra valid from / to fields to match.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,520
    Yes, dates would have to be exact matches for this to work. You basically have a compound key situation with this data. If dates won't be exact match then tables do not have relationship and gets more complicated

    Use # instead of apostrophe to delmit parameters for date/time field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    114
    Quote Originally Posted by June7 View Post
    Yes, dates would have to be exact matches for this to work. You basically have a compound key situation with this data. If dates won't be exact match then tables do not have relationship and gets more complicated
    Hmm, any other way around it?

    If the FRT_Table valid dates were 15/05/20 to 14/06/20, then the listbox would need to show two records 1 with each FRT_Additionals value of both months:

    Valid From 15/05/20 to 31/05/20 - FRT Cost + BAF etc for May

    &

    Valid From 01/06/20 to 14/06/20 - FRT Cost + BAF etc for June

    Quote Originally Posted by June7 View Post
    Use # instead of apostrophe to delmit parameters for date/time field.
    I have replaced as below:

    Code:
    TEUGPBAFValue = DLookup("[20GP BAF]", "FRT_Additionals_Table", "[Carrier] = '" & List8.Column(3) & "' And [Valid From] = #" & List8.Column(9) & "# And [Valid To] = #" & List8.Column(10) & "#")
    However result is blank when it should work. I have checked what the code is generating for the criteria as below:

    Click image for larger version. 

Name:	Capture.PNG 
Views:	13 
Size:	2.9 KB 
ID:	41894

    Which looks legit to me, but the Dlookup is not returning any value let alone the correct value which would be $100 in this case?
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,520
    The correct return is $10, not $100, for dates 5/1/2020 and 5/31/2020. DLookup() works for me.

    You show dates in international format. Review http://allenbrowne.com/ser-36.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    114
    Quote Originally Posted by June7 View Post
    The correct return is $10, not $100, for dates 5/1/2020 and 5/31/2020. DLookup() works for me.
    Yes for May that would be correct, I was using June as my example.

    Quote Originally Posted by June7 View Post
    You show dates in international format. Review http://allenbrowne.com/ser-36.html
    That will be it, we use dd/mm/yyyy.

    I'll try put this function in and see if it works:

    Code:
    Function SQLDate(varDate As Variant) As String    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
        'Argument:   A date/time value.
        'Note:       Returns just the date format if the argument has no time component,
        '                or a date/time format if it does.
        'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
        If IsDate(varDate) Then
            If DateValue(varDate) = varDate Then
                SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
            Else
                SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
            End If
        End If End Function

  14. #14
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,520
    You showed May in first image and June in second.

    Good luck with the date function.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    114
    Ok got it working thanks a great help.

    It works as is so thats something.



    Will have to keep researching / trying to find a solution to the dates being different between FRT and FRT_Additionals (there is also Locals_Table as well with its own set of valid dates).

Page 1 of 2 12 LastLast
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