Query summing a vba calc gets mutliplied

[soulice]

I have a simpel query:
select field1, sum(VBACalc(field2,field3)) as thing, field 4
from table
groupby field1,thing, field4

seems to run fine, but always mutilplies the result by 4. I have edited my database table to have only 1 record. Stil multiplies by 4. I set a stop itneh VBACalc (which takes a date field and a number, finds a number based on the date field and multiplies the 2 numbers) and it cycles through 4 times. Not sure why...

 

[Pat Hartman]

I think we'll need to see the function code.

 

[soulice]

Public Function GetTaxPercentage(DonationDate As Date, DonationValue As Double)
Dim tax As Double
Set db = CurrentDb()

LSQL = "Select percentage from TaxPercentage"
LSQL = LSQL & " where StartDate <= #" & DonationDate & "# and EndDate >= #" & DonationDate & "#"

Set Lrs = db.OpenRecordset(LSQL)
If Lrs.EOF = True Then
tax = 0.65
MsgBox "The tax percentage could not be retrieved."

Else
tax = Lrs("percentage")

End If

Lrs.Close
Set Lrs = Nothing
Set db = Nothing

GetTaxPercentage = tax * DonationValue
End Function

 

[vbaInet]

You can do this in a query. No need for a function.

In a new column in your query put the following:

[COLOR=Blue]AliasName[/COLOR]: Nz((SELECT [Percentage] From TaxPercentage WHERE [StartDate] <= [DonationDate] AND EndDate >= [DonationDate]), 0.65) * [DonationValue]

... rename AliasName to your liking.

 

[soulice]

Looks good. Will swap the 0.65 with the dollar value and I should be good to go. (the slect TaxPercantage gets the 0.65, or whatever the percentage is for the date range.)
THanks. will test and post back.

 

[soulice]

I still get a 4x result...very odd

 

[vbaInet]

Let's see your entire SQL statement exactly as you have it now.

 

[soulice]

SELECT BusinessDonation.DonationTo, Sum(Nz((SELECT [Percentage] From TaxPercentage WHERE [StartDate] <= [BusinessDonation.DonationDate] AND EndDate >= [BusinessDonation.DonationDate]),0.65)*[BusinessDonation.DonationValue]) AS TaxCredit, DonationRecipients.ApprovedTaxCreditAmount
FROM TaxPercentage, DonationRecipients INNER JOIN BusinessDonation ON DonationRecipients.RecipientID = BusinessDonation.DonationTo
WHERE (((BusinessDonation.TaxYear)=[Year]))
GROUP BY BusinessDonation.DonationTo, DonationRecipients.ApprovedTaxCreditAmount, BusinessDonation.DonationDate;

User is prompted for Year.

 

[vbaInet]

The join between the two tables is most likely causing duplicate records, hence, the quadruple value. Let's see your db.

 

[soulice]

Turned out to be the join...

 

[vbaInet]

Good to know that's sorted.