Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    last date as criteria (Access 2000)

    i have a query showing litres per month week and date.
    I need a query that shows the liters only for the last date
    available.is there any possibility to give he query a criteria to show only the last date ?

    SELECT Format([InvoiceDate],'mmmm') AS MonthName, DatePart('m',[invoicedate]) AS MonthNumber, DatePart('ww',[invoicedate]) AS WeekNumber, DatePart('d',[invoicedate]) AS DayNumber, Sum([order details].liters) AS SumOfLiters
    FROM orders INNER JOIN [order details] ON orders.orderid = [order details].OrderID
    GROUP BY Format([InvoiceDate],'mmmm'), DatePart('m',[invoicedate]), DatePart('ww',[invoicedate]), DatePart('d',[invoicedate])
    ORDER BY DatePart('d',[invoicedate]) DESC;

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: last date as criteria (Access 2000)

    If you want to show data for the last date only, add the following criteria to the InvoicePart field:

    DMax("InvoiceDate","Orders")

    and set the Total condition for InvoiceDate to Where.

    Note: there doesn't seem to be much point in grouping on MonthName, MonthNumber, WeekNumber and DayNumber if you only want to show data for the last date.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last date as criteria (Access 2000)

    Thank you for the reply.Of course i will not need the months and the weeks.I have removed them.Somehow i cannot write down the
    line
    DMax("InvoiceDate","Orders").
    Access does not accept this.
    I think it must be some regional peculiarity,because when i write down DMax("InvoiceDate";"Orders")
    with semicoloumn instead of a comma,i get no error, but when i save it and open it it disappears!

    Wil you help ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: last date as criteria (Access 2000)

    If your system uses a comma as decimal separator and a semicolon as list separator, you must use a semicolon in the expression. I don't know why it disappears.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last date as criteria (Access 2000)

    Thank you for your reply.Obvisouly this is something with my computer.The same fault i find also with other queries having no date part.
    For example :
    SELECT Sum([order details].liters) AS SumOfLiters, orders.invoicedate
    FROM orders LEFT JOIN [order details] ON orders.orderid = [order details].OrderID
    GROUP BY orders.invoicedate;
    I have tried to make a new coloumn with and DMax("InvoiceDate","Orders") But i have no success. This entry always disappears when i open the query again.
    Apart from the reason, is there any other way to filter and show only the last date from the query ? I

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: last date as criteria (Access 2000)

    Perhaps you can post a stripped down copy of the database?

  7. #7
    2 Star Lounger
    Join Date
    Aug 2005
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: last date as criteria (Access 2000)

    Dear Hans,

    In Post 520,059 you have recommended to me to use DMax("Invoicedate","orders") and to set the Total condition for Invoicedate to Where.When i try to set it, the whole DMax disappears from my query.If i leave it as it is, i receive false results.For example, in my attachement, in the query QryDailies,the result for Affiliate Number 1 is 338 which is false,338 is the total quantity for all dates.Could you help me put the where condition on the Invoice?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: last date as criteria (Access 2000)

    The idea was to use the DMax expression as criteria, not as a separate column. See attached version.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •