Nz(DSum

[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

 

[JohnFol]

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)

 

[Stephanie]

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

("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

.

 

[JohnFol]

You're welcome

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

("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

.