Aging Function
From: Ted Allen (anonymous_at_discussions.microsoft.com)
Date: 03/10/04
- Next message: Dirk Goldgar: "Re: Mysterious Change in Memo Field"
- Previous message: kari: "bookmark variable"
- In reply to: Melody: "Aging Function"
- Next in thread: Melody: "Aging Function"
- Reply: Melody: "Aging Function"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 9 Mar 2004 16:45:15 -0800
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: Dirk Goldgar: "Re: Mysterious Change in Memo Field"
- Previous message: kari: "bookmark variable"
- In reply to: Melody: "Aging Function"
- Next in thread: Melody: "Aging Function"
- Reply: Melody: "Aging Function"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
