Results 1 to 4 of 4
  1. #1
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185

    Checking All Values In Field against 1 Value - Dates

    Hi All



    In a bit of a bind with my current project and its a bit hard to explain.

    I have a query that currently filters out a table based on 2 working criteria pulled from a form, the first 2 criteria work as designed and the query shows the relevant records from the corresponding table.

    My issue is I need a 3rd criteria based on dates.

    The query has a column with a valid from date in it, and based on the filters there could be numerous records so therefore a potentially long list of valid from dates in that column.

    I then have 1 date value from a form. I need to filter the query based on this date to get 1 record as a result, this is what I can't figure out.

    Here is an example:

    Date From Form = "5/7/2020" (5th July)

    List of Records In Query:

    1/4/2020
    1/7/2020
    1/9/2020

    So I need something to filter out the 1st and 3rd dates above so it just leaves the 1/7/2020 record in the query.

    The logic is FormDate needs to be greater than record valid from date, but not greater than next valid from date.

    Does this make sense?

    I hear you ask, why not add a column with another date for valid to date, the problem is when the record is created, we dont know the valid to date, the valid to date is infinity until we are advised otherwise (and thus make a new record).

    Let me know if any questions, I feel like I explained this poorly.

    Any help would be greatly appreciated.

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    use Q1 to pull all records < formdate, but add a field to calculate the dates diff, DaysDiff: DateDiff("d",[dateFld],[formDate])

    then Q2, (using Q1) to get the closest date: select Min(DaysDiff) from Q1

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Your explanation is a little vague.

    What is the table structure?
    What is the SQL of (the first query) Query1?
    Where is the "next valid from date"? In the current record or the next record?


    I would probably use VBA to find the record........

    Maybe you would post the dB with maybe 10 records and provide a couple of examples of what a "Date From Form" and the results you are looking for.
    Last edited by ssanfu; 08-20-2020 at 05:50 PM.

  4. #4
    stildawn is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Oct 2013
    Posts
    185
    Quote Originally Posted by ranman256 View Post
    use Q1 to pull all records < formdate, but add a field to calculate the dates diff, DaysDiff: DateDiff("d",[dateFld],[formDate])

    then Q2, (using Q1) to get the closest date: select Min(DaysDiff) from Q1
    Thanks Ranman, first part is working I'm just having issue with selecting the min in the second query.

    What exactly do I put in to get the Min(DaysDiff), here is the current SQL:

    Code:
    SELECT Locals_Selected_Query1.Carrier, Locals_Selected_Query1.[POD Name], Locals_Selected_Query1.[20GP THC], Locals_Selected_Query1.[40GP THC], Locals_Selected_Query1.[40HC THC], Locals_Selected_Query1.[BL Flat Fee], Locals_Selected_Query1.[SEC Fee], Locals_Selected_Query1.[SEC Fee Currency], Locals_Selected_Query1.[Valid From], Locals_Selected_Query1.Notes, Locals_Selected_Query1.DaysDiff
    FROM Locals_Selected_Query1;

Please reply to this thread with any new information or opinions.

Similar Threads

  1. checking import values
    By Darla in forum Queries
    Replies: 5
    Last Post: 02-15-2018, 06:44 PM
  2. Checking if values already exist in table
    By doobybug in forum Forms
    Replies: 2
    Last Post: 05-24-2017, 03:12 PM
  3. Password field with input checking?
    By panoss in forum Forms
    Replies: 2
    Last Post: 02-16-2015, 09:55 AM
  4. Replies: 8
    Last Post: 02-12-2014, 06:43 PM
  5. checking availale dates and times
    By Nixx1401 in forum Forms
    Replies: 1
    Last Post: 01-04-2012, 01:54 PM

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