Chanchito I'll give it a go:
The domain aggregate functions ,Dlookup(), DSum(), DCount() etc. have 3
components:
1. Which (Field or expression)
2. Where (Table or query)
3. How (Criteria)
Each of them is expressed as *string*:
Dlookup("[SomeField]","SomeTableOrQuery","SomeCriteria")
The first 2 are straight foreword, the criteria is the one that causes
problem
to many people. The simplest example of criteria is:
"[Customer_ID]=2" (Number)
"[FirstName]='Bill' " (string - the single quotation tells Access: "hey, I
am a string)
So far no problem because you specified a constant as a criteria. However,
in most cases you match a filed in a table/query against a filed in your
form.
Suppose yo have a field in your form called Current_ID", if you use
the expression:
"[Customer_ID]=[Current_ID]",
Risky, but Access will still evaluate it correctly because it will look
around and find [Current_ID] on your form and match it against the value
have *in* [Current_ID]. But here comes trouble.
Suppose your Filed name on your form is the same name as on the table
[Customer_ID] and you make the criteria:
"[Customer_ID]=[Customer_ID]"
Access will return the *first* record in your table, why? because
[Customer_ID] is always equal to [Customer_ID] the same way as X=X
(Even I know that
the
criteria [Customer_ID]=[Customer_ID is evaluated as True (-1) and the
whole expression becomes:
DLookup("[SomeFiled]","SomeTable",-1)
In other words, *there is no criteria* if you run DCount you will get the
total number of records, or if it is DSum() you will get the sum across the
whole table/query etc
Now, we have to get Access to look *inside* [Customer_ID] in our *form* to
get
the right result. Remember, the criteria is a **string**! In order force
Access to look into [Customer_ID] in our form we break-up our criteria into
2
components and simply join them together later:
1. "[Customer_ID]=" (this is the field in the table part of the criteria)
2 [Customer_ID] (this is the form part of the criteria.
Joining them together as one string becomes:
"[Customer_ID]=" & [Customer_ID]
As the second [Customer_ID] is not enclosed by quotation marks, Access
evaluate it first and look for a field [Customer_ID], takes the value from
it
and adds it to the criteria. If, say, Access find the value 27 in our field
on the form, it takes the number 27 and adds it to the criteria which
becomes:
[Customer_ID]=" & [Customer_ID] becomes "[Customer_ID]=27"
Bingo!
Now, Remember Bill up above?, it is basically the same principle, except
that
"Bill" is a string. If you are writing an SQL expression, like you will do
in a calculated field on a form, No difference *whatsoever* . Suppose
You have [CustomerFullName] in your query and in your form
=DSum("[InvoiceAmount]","tblInvoices","[CustomerFullName]=" &
[CustomerFullName])
However,
When you are using VBA you *must* tell Access "hey !I'm a string". You do so
by enclosing the Field name in single quotations marks (yes, I know, I can
use
double quotation marks twice but I never do - it confuses the hell out of
me).
We do it the same way as before but adding the single quotation marks:
"[CustomerFullName]=" & " ' " & Me.CustomerFullName & " ' " (enclosing it in
')
access will look at the field on the form "CustomerFullName" and will match
it against "[CustomerFullName] in the table "Invoices". If
[CustomerFullName]="Bill Gates" the criteria is:
"[CustomerFullNeme]='Bill gates' "
On this occasion, because we have tow consecutive double quotations we can
shorten it by adding the left single quotation mark to the right side of the
criteria:
Something=Dlookup("[Customer_ID","tblInvoices","[CustomerFullName]= ' " &
Me.CustomerFullName & " ' ")
I hope it clarifies it for you.
Exam tomorrow ;-)
Regards/JK
chanchito said:
JK,
You rock. I had realized when I first typed your code into the unbound
field that I was to populate it with my own table that is the source of
the form. What needed changing was as you explained, removing the Me.
portion. Once I did that...voila!!!
Now, if I could pick your brain just a bit more. I would really like
to understand the code so that if something similar comes up in the
future I will have a better grasp at how to tackle it. Just typing it
in and seeing it work isn't going to get me any further than where I am
now. =)
I understand that we are using the Dsum statement, and telling it that
we only want the calculation for the total_hours_worked filed, found
within the table "YourTableName", where I get a bit lost is here:
"[Project_name]=' " & [Project_name] & " ' " ) I removed the
spaces where you had indicated, i just wanted it to be easier to see
here.
I can see that the above statement is saying that the field
Project_name must equal whatever is in the field because that is my
intention for it to be that way. But i can't see it based on just the
code itself, I gues the use of the single and double quotations is
throwing me off.
I apologize for beating this to death JK, I just really want to get a
better understanding of how you worked your magic. And, if you choose
to throw the magicians code at me ('a magician never reveals his
secrets') I would understand. =)
cheers
chanchito
=Dsum("[total_hours_worked]","YourTableName","[Project_name]=' " &
Me.Project-Name & " ' ")
(remove the sapces in ' " and " ' ")
