Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58

    Query Help

    I need help with a query.


    My database has a [SaleDate] field which is defaulted to 1st day of last month (i.e., [SaleDate] for any day in February would be 01/01/2020).
    I am writing a query to retrieve data prior to the month of [SaleDate].
    For example if my [SaleDate] is January 2020, i need to retreive data of December 2019.

    Can somebody help me with the criteria i need to enter in the query design?

    Thanks in Advance.

    Mismag.

  2. #2
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    DateAdd("m", -1. [SaleDate])

  3. #3
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    Thank you for the reply....
    Quote Originally Posted by Bulzie View Post
    DateAdd("m", -1. [SaleDate])
    But this isn't working.

    Thanks,
    Mismag

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Where are you getting the date to use as criteria? A form?

    If you want to select all records where sales date is equal to 12/1/2019 (for Jan 2020), do you enter a date like 12/1/2019 (the SaleDate) or 1/5/2020 (any date in the month)?

    I would not think you would use the field "SalesDate".

    So lets say the table name is "tblSales"
    There is a form named "frmSearch "with an unbound control named "ubSaleDate".
    You would enter 12/1/2019 to get the sales records for January 2020

    The query would be
    Code:
    SELECT tblSales.SaleID_PK, tblSales.RecDate, tblSales.SaleDate
    FROM tblSales
    WHERE tblSales.SaleDate=[forms]![frmSearch].[ubSalesDate];


    If you wanted to enter ANY date in January 2020 to get Dec sales:
    Code:
    SELECT tblSales.SaleID_PK, tblSales.RecDate, tblSales.SaleDate
    FROM tblSales
    WHERE (((tblSales.SaleDate)=DateSerial(Year([Forms]![frmSearch].[ubSalesDate]),Month([Forms]![frmSearch].[ubSalesDate])-1,1)));

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I edited the prev post, so this is to sent notification.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Bulzie's solution has a typo - should be a comma after -1, not a stop

    DateAdd("m", -1, [SaleDate])

  7. #7
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    Quote Originally Posted by ssanfu View Post
    Where are you getting the date to use as criteria? A form?

    [/CODE]
    It is coming from a form.
    But my database is split into front end and backend.
    I am writing the query in the backend. And form is in front end database.
    So I am not able to use the code.
    Thank you ....

    Mismag

  8. #8
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    Ajax,

    I corrected the statement and tried. It is still not working.

    Thanks,
    Mismag

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Why are you putting the query in the BE?? It should be in the FE!
    Only tables should be in the BE.

  10. #10
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    Quote Originally Posted by ssanfu View Post
    Why are you putting the query in the BE?? It should be in the FE!
    Only tables should be in the BE.
    I am new to Access.
    When i first created the database, i didn't have BE and FE.
    I split the database after I created queries and it automatically put the queries in BE.

    Let me try to create the same query in FE and see.
    Thank you SSanfu.

    Regards,
    Mismag

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The BE should have only tables.
    The FE should have all other objects.

  12. #12
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Just to clarify, mismag when you say "I am writing a query to retrieve data prior to the month of [SaleDate]." Do you mean all records before SaleDate or just that previous Month only. So if Satedate is 1/1/2020, you only want to see data for month of Dec 2019 so 12/1/2019 to 12/31/2019 or do you mean any records prior to 1/1/2020?



  13. #13
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    Quote Originally Posted by Bulzie View Post
    Just to clarify, mismag when you say "I am writing a query to retrieve data prior to the month of [SaleDate]." Do you mean all records before SaleDate or just that previous Month only. So if Satedate is 1/1/2020, you only want to see data for month of Dec 2019 so 12/1/2019 to 12/31/2019 or do you mean any records prior to 1/1/2020?


    Only previous month.

    Regards,
    Mismag

  14. #14
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Can they enter any date in the search field on your form? For instance can they enter 2/10/2020 and you would still want to pull all records with 1/1/2020 right?

    I think ssanfu Dateserial would work but unless I am reading it wrong which could be, not sure the example figures the year right if it is say 1/1/2020, the date to search would be 12/1/2019 for the criteria so thinking any method has to use that previous month's date. Another option using the search field example could be:

    "#" & Datepart("m",DateAdd("m",-1,[Forms]![frmSearch].[ubSalesDate])) & "/1/" & Datepart("y",DateAdd("m",-1,[Forms]![frmSearch].[ubSalesDate])) & "#"

    Not sure all the syntax is 100% but thinking this should subtract 1 month from the entered date of Sales, then get that month, default 1 for the day and then the year from that same search date minus 1 month.

  15. #15
    mismag is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2019
    Location
    California, US
    Posts
    58
    Can they enter any date in the search field on your form? For instance can they enter 2/10/2020 and you would still want to pull all records with 1/1/2020 right?
    I am sorry if i wasn't clear in explaining my requirement properly.

    The user will not be searching for any date.
    In my database every month I enter last month's sales data.
    Irrespective of the order date, my sale date would be 1st day of last month. So for example, in february 2020, i enter January 2020's sales data and my [SaleDate] would be 1/1/2020, for all the orders.

    I am trying to write a query to display only Dec 2019 sales information (in this example).

    Hope this clarifies the requirement.


    Regards,
    Mismag

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

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