UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Control Source Formula, Access 2016    
 
   
cielo
post Jan 19 2020, 10:49 AM
Post#1



Posts: 991
Joined: 5-February 02
From: Lytham St Annes, Lancashire, UK


Hi,

I have an unbound control on a form that I use to display the tax year (UK-HMRC) that applies to an expense. This is what I have currently:

="6/4/" & [text79] & " to " & "5/4/" & [text79]+1

The tax year is from 6/4/any given year to 5/4/any given year in the format (mm/dd/yyyy)

My issue is that using the above formula an expense in 2020 BEFORE '5/4/2020' results in 2021 due to the '+1' in the formula.

In other words the following date 'Thu 02/01/2020' should be tax year '6/4/2019 to 5/4/2020' but because of the '+1' I get '6/4/2019 to 5/4/2021' which is incorrect of course.

Hope I have explained this in a way that makes sense :-)

Thanks

Alan
Go to the top of the page
 
theDBguy
post Jan 19 2020, 11:16 AM
Post#2


UA Moderator
Posts: 77,520
Joined: 19-June 07
From: SunnySandyEggo


Hi Alan. Are you saying [text79] contains the date 5/4/2020?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
cielo
post Jan 19 2020, 11:45 AM
Post#3



Posts: 991
Joined: 5-February 02
From: Lytham St Annes, Lancashire, UK


Hi,

No. It can be any date. Actually the control source of Text79 is: =DatePart("yyyy",[flddate])'. I have used small steps so to speak to try and work a formula out. So I extracted only the year for Text79 which then gets used to build the control source for the tax year calculation for txtTaxYear control of ="6/4/" & [text79] & " to " & "5/4/" & [text79]+1

What I am trying to do is show in which tax year a particular expense occurs. A UK tax year is 6/4/2019 to 5/4/2020 for example. My formula of course bombs on a date that occurs for example for any expense occurring after the 31st December 2019 up to 5/4/2020 because it adds '1' to 2020 which it should not.

I need to create a formula that uses 'between' I guess which I have tried but have so far failed. Formula would be between 6/4/2019 and 5/4/2021 inclusive for example. Issue is how do I work around the period 1/12020 and 5/4/2020 which does NOT fall in 2021 but 2020.

Hope that helps.

Cheers
Go to the top of the page
 
GroverParkGeorge
post Jan 19 2020, 12:00 PM
Post#4


UA Admin
Posts: 36,778
Joined: 20-June 02
From: Newcastle, WA


This is a variation of the "current age" problem that most often manifests itself when trying to calculate a person's age. Because birthdates fall on any one of 365 (or 366) different days within a year, a person's "age" depends on whether you are asking "as of" a date before their birthdate or after it. In other words, if your birthday is February 1st, you are one age on January 31st and a year older on February 1st.

Same thing here. You have to account for that factor in your calculation.

In this case, the date that the change occurs is the date of the new tax year, or April 6th, not January 1st. So, just as with a person's age, the change doesn't occur until that actual anniversary date.

One way to address the current age problem is to use a formula that accounts for this factor.

You need to know whether to add the 1, in other words, depending on whether the date in question is before or after the anniversary date. So, I would use an Immediate If Iif() calculation on the value of flddate to see whether you need to add the 1 or not.
This post has been edited by GroverParkGeorge: Jan 19 2020, 12:02 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Jan 19 2020, 12:03 PM
Post#5


UA Admin
Posts: 36,778
Joined: 20-June 02
From: Newcastle, WA


I'd be tempted not to do this in the data source for the control, but in a VBA function.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
cielo
post Jan 19 2020, 12:21 PM
Post#6



Posts: 991
Joined: 5-February 02
From: Lytham St Annes, Lancashire, UK


Thanks for feedback. Because I have not worked with access for over 10 years at least I am super rusty. So would it be too much to ask how I go about this whether in VBA or as a formula in a control source.

Would really appreciate the help to get me back into figuring this stuff out.....it does not come easy to me at all.

Cheers
Go to the top of the page
 
RJD
post Jan 19 2020, 01:10 PM
Post#7


UtterAccess VIP
Posts: 10,218
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but see if this demo helps solve this for you. It is all contained in a query and displayed on a form. You can enter a date in the form and see if it gives you the results you need. Several dates are already present in the form (underlying table) as examples.

I usually mess up the Euro dates, so look at this closely to make sure it is correct. And check the date boundary closely as well.

I like George's idea of using a function, but in this demo the calculation is included in the query ...

Year([flddate])-(IIf(Format([flddate],"mmdd")<"0406",1,0)) AS text79

This emulates what you would do in your own form/textbox.

See if this guides you in the right direction.

HTH
Joe
Attached File(s)
Attached File  ControlSourceFormula.zip ( 25.05K )Number of downloads: 3
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
cielo
post Jan 19 2020, 01:22 PM
Post#8



Posts: 991
Joined: 5-February 02
From: Lytham St Annes, Lancashire, UK


Thank you! Will work on it!
Go to the top of the page
 
RJD
post Jan 19 2020, 01:59 PM
Post#9


UtterAccess VIP
Posts: 10,218
Joined: 25-October 10
From: Gulf South USA


Let us know if we can be of further assistance ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
projecttoday
post Jan 19 2020, 07:15 PM
Post#10


UtterAccess VIP
Posts: 11,604
Joined: 10-February 04
From: South Charleston, WV


Can you clarify?
Text79 is: =DatePart("yyyy",[flddate])

If flddate is a date in 2019 then
"6/4/" & [text79] & " to " & "5/4/" & [text79]+1
should get you 6/4/2019 to 5/4/2020.

If flddate is a date in 2020 then
"6/4/" & [text79]-1 & " to " & "5/4/" & [text79]
should get you 6/4/2019 to 5/4/2020.

--------------------
Robert Crouser
Go to the top of the page
 
RJD
post Jan 19 2020, 07:29 PM
Post#11


UtterAccess VIP
Posts: 10,218
Joined: 25-October 10
From: Gulf South USA


Hi Robert: I think what the OP is saying is that if the flddate is in 2019 but the actual date is before April 6, then the tax year is 2018-2019, but after April 5, then the tax year is 2019-2020. So it is "the age calculation thing" that has to be used to get the appropriate tax year span in the UK, based on the OP's description.

At least, that's what I was working with ... and thus my adjustment calculation ...

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
projecttoday
post Jan 19 2020, 07:56 PM
Post#12


UtterAccess VIP
Posts: 11,604
Joined: 10-February 04
From: South Charleston, WV


So the problem is not with the formula itself but rather with flddate? As George noted, an Iif should take care of this.

--------------------
Robert Crouser
Go to the top of the page
 
RJD
post Jan 19 2020, 08:31 PM
Post#13


UtterAccess VIP
Posts: 10,218
Joined: 25-October 10
From: Gulf South USA


Correct. As I posted above ...

Year([flddate])-(IIf(Format([flddate],"mmdd")<"0406",1,0)) AS text79

... or in the textbox text79 ...

=Year([flddate])-(IIf(Format([flddate],"mmdd")<"0406",1,0))

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
theDBguy
post Jan 20 2020, 12:34 AM
Post#14


UA Moderator
Posts: 77,520
Joined: 19-June 07
From: SunnySandyEggo


Hi Alan. Just curious, what tax year would the dates like 5/30/2020 or 6/1/2020 would be?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
projecttoday
post Jan 20 2020, 08:08 AM
Post#15


UtterAccess VIP
Posts: 11,604
Joined: 10-February 04
From: South Charleston, WV


Also, "text79" not a good name.

--------------------
Robert Crouser
Go to the top of the page
 
cielo
post Jan 21 2020, 02:36 PM
Post#16



Posts: 991
Joined: 5-February 02
From: Lytham St Annes, Lancashire, UK


Hi,

Agreed. It's currently just a temp control echoing another control fldDate.
Go to the top of the page
 
cielo
post Jan 21 2020, 02:40 PM
Post#17



Posts: 991
Joined: 5-February 02
From: Lytham St Annes, Lancashire, UK


An HMRC tax year is from 6/4/2018 to 5/4/2019 for example.
Go to the top of the page
 
cielo
post Jan 21 2020, 02:41 PM
Post#18



Posts: 991
Joined: 5-February 02
From: Lytham St Annes, Lancashire, UK


Thanks for all the help guys. I'll get working on it and get back to you!!
Go to the top of the page
 
cielo
post Jan 21 2020, 02:43 PM
Post#19



Posts: 991
Joined: 5-February 02
From: Lytham St Annes, Lancashire, UK


Yes by adding the '-1' then the first year would decrease by one from that entered.

Go to the top of the page
 
cielo
post Jan 21 2020, 02:51 PM
Post#20



Posts: 991
Joined: 5-February 02
From: Lytham St Annes, Lancashire, UK


Hi,

The current tax year here in the UK is 2018 - 2019. Any 'for work purchase' I make in that tax year which is from 6/4/2018 to 5/4/2019 falls in the current tax year (2018 - 2019). A purchase on for example 7/4/2019 would fall in the year 2019 - 2020.

My issue is this.

I have a control on the form which I want to use to reflect the tax year my new 'expense' record is in. So I used the formula in my original post. Of course it does not work because I '+1' for the second yyyy part. That wont work because expenses between 1/1/2019 and 5/4/2019 will have '1' added to the year making them 2019 to 2020 expenses which they are not.

So I will try out the suggested formula and see if it works.

Thanks and I hope the above explains the issue better than what I have previously posted.
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search


RSSSearch   Top   Lo-Fi    24th February 2020 - 03:28 AM