Re: Access Query to request record with date closest to todays dat

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: John Spencer (MVP) (spencer4_at_comcast.net)
Date: 07/29/04 

Date: Thu, 29 Jul 2004 18:40:27 -0400

The query grid is a graphic view of SQL that has a section at the bottom (a
grid) of cells.

Field: SomeDate
Criteria: <= Date()
Sort: Descending

Select View: Properties from the menu
Click on the grey area next to the table in the query
In the Top Values property enter the number 1

Add any other fields you want to see. If you want to eliminate ties, then add
additional sort criteria.

Pete Sperling wrote:
>
> I would like to enter the correct info in the criteria section of the query for the field that contains the date. Is this the same as the "query grid" you mentioned?
>
> "John Spencer (MVP)" wrote:
>
> > Probably a TOP Clause.
> >
> > SELECT TOP 1 FieldA, FieldB
> > FROM YourTable
> > WHERE SomeDateField <= Date()
> > ORDER BY SomeDateField, Desc
> >
> > If you are doing this in the query grid, post back and I will try to describe
> > what to do.
> >
> > Pete Sperling wrote:
> > >
> > > I'm using Access 97. What entry do I make in the Criteria section of a query, for a control which only contains dates, so that I only get the record that contains a date in the past which is closest to or equals todays date. Only want the query to return one record.
> > >
> > > Any help greatly appreciated.
> > >
> > >
> >

Relevant Pages

  • Re: Using MultiSelect with a Listbox as criteria for a report
    ... A Multi-Select ListBox can not be used directly in the Query Grid Criteria. ... Simply examine the output of the Selection and you will see that the string ...
    (microsoft.public.access.formscoding)
  • Re: Duplicate Values Query
    ... If you are using the query grid. ... BUT stair step these criteria one to each of three criteria lines. ... David Patterson wrote: ... > Previous Business Unit ...
    (microsoft.public.access.queries)
  • Re: No records with date criteria
    ... FROM tblPatients INNER JOIN (tblTherapyType INNER JOIN tblPtThpy ... The main change is the WHERE clause asking for dates greater than or equal ... sql below but I use the query grid to ... When I put in a criteria under my date ...
    (microsoft.public.access.queries)
  • Re: Printing labels for alumni database for 21 and older
    ... It appears that you did not add all the needed fields to the query grid. ... Address in addtiontion to the criteria you have under Graduating Class Year. ... Married Name and then Home Street Address and then Home City and then Home ...
    (microsoft.public.access.reports)
  • Re: How do I make a query?
    ... In the query grid: ... Under The "StartDate" field in your query, put your criteria as: ... This should find any records where the start date is in the time period ... I want to pull the fiscal year records. ...
    (microsoft.public.access.queries)