Gross Totals For Tax Year

[Guest]

i have a qry called "qryGrossWages, with the follwoing fields

EmployeeID Gross Wages Dates: Year([Date])
qryWages qryWages
Group By Sum Group By

All works fine and when run looks like this

1 150 2005
2 507 2005

What im trying to do is sum the wages up for the current tax year and not
the current year jan - dec. the tax year starts 6th april.

Can anyone help

 

[Douglas J. Steele]

Add a computed field to your query that calculates which tax year it is, and
use that instead of Year([Date])

 

[Gary Walter]

i have a qry called "qryGrossWages, with the follwoing fields

EmployeeID Gross Wages Dates: Year([Date])
qryWages qryWages
Group By Sum Group By

All works fine and when run looks like this

1 150 2005
2 507 2005

What im trying to do is sum the wages up for the current tax year and not
the current year jan - dec. the tax year starts 6th april.

Hi Stu,

PMFBI

To expand on Doug's sage advice,
here be a typical method for the computed field:

I believe you want 4/6/2005 to be the first day
of your tax year.

If we subtract 3 months from it

MyDate=#4/6/05#
?DateAdd("m",-3,MyDate)
1/6/2005

then subtract 5 days

?DateAdd("m",-3,MyDate)-5
1/1/2005

then 4/6/05 can be thought of
as the "first day" of tax year 2005

============================
So I believe your calc field would look like:

TaxYear: DatePart("yyyy",DateAdd("m",-3,[YourDateField)-5)

===========================
*quick* tests in Immediate Window:

MyDate=#4/5/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2004

MyDate=#4/6/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2005

MyDate=#1/1/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2004

MyDate=#4/7/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2005

Again apologies for butting in,

gary

 

[Douglas J. Steele]

Thanks, Gary. I realized after the fact that I was a little too terse... <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

i have a qry called "qryGrossWages, with the follwoing fields

EmployeeID Gross Wages Dates: Year([Date])
qryWages qryWages
Group By Sum Group By

All works fine and when run looks like this

1 150 2005
2 507 2005

What im trying to do is sum the wages up for the current tax year and not
the current year jan - dec. the tax year starts 6th april.

Hi Stu,

PMFBI

To expand on Doug's sage advice,
here be a typical method for the computed field:

I believe you want 4/6/2005 to be the first day
of your tax year.

If we subtract 3 months from it

MyDate=#4/6/05#
?DateAdd("m",-3,MyDate)
1/6/2005

then subtract 5 days

?DateAdd("m",-3,MyDate)-5
1/1/2005

then 4/6/05 can be thought of
as the "first day" of tax year 2005

============================
So I believe your calc field would look like:

TaxYear: DatePart("yyyy",DateAdd("m",-3,[YourDateField)-5)

===========================
*quick* tests in Immediate Window:

MyDate=#4/5/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2004

MyDate=#4/6/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2005

MyDate=#1/1/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2004

MyDate=#4/7/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2005

Again apologies for butting in,

gary

 

[Guest]

thanks for info gents, you have been a great help. I need to do some reading
and understand the date functions

i've noticed the date format you have used is March 01 2005 format.

can it be used in 01 March 2005 format??

i have a qry called "qryGrossWages, with the follwoing fields

EmployeeID Gross Wages Dates: Year([Date])
qryWages qryWages
Group By Sum Group By

All works fine and when run looks like this

1 150 2005
2 507 2005

What im trying to do is sum the wages up for the current tax year and not
the current year jan - dec. the tax year starts 6th april.

Hi Stu,

PMFBI

To expand on Doug's sage advice,
here be a typical method for the computed field:

I believe you want 4/6/2005 to be the first day
of your tax year.

If we subtract 3 months from it

MyDate=#4/6/05#
?DateAdd("m",-3,MyDate)
1/6/2005

then subtract 5 days

?DateAdd("m",-3,MyDate)-5
1/1/2005

then 4/6/05 can be thought of
as the "first day" of tax year 2005

============================
So I believe your calc field would look like:

TaxYear: DatePart("yyyy",DateAdd("m",-3,[YourDateField)-5)

===========================
*quick* tests in Immediate Window:

MyDate=#4/5/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2004

MyDate=#4/6/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2005

MyDate=#1/1/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2004

MyDate=#4/7/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2005

Again apologies for butting in,

gary

 

[Guest]

Gent,

Please dont read my last comment, it makes me sound thick.

thanks for info gents, you have been a great help. I need to do some reading
and understand the date functions

i've noticed the date format you have used is March 01 2005 format.

can it be used in 01 March 2005 format??

i have a qry called "qryGrossWages, with the follwoing fields

EmployeeID Gross Wages Dates: Year([Date])
qryWages qryWages
Group By Sum Group By

All works fine and when run looks like this

1 150 2005
2 507 2005

What im trying to do is sum the wages up for the current tax year and not
the current year jan - dec. the tax year starts 6th april.

Hi Stu,

PMFBI

To expand on Doug's sage advice,
here be a typical method for the computed field:

I believe you want 4/6/2005 to be the first day
of your tax year.

If we subtract 3 months from it

MyDate=#4/6/05#
?DateAdd("m",-3,MyDate)
1/6/2005

then subtract 5 days

?DateAdd("m",-3,MyDate)-5
1/1/2005

then 4/6/05 can be thought of
as the "first day" of tax year 2005

============================
So I believe your calc field would look like:

TaxYear: DatePart("yyyy",DateAdd("m",-3,[YourDateField)-5)

===========================
*quick* tests in Immediate Window:

MyDate=#4/5/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2004

MyDate=#4/6/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2005

MyDate=#1/1/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2004

MyDate=#4/7/05#
?DatePart("yyyy",DateAdd("m",-3,MyDate)-5)
2005

Again apologies for butting in,

gary

 

[Guest]

Gents,

One last thing if you dont mind, my qry now looks like this
EmployeeID Sum Tax Year
1 350 2004
1 150 2005
2 507 2005

i have a dlookup function in a form which goes like this, which works fine
except it doesnt take into account the current tax year

=DLookUp("[SumOfGrossWages]","qryGrossWages","[EmployeeID] =
Forms!frmWages!Combo39")+[Wages Total]

i have tried adding And "[Tax Year]"=Now() to the end but doesnt work

 

[Gary Walter]

Hi Stu,

The function DatePart returns a variant (Integer),
not a Date/Time.

So, the additional clause would probably need
to be something like:

" AND [Tax Year] = Year(Date())"

Truly, I don't understand how your current DLookUp
is working "fine."

I could be wrong, but I would have thought (all 1 line):

=NZ(DLookUp("[SumOfGrossWages]","qryGrossWages",
"[EmployeeID] = " & Forms!frmWages!Combo39
& " AND [Tax Year] = Year(Date())"),0) + [Wages Total]

Again, I could be mistaken, but whenever I find myself
thinking about using a domain function in a control on a
form (or report), I might go back and rethink the query
that the form is based on. Of course, using aggregates
in a query makes the query non-updateable which may
be what is happening here....

good luck,

gary

Gents,

One last thing if you dont mind, my qry now looks like this
EmployeeID Sum Tax Year
1 350 2004
1 150 2005
2 507 2005

i have a dlookup function in a form which goes like this, which works fine
except it doesnt take into account the current tax year

=DLookUp("[SumOfGrossWages]","qryGrossWages","[EmployeeID] =
Forms!frmWages!Combo39")+[Wages Total]

i have tried adding And "[Tax Year]"=Now() to the end but doesnt work

i have a qry called "qryGrossWages, with the follwoing fields

EmployeeID Gross Wages Dates: Year([Date])
qryWages qryWages
Group By Sum Group By

All works fine and when run looks like this

1 150 2005
2 507 2005

What im trying to do is sum the wages up for the current tax year and not
the current year jan - dec. the tax year starts 6th april.

Can anyone help