Nz(DSum

  • Thread starter Thread starter Stephanie
  • Start date Start date
S

Stephanie

Hi. I've use Nz(DSum... before, but I'm not sure how/if
to use it in this case.

I have
Dim strWhere As String
Dim curBucksNumber As Currency
Dim curITABucks As Currency

strWhere = "[ContactID] = " & Nz([ContactID], 0)
curBucksNumber = Nz(DSum("BucksNumber", "BucksMember",
strWhere), 0)

That all works and I understand on curBucksNumber that I
am looking for field BucksNumber in the BucksMember table
given a certain ContactID (that reflects my current
Contact). This is all easy since ContactID is in the
BucksMember table.

Now, I need to find the sum of another field as well, but
ContactID is not in the same table, it's 3 relationships
away. So I tried to use sql which I pasted sql into VB
and it wasn't pretty- couldn't get the syntax correct.
If I do get it right, can I use it in Nz(DSum) and what
would it look like? Or is there a better way
altogether? I appreciate the help and details are nice.

Thanks,
Stephanie
 
Why not create a query containing the ContactID and the fields you want
summed from all the various tables? Taking it a step further, make it an
Aggregate query containign just ContactID and BucksNumber. Your DSum then
becomes a DLookup, ie

curBucksNumber = Nz(DLookup("SumOfBucksNumber", "myAggregateQuery",
strWhere), 0)
 
John,
Elegant! Thanks, I was able to get it working with your
help- great explanation. So excited! Now I understand
better how to use an existing query without pasting sql
into VB- excellent! Thanks so much, Stephanie
-----Original Message-----
Why not create a query containing the ContactID and the fields you want
summed from all the various tables? Taking it a step further, make it an
Aggregate query containign just ContactID and BucksNumber. Your DSum then
becomes a DLookup, ie

curBucksNumber = Nz(DLookup
Click to expand...
("SumOfBucksNumber", "myAggregateQuery",
strWhere), 0)





Hi. I've use Nz(DSum... before, but I'm not sure how/if
to use it in this case.

I have
Dim strWhere As String
Dim curBucksNumber As Currency
Dim curITABucks As Currency

strWhere = "[ContactID] = " & Nz([ContactID], 0)
curBucksNumber = Nz(DSum("BucksNumber", "BucksMember",
strWhere), 0)

That all works and I understand on curBucksNumber that I
am looking for field BucksNumber in the BucksMember table
given a certain ContactID (that reflects my current
Contact). This is all easy since ContactID is in the
BucksMember table.

Now, I need to find the sum of another field as well, but
ContactID is not in the same table, it's 3 relationships
away. So I tried to use sql which I pasted sql into VB
and it wasn't pretty- couldn't get the syntax correct.
If I do get it right, can I use it in Nz(DSum) and what
would it look like? Or is there a better way
altogether? I appreciate the help and details are nice.

Thanks,
Stephanie
Click to expand...


.
Click to expand...
 
You're welcome

Stephanie said:
John,
Elegant! Thanks, I was able to get it working with your
help- great explanation. So excited! Now I understand
better how to use an existing query without pasting sql
into VB- excellent! Thanks so much, Stephanie
-----Original Message-----
Why not create a query containing the ContactID and the fields you want
summed from all the various tables? Taking it a step further, make it an
Aggregate query containign just ContactID and BucksNumber. Your DSum then
becomes a DLookup, ie

curBucksNumber = Nz(DLookup
Click to expand...
("SumOfBucksNumber", "myAggregateQuery",
strWhere), 0)





Hi. I've use Nz(DSum... before, but I'm not sure how/if
to use it in this case.

I have
Dim strWhere As String
Dim curBucksNumber As Currency
Dim curITABucks As Currency

strWhere = "[ContactID] = " & Nz([ContactID], 0)
curBucksNumber = Nz(DSum("BucksNumber", "BucksMember",
strWhere), 0)

That all works and I understand on curBucksNumber that I
am looking for field BucksNumber in the BucksMember table
given a certain ContactID (that reflects my current
Contact). This is all easy since ContactID is in the
BucksMember table.

Now, I need to find the sum of another field as well, but
ContactID is not in the same table, it's 3 relationships
away. So I tried to use sql which I pasted sql into VB
and it wasn't pretty- couldn't get the syntax correct.
If I do get it right, can I use it in Nz(DSum) and what
would it look like? Or is there a better way
altogether? I appreciate the help and details are nice.

Thanks,
Stephanie
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top