Results 1 to 7 of 7
  1. #1
    Russellh is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    68

    How to use Variables? For auto populating date range automatically each month

    Each month I want to pull billing records just for that month. Commercial accounts are always billed last each month but the date always varies. Currently I am having to enter the between dates by looking up a commercial account to see when it was billed the previous month (beginning date) and when it was billed in the current month ( add 1 day to it so it is included in query END DATE).

    How would I automate that?



    Each account has a bill month year and then a bill date as fields. So if I want May's records I need how would I pull the bill date field for april as beginning date and I need to pull the bill date for May and add 1 day to it for my end date. What would the syntax look like?

  2. #2
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    164
    Not quit Clear,
    In query filter Criteria Use Between #01/04/2014# To 01/05/2014# to fetch records of may month + 1 day.
    You can also type date in form From Date To Date and Filer Query.
    Hope this will Clear air.

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Build a WHERE clause within your query that uses the BETWEEN operator. In Access 2010 it is super easy. Use the grid at the bottom of the query builder window, in the Criteria field. Start by typing the word BETWEEN.

    Between 1-1-2010 AND 12-31-2014

    The above criteria can be made Dynamic by referencing control(s) on a form and or other field(s) within the query.

  4. #4
    Russellh is offline Advanced Beginner
    Windows 8 Access 2010 32bit
    Join Date
    Apr 2014
    Posts
    68
    Quote Originally Posted by ItsMe View Post
    Build a WHERE clause within your query that uses the BETWEEN operator. In Access 2010 it is super easy. Use the grid at the bottom of the query builder window, in the Criteria field. Start by typing the word BETWEEN.

    Between 1-1-2010 AND 12-31-2014

    The above criteria can be made Dynamic by referencing control(s) on a form and or other field(s) within the query.
    I don't think I am making the problem clear. I don't want to have to enter a date and it will change from day to day or month to month so entering a between will fail the purpose. I would like for it to always the current day's date or read a date from a field and add 1 to it and use that date. But if it reads a date and adds 1 it will have to be like:

    I want all the records will billmonth May and between "Record x's bill date on Bill month April plus 1" and "may bill date plus 1".
    Set up with variable or some method to always chose a starting date that will be the bill date for a record x last month and an ending date that will be the same records May Bill Date plus 1 day (so it captures that record)

    That way nothing ever has to be entered and it can always just be ran. Seeing as the accounts arent billed on the same days each month the range of dates I need all records for changes each month. But I could base the range off of an account that is always the last billed each month. So if I do a > last months bill date and < this months bill date plus 1 i get the range I want. But isnt just this one record I want. I want all the records for all accounts during that range of dates.
    Let me know if that isnt clear. And thanks again for all the help.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    You can cause your parameter to be dynamic by replacing the dates with references to fields within the query and or controls within an open form.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    As ItsMe said, use a form for input of filter criteria into UNBOUND controls and query references the controls. The controls can be set with a default value such as Date() and Date()+1 or some other expression.

    I don't use dynamic parameterized queries. I would use a form or report and apply filter criteria to the form/report when it opens:

    DoCmd.OpenReport "report name", , , "[datefield] BETWEEN #" & Me.tbxStart & "# AND #" & Me.tbxEnd & "#"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Maybe I'm misreading this but the OP seems to want to maintain a history of billing dates such that he can run his billing from the day after the last billing date to the current date.

    If that's the case you could do 1 of two things:

    1. Add a field to your existing detail table something like DATE_BILLED, then update that field when you run your bills. Then you could look for any record newer than your last bill date (or basically any record that doesn't have a bill date at all)

    2. Create a table that tracks your billing periods. This is a bit more clunky but still manageable

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

Similar Threads

  1. Replies: 43
    Last Post: 08-06-2014, 08:21 PM
  2. 13 Month Range?
    By aellistechsupport in forum Queries
    Replies: 7
    Last Post: 05-14-2014, 12:42 PM
  3. Date Range Query Only Returns Month and Day
    By hammer187 in forum Queries
    Replies: 5
    Last Post: 09-18-2012, 11:25 AM
  4. Replies: 4
    Last Post: 05-26-2012, 09:29 AM
  5. Auto populating date.
    By emccalment in forum Access
    Replies: 3
    Last Post: 02-19-2010, 11:00 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