Re: Query to show items before a nominated Date

Discussion in 'Microsoft Access Queries' started by John W. Vinson, Apr 16, 2012.

  1. On Sun, 15 Apr 2012 07:41:09 GMT, David Ball <> wrote:

    >Hi,
    >
    >I have a database table of Forecast Delivery Dates.
    >I want to set up a Query to show items that have a forecast delivery date before a certain date. I want to be able to nominate that "certain date" on the fly.
    >
    >I have tried to set up a "Date" field where I can enter a date as a parameter, then have a calculated field to subtract the Forecast dates from the date entered.
    >
    >This returns an error. I think it is because the date returned by my new field doesn't seem to be in date format. I can't find any way to set this to date type.
    >
    >How can I make my new date field return results as dates?
    >
    >Is there a better, more direct way to do this?
    >
    >I tried:
    >
    > InvalidDates: [ForecastDate]<[Enter Date]
    >
    >where "ForecastDate" is the field for my delivery date forecasts and "Enter Date" generates a parameter box where I enter a date. This didn't work either (maybe the date I'm entering into the parameter box is not showing as a date?).
    >
    >Thanks very much for any help with this.
    >
    >Dave
    >
    >
    >


    You can use a Form with a textbox, frmCrit and txtDate let's say, and use a
    Query with a criterion

    < [Forms]![frmCrit]![txtDate]

    It's often helpful to define this in the query's Parameters collection:
    rightclick the background of the tables window in query design; select
    Parameters; copy and paste [Forms]![frmCrit]![txtDate] in the left column of
    the grid and specify Date/Time in the right. You certainly should not be
    creating a new table field to do this.
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Apr 16, 2012
    #1
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Query week to date, month to date, year to date hours

    Guest, Dec 14, 2004, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    722
    Duane Hookom
    Dec 15, 2004
  2. Dustin Swartz
    Replies:
    1
    Views:
    881
    PC Datasheet
    Jan 25, 2005
  3. Display report for nominated client

    , Mar 21, 2006, in forum: Microsoft Access Queries
    Replies:
    0
    Views:
    195
  4. Display report for nominated client

    , Mar 21, 2006, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    230
    Guest
    Mar 21, 2006
  5. Guest

    Date Query: Select all items before 5/Sep/2007

    Guest, Sep 10, 2007, in forum: Microsoft Access Queries
    Replies:
    6
    Views:
    345
    Guest
    Sep 10, 2007
  6. g2v

    Date/Time won't show items on first day in query

    g2v, Oct 1, 2008, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    208
    KARL DEWEY
    Oct 1, 2008
  7. Joe
    Replies:
    6
    Views:
    242
    John W. Vinson
    Mar 26, 2010
  8. Nancy Tang

    start date & end date - show all date in between

    Nancy Tang, May 11, 2010, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    1,265
    Allen Browne
    May 11, 2010
Loading...