Get month between non calendar dates

G

Guest

Office 2007

I want to be able to get the Tax Month from a given pay date.

Tax months run from 6 April to 5th May, 6th May to 5th June etc

If the payment date is 19th April the Tax Month = 1
If the payment date is 4th July the Tax Month = 3 etc

I have a the following function that is called from the query (Date are UK
dd/mm/yyyy) but whatever the date is it always gives Tax Month 1

What am I doing wrong

Thanks

Public Function GetTaxMth(datStart As Date) As String
Select Case datStart
Case Is > #4/5/2006# And datStart < #5/6/2006#
GetTaxMth = 1
Case Is > #5/5/2006# And datStart < #6/6/2006#
GetTaxMth = 2
Case Is > #6/5/2006# And datStart < #7/6/2006#
GetTaxMth = 3
Case Is > #7/5/2006# And datStart < #8/6/2006#
GetTaxMth = 4
Case Is > #8/5/2006# And datStart < #9/6/2006#
GetTaxMth = 5
Case Is > #9/5/2006# And datStart < #10/6/2006#
GetTaxMth = 6
Case Is > #10/5/2006# And datStart < #11/6/2006#
GetTaxMth = 7
Case Is > #11/5/2006# And datStart < #12/6/2006#
GetTaxMth = 8
Case Is > #12/5/2006# And datStart < #1/6/2007#
GetTaxMth = 9
Case Is > #1/5/2007# And datStart < #2/6/2007#
GetTaxMth = 10
Case Is > #2/5/2007# And datStart < #3/6/2007#
GetTaxMth = 11
Case Is > #3/5/2007# And datStart < #4/6/2007#
GetTaxMth = 12
End Select
End Function
 
G

Guest

thanks - it seems to work

Please could you explain what it is doing?

KARL DEWEY said:
Try this ---
Month(DateAdd("d",-5,DateAdd("m",-3,[datStart])))

--
KARL DEWEY
Build a little - Test a little


AccessNewbie said:
Office 2007

I want to be able to get the Tax Month from a given pay date.

Tax months run from 6 April to 5th May, 6th May to 5th June etc

If the payment date is 19th April the Tax Month = 1
If the payment date is 4th July the Tax Month = 3 etc

I have a the following function that is called from the query (Date are UK
dd/mm/yyyy) but whatever the date is it always gives Tax Month 1

What am I doing wrong

Thanks

Public Function GetTaxMth(datStart As Date) As String
Select Case datStart
Case Is > #4/5/2006# And datStart < #5/6/2006#
GetTaxMth = 1
Case Is > #5/5/2006# And datStart < #6/6/2006#
GetTaxMth = 2
Case Is > #6/5/2006# And datStart < #7/6/2006#
GetTaxMth = 3
Case Is > #7/5/2006# And datStart < #8/6/2006#
GetTaxMth = 4
Case Is > #8/5/2006# And datStart < #9/6/2006#
GetTaxMth = 5
Case Is > #9/5/2006# And datStart < #10/6/2006#
GetTaxMth = 6
Case Is > #10/5/2006# And datStart < #11/6/2006#
GetTaxMth = 7
Case Is > #11/5/2006# And datStart < #12/6/2006#
GetTaxMth = 8
Case Is > #12/5/2006# And datStart < #1/6/2007#
GetTaxMth = 9
Case Is > #1/5/2007# And datStart < #2/6/2007#
GetTaxMth = 10
Case Is > #2/5/2007# And datStart < #3/6/2007#
GetTaxMth = 11
Case Is > #3/5/2007# And datStart < #4/6/2007#
GetTaxMth = 12
End Select
End Function
Click to expand...
Click to expand...
 
G

Guest

You said ---Tax months run from 6 April to 5th May, 6th May to 5th June etc
DateAdd("m",-3,[datStart]) subtract 3 months as Tax Year starts in the
4th month
DateAdd("d",-5,DateAdd("m",-3,[datStart])) subtract 5 days as Tax
Month starts on the 6th of the month
Month(DateAdd("d",-5,DateAdd("m",-3,[datStart]))) extraxt the numerial
month

According to how you use the data you might need something different like
this --
Format(DateAdd("d",-5,DateAdd("m",-3,[datStart])),"yyyymm")
This will give you the tax year and two digit month.
--
KARL DEWEY
Build a little - Test a little


AccessNewbie said:
thanks - it seems to work

Please could you explain what it is doing?

KARL DEWEY said:
Try this ---
Month(DateAdd("d",-5,DateAdd("m",-3,[datStart])))

--
KARL DEWEY
Build a little - Test a little


AccessNewbie said:
Office 2007

I want to be able to get the Tax Month from a given pay date.

Tax months run from 6 April to 5th May, 6th May to 5th June etc

If the payment date is 19th April the Tax Month = 1
If the payment date is 4th July the Tax Month = 3 etc

I have a the following function that is called from the query (Date are UK
dd/mm/yyyy) but whatever the date is it always gives Tax Month 1

What am I doing wrong

Thanks

Public Function GetTaxMth(datStart As Date) As String
Select Case datStart
Case Is > #4/5/2006# And datStart < #5/6/2006#
GetTaxMth = 1
Case Is > #5/5/2006# And datStart < #6/6/2006#
GetTaxMth = 2
Case Is > #6/5/2006# And datStart < #7/6/2006#
GetTaxMth = 3
Case Is > #7/5/2006# And datStart < #8/6/2006#
GetTaxMth = 4
Case Is > #8/5/2006# And datStart < #9/6/2006#
GetTaxMth = 5
Case Is > #9/5/2006# And datStart < #10/6/2006#
GetTaxMth = 6
Case Is > #10/5/2006# And datStart < #11/6/2006#
GetTaxMth = 7
Case Is > #11/5/2006# And datStart < #12/6/2006#
GetTaxMth = 8
Case Is > #12/5/2006# And datStart < #1/6/2007#
GetTaxMth = 9
Case Is > #1/5/2007# And datStart < #2/6/2007#
GetTaxMth = 10
Case Is > #2/5/2007# And datStart < #3/6/2007#
GetTaxMth = 11
Case Is > #3/5/2007# And datStart < #4/6/2007#
GetTaxMth = 12
End Select
End Function
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to over-ride US Date Format? 2
adding subsequent Quarters to start of year 3
3 work days prior to 18th 2
WCG Stats Tuesday 05 September 2023 3
WCG Stats Thursday 06 April 2023 2
Query Help 5
WCG Stats Tuesday 04 April 2023 3
WCG Stats Wednesday 05 April 2023 2

Top