Results 1 to 3 of 3
  1. #1
    neill_long is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2018
    Posts
    9

    Where condition on query to return specific data, using date fields

    I am trying to specify in a query that I want it to only show data that meet a specific criteria. Only records with a lost date from two months ago. So for example, this being June, it should only show records from April 2018. July would show May and so on.

    I have put the following condition (which I know is not the correct one for my particular question, but I was hoping I would be able to work on it to suit my requirements):

    [MaxOfLostDate]>=DateSerial(Year(Now())-1,1,1) And [MaxOfLostDate]<=DateSerial(Year(Now())-1,Month(Now()),Day(Now()))


    This kind of works but it should show records from last year to present date, but it only shows last years data.



    Does anyone know what I can do to fix this to meet my requirements or have another criteria I could use?

    thanks
    Alistair

  2. #2
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You would need something like

    Code:
    Between DateSerial(Year(Date()), Month(Date())-2, 1) And DateSerial(Year(Date()), Month(Date())-1, 1)-1
    A good way to experiment with these types of expression is to open the VBA debug window press Ctrl+G to get the immediate window up.
    Now type ? DateSerial(Year(Date()), Month(Date())-1, 1)-1
    And hit return.

    Code:
    ? DateSerial(Year(Date()), Month(Date())-1, 1)-1
    30/04/2018
    The ? tells the editor to print the result to the immediate window.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    neill_long is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jun 2018
    Posts
    9
    Brilliant, thanks very much for your help.

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

Similar Threads

  1. Replies: 7
    Last Post: 06-25-2015, 01:03 PM
  2. Replies: 1
    Last Post: 11-03-2014, 12:31 PM
  3. Query to find condition and return field name
    By kagoodwin13 in forum Queries
    Replies: 3
    Last Post: 01-15-2014, 02:04 PM
  4. Replies: 1
    Last Post: 12-31-2012, 06:25 PM
  5. Replies: 3
    Last Post: 08-15-2011, 10:06 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