Aging Function
From: Melody (anonymous_at_discussions.microsoft.com)
Date: 03/11/04
- Next message: Melody: "Re: Aging Function"
- Previous message: Drew: "sql action qry in vba not matching same qry in design view"
- In reply to: Ted Allen: "Aging Function"
- Next in thread: Ted Allen: "Aging Function"
- Reply: Ted Allen: "Aging Function"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 10 Mar 2004 16:32:14 -0800
Ted,
I took your advice and the query worked! =) (That syntax
sure does get tricky!) Anyway, thank you so much all your
help. I did notice two discrepancies when matching my
control totals however.. it had to deal with the fact that
two of my accounts had total credits which exceeded the
total debits for those accounts, so there were still some
credits that needed to be applied to debit line items that
didn't exist. Do you know if there's a way to state in my
iff statement to: input 0 if [CreditAmt]- [OlderDebits]>
[DebitAmt] unless it's the last line, then input
[CreditAmt] - [DebitAmt]? Actually I'm not concerned with
this because I can always just point this out manually but
I was just wondering if that was possible.
Anyway, thanks again for helping me out! I am still going
to try to use Graham's VBA method, I have to learn
somewhere I guess, so expect to still see me online!
Melody
>-----Original Message-----
>Hi Melody,
>
>I believe that the problem is just with your DSum
>function, so I will focus on that.
>
>Following is the function in your code:
>DSum([Debit],"Debits By Trx Date Copy","[Active or
>Inactive Account]"=" & [Active or Inactive Account]" And
>[Age]>" & [Age]")
>
>I'll offer the following potential corrections:
>
>The first reference to [Debit] must be enclosed in
>quotations.
>
>I'm not sure, but you may need to enclose the table name
>in brackets since it includes spaces (I don't think so,
>but if all of the other recommendations don't fix the
>function you could try it.
>
>There is an extra quotation mark before the = sign
>
>If the Acct No is an alpha, you need to enclose it in
>single quotes by placing a single quote in the string
>before and after the field.
>
>The reference to the query records Account number must be
>preceeded with the table name, because your query
>contains two fields with the same name (one for each
>table)
>
>There is an & missing before the quote before the word AND
>
>You have an extra quotation mark after the last Age field
>reference.
>
>Incorporating these notes, try the following to see if it
>will work:
>
>DSum("[Debit]","Debits By Trx Date Copy","[Active or
>Inactive Account] = '" & [Debits by Trx Date Copy].
>[Active or Inactive Account] & "' And [Age] > " & [Age])
>
>If this does not work, I would try placing brackets
>around the table name as follows:
>
>DSum("[Debit]","[Debits By Trx Date Copy]","[Active or
>Inactive Account] = '" & [Debits by Trx Date Copy].
>[Active or Inactive Account] & "' And [Age] > " & [Age])
>
>Regarding your concern about the problem with the
>accounts being set equal to each other, I think it was
>likely a syntax error in the criteria, rather than a
>problem with using the accounts as criteria. Most likely
>it was a result of the missing single quotes. It is easy
>to make mistakes in the syntax for these criteria. The
>way you have to think of it is that anything not in
>quotes will get replaced with the field values, and then
>everything will get pieced together. Once pieced
>together, the string should result in a valid WHERE
>condition, without the word WHERE. So, if you look at
>just the criteria part of the expression:
>
>"[Active or Inactive Account] = " & [Debits by Trx Date
>Copy].[Active or Inactive Account] & " And [Age] > " &
>[Age]
>
>It will substitute the values for the fields not in
>quotes. So, if you assume that [Debits by Trx Date Copy].
>[Active or Inactive Account] for the current record in
>the query = "A1234" and the Age = "59", the result of the
>concatenation will be:
>
>"[Active or Inactive Account] = 'A1234' And [Age] > 59"
>
>So, the function would sum the "[Debit]" field (your
>first function criteria), in the "Debits by Trx Date
>Copy" table (second criteria), WHERE [Active or Inactive
>Account] = 'A1234' And [Age] > 59
>
>When you see the result of the criteria concatenation, I
>think it is easier to see that the field names inside the
>quotes in your criteria are referring to the table field
>names, while the ones not in quotes are placeholders
>referring to the value of that field for the current
>record in the query. The function uses the resulting
>expression once the query fields (not in quotes) have
>been replaced with their values.
>
>Note that the single quotes are used to designate string
>values. Single quotes are used because double quotes
>would mess up the string concatenation expression. #
>signs are used to similarly denote date values (such as
>#3/9/04#).
>
>One other thing, I don't remember ever seeing whether you
>had checked to see if you will have duplicate age values
>for a given acct. If so, you may need to revise the
>formula somewhat.
>
>Hopefully this will help. I remember how much it helped
>me when I finally got the hang of the domain functions.
>They really open up a lot of opportunities.
>
>Post back and let me know if you are able to get it
>working.
>
>-Ted Allen
>
>>-----Original Message-----
>>Ted,
>>
>>BTW, as followup to my last post(pasted below), I
>believe
>>the error has something to do with setting the accounts
>>equal to each other in the dsum function, because when I
>>used only the dates as criteria I was able to generate
>>values but not when using the account criteria. The
>>accounts [Active or Inactive Account] are text strings.
>I
>>played around with the & and " characters but it did not
>>help. If you happen to know why this is not working,
>>please let me know.
>>
>>Thanks,
>>Melody
>>
>>
>>Ted, I finally gotcha. I think the query method will
>work
>>for the results I need. I did as you recommended and
>>started off by creating a field which calculates the
>dsum
>>for each record in the debit table, although for some
>>reason, all OlderDebits values are null.
>>
>>This is the SQL code which I can't get to work:
>>SELECT [Debits by Trx Date Copy].[Active or Inactive
>>Account], [Debits by Trx Date Copy].[Trx Date], [Debits
>by
>>Trx Date Copy].Age, [Debits by Trx Date Copy].Debit, DSum
>>([Debit],"Debits By Trx Date Copy","[Active or Inactive
>>Account]"=" & [Active or Inactive Account]" And [Age]>"
>&
>>[Age]") AS OlderDebits, [Total Credits By Account
>>Copy].TotalCredit
>>FROM [Debits by Trx Date Copy] LEFT JOIN [Total Credits
>By
>>Account Copy] ON [Debits by Trx Date Copy].[Active or
>>Inactive Account] = [Total Credits By Account Copy].
>>[Active or Inactive Account];
>>
>>After I get the dsum calculation to work within Query
>1,
>>I plan to run a 2nd query from query 1, since when i try
>>to refer to OlderDebits within query 1, it prompts me
>for
>>an OlderDebits parameter.
>>
>>Can you please help me figure out what I'm doing wrong
>in
>>the above code?
>>
>>Thanks,
>>Melody
>>
>>.
>>
>.
>
- Next message: Melody: "Re: Aging Function"
- Previous message: Drew: "sql action qry in vba not matching same qry in design view"
- In reply to: Ted Allen: "Aging Function"
- Next in thread: Ted Allen: "Aging Function"
- Reply: Ted Allen: "Aging Function"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
