Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47

    Unanswered: Form to Date query, how to return previous day?

    I currently have a report that has 4 subreports where the queries pull a date from a form.

    Is it possible to either add a field to my query or create another query that would return data from the previous day, previous week? (something along the lines of (DateX d,-1)
    Furthermore, I do not use this on the weekend, if I were to run the report on monday set it to pull on the previous Friday? (i.e. Workdays only?)

    Currently, I am doing all this work in excel, using pivot tables to calculate the instock percentages, and I'd like to start using access. I've included an excel sample of what I do everyday for reference.

    (Complete Access Rookie, promise I will get better.)
    Attached Files Attached Files
    Last edited by Marsbars; 12-30-09 at 15:47.
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    well did not look at your zip

    as your question is an 'is it possible' the answer is definitely yes.

    you can manipulate dates with +1 or -1 and there is a weekday feature...VBA has alot of date manipulation - that if you go to the Help area or google around - or get a text book for your version of Access you will find elaborate date manipulation methods both embedded in Access' VBA as well as custom code....

    whether or not you enter directly into a query parameter may be questionable - and instead you might want a form so that you can have unbound text boxes that manipulate date variations - - -

  3. #3
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47
    Thanks NTC,

    Let me rephrase. I am already using a form with an unbound text box that my queries are manipulated by, they are then used to create a report.

    Lets say I enter 12/30/2009 in the form, it correctly makes the report, but I want it to also report for 12/29/2009.

    My queries use the criteria: [Forms]![Dates]![DateEntered]
    I can't seem to find (in my books, googling, and searching dbforum) the correct syntax to return [Forms]![Dates]![DateEntered]-1.

    (p.s. My programming experience consists of Intro to Java back in 2002, so please bear with me)
    Last edited by Marsbars; 12-30-09 at 16:16.
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The x-1 should work, though using the DateAdd() function would be more bullet proof (more info in help). It would help to see how you are using it. I would expect this as a query criteria:

    Between x And y

    where x and y are your two dates (or references to form controls, functions, etc).
    Last edited by pbaldy; 12-30-09 at 16:50.
    Paul

  5. #5
    Join Date
    Oct 2009
    Posts
    340
    and you are into a date range

    I usually make a StartDate text box and an EndDate text box....if the span is generally fixed then I will make one a calculated value...in your case StartDate would be:
    =EndDate - 1

    but one can always type over the value to make a longer date span...it is just a convenience factor...

    the query parameter is:
    =>Forms!MyForm.StartDate AND <=Forms!MyForm.EndDate

    be sure to have the format of the text box in the form as a Date type..

  6. #6
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47
    I see, I see.

    Okay, I was trying to add another field to my already existing queries, never got it to work, but what I ended up doing was copying the the queries and changing the date criteria to DateAdd("d",-1,[Forms]![Dates]![DateEntered]), and it worked like a charm.

    Sorry forgot to mention I added the mdb to the zip also, but I've already changed the database so much, I should probably upload it again.
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

  7. #7
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47
    I may be doing this the hard way though.

    I have expressions that build off of sums built into the queries, if I were to use start date and end date do you think these calculations would still work correctly? i.e. using StartDate and EndDate in one query for "category" it would return the expression results for 12/30/2009 and 12/29/2009 seperately? (Instead of using two seperate queries, 1 query to return 12/30 using [Forms]![Dates]![DateEntered] criteria, and another query to return 12/29 using DateAdd("d",-1,[Forms]![Dates]![DateEntered]) criteria.)

    Do I make sense?
    Attached Files Attached Files
    Last edited by Marsbars; 12-30-09 at 18:56. Reason: added updated mdb changes
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

  8. #8
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    hmmm...someone from Bentonville calculating in-stock percentages.

    One can only wonder Who you work for

    Anyway... if you put this little snippet in the forms code module

    Code:
    Function PrevWorkDay()
     Select Case DatePart("w", Now())
        Case vbMonday
          PrevWorkDay = DateAdd("d", -3, Now())
        Case vbSunday
          PrevWorkDay = DateAdd("d", -2, Now())
        Case Else
          PrevWorkDay = DateAdd("d", -1, Now())
      End Select
    End Function
    Then in the StartDate's Default Value put =PrevWorkDay
    it should automatically default to the last working day.
    Last edited by RedNeckGeek; 12-31-09 at 10:31.
    Inspiration Through Fermentation

  9. #9
    Join Date
    Nov 2009
    Location
    Google, KS
    Posts
    47
    Not who you think . I already did my 2 year tour of duty with them.

    I haven't been using startdate/enddate function, I had been using DateAdd() criteria in the date fields of queries.
    I made a form now with startdate and enddate, StartDate's default value =Date() EndDate's default value =PrevWorkDay
    When I change the StartDate value the EndDate does not change...
    I am thinking I put the code in the wrong place in the forms code module.

    Thank you for your help so far everyone.
    Last edited by Marsbars; 12-31-09 at 12:11.
    Life - sexually transmitted, always fatal.

    My beer drunken soul is sadder than all the dead christmas trees in the world.

  10. #10
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's a "ReportForm" type example from the code bank where you can select a "start" date and "end" date as well as do many other things (ie. export the data to an excel or other file type)

    http://www.dbforums.com/6304815-post55.html
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Here's a "ReportForm" type example from the code bank where you can select a "start" date and "end" date as well as do many other things (ie. export the data to an excel or other file type)

    http://www.dbforums.com/6304815-post55.html
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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