Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2016
    Posts
    1

    Unanswered: Query Based on Form Dates

    Hello, I am getting back into Access and having trouble figuring out how to get the below to work. I've tried TempVar, Subforms, et cetera but cannot seem to get the right combination.

    Essentially, I have two tables: 1) Opening balances in accounts 2) transactions with dates, categories, amounts, account, description

    I have a query and form that gives me the current balance by grouping the two tables by accounts and summing the opening balances and transaction amounts.

    What I am trying to accomplish, is to have a form or subform return the balances as of a certain date. Meaning that the once a date is input into a 'Short Date' field, the query filters out transactions after that date and returns the summation of the accounts.

    Anyone have any advise as far as direction I should be headed?

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You'd do the same thing, but add a criteria on the date field. In design view, make sure you change Group By to Where.
    Paul

  3. #3
    Join Date
    Apr 2006
    Posts
    1

    re:Query Based on Form Dates

    In the criteria section of the query, you could put something like

    >=[Forms]![FrmReports]![DtCritStart] And <=[Forms]![FrmReports]![DtCritStop]

    with [DtCritStart] and [DtCritStop] being text boxes on the FrmReports form where the user makes a selection.

    Gary

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a balance enquiry HAS to run from a known starting point (date of the most recent PRECEEDING opening balance), up to and including the specified closing point. you can make life easier by coericing the start point by only allowing the user to specify a start point that also has an opening balance. do that by say offering the start point as a choice in a combo/list box.

    however your main problem is going to be how to identify what date rows are relevant, and that is primarily down to whether or not you have stored a time component as part of your transaction date. and that depends on your design. like most db systems Access/JET stores dates in a date time value (its actually a decimal number where the integer portion of the number is number of says since, IIRC 31 Dec 1899, and the decimal part id the proportion of the day.

    so 1.0 is (I think) 01 Jan 1900 00:00:00
    1.5 is 01 Jan 1900 12:00:00
    7.75 is 07 Jan 1900 18:00:00

    so when it comes to identifying your date bands you need to know if the time component is relevant (if you have only ever stored a date then you are fine), and if there is a risk that you may have some transactions for the same day but included in the opening balance of the same day, again if you don't then its not a problem

    so say you want transactions for the month of Jan 2016, then
    1) you need the opening balance for Jan 2016
    2) all transctions from 01 Jan 2016 to 31 Jan 2016
    ..which equates to where mydatecolumn BETWEEN #2016/01/10# AND #2016/01/31#
    but if you have specified a time element then you need to cater for any transaction that occurred on the 31st of jan
    ..which equates to where mydatecolumn BETWEEN #2016/01/10# AND dateadd("d",1,cdate("2016/01/31"))
    OR
    where mydatecolumn >= #2016/01/10# AND mydatecolumn <=#2016/02/01"

    you can always get the opening balance by using say a domain function such as dlookup

    eg OpeningBal = dlookup("value", "mybalancetable", "AccountNo=''WISLO006' AND mydatecolumn = #2016/01/01#)
    ..assumign the table contining the opening balances is called mybalancetable
    AND the openign balance column is called "value", it has a account number column called accountno and the date of the blance is in a column called mydatecolumn

    the # delimits a date literal and the following text should be in either ISO date format yyyy/mm/dd or US format mm/dd/yyyy

    you can probably use a join in the transactions query to get the opneign balance (take note of it for the first row processed, and ignore in all subequent rows being processd eg:-

    select A.balance, T.txdate, T.Value from Accounts as A, Transacations as T
    left join T as A.ACCOUNTID = T.ACCOUNTID
    where A.BALANCEDATE = #2016/01/01# AND
    T.txdate = between #2016/01/01# AND #2016/01/31# AND

    it also depends on how you store the opening balance. this approach assumes that you store an opening balance for each period irrespective of whether there are any transactions ont he the account that period

    if not then you have to find the moist recent openinhg balance on the account and then apply all transaction from that point forward.

    what I tend to do in such situations is store everything in the transaction table with a time as well as a datestamp (remember Access/JET stores time and date values in the same column so they can ]and SHOULD] be the same column)
    have a transaction ID that identifies the type of tracnsactionit is and how it affects the value either + or -1 and multiply by that
    eg
    0 = opening balance, effect +
    1 = payment received +
    2 = invoice raised -
    3 = credit note +
    4 = debit note -
    10 writeoff +
    ..and so on.

    then the balance becomes
    select account, sum(transaction_value * TT.affect) from transaction as T
    join transactionTypes as TT on T.TransactionType = TT.TransactionType
    where t.txdate between #2016/01/01# and #2016/01/31#
    group by account
    Last edited by healdem; 03-29-16 at 09:13.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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