Dynamic table, based on date varable (1 Viewer)

wonko

New member
Local time
Today, 17:06
Joined
Jan 5, 2020
Messages
6
I'm new here so hope I landed in the right forum.

I've a small Access db which I use to track financial activities for a house I rent out.

here are the table [tRental] fields:

pk ID AutoNumber
*PaymentDate Date/Time * Renamed from Date - Thanks to MajP :eek:
Detail Short Text
GrossIncome Currency
Expenditure Currency
NetPosition Calculate [GrossIncome]-[Expenditure]
Document Attachment

Being in the EU I am formatted to use the dd/mm/yyyy

and to be honest, it does as a db all I want it to. However, I've a couple of challenges.

I've made a basic query that, having entered two dates, returns the sum's of GrossIncome, Expenditure and NetPosition - fine but clunky.

I've two challenges:

Firstly the UK tax year begins 6/4/yyyy and ends 5/4/yyyy+1

from a post hereabouts I (modified) this formula from 2012 which returns the correct tax year ending 5/4/yyyy
Tax Year Ending: IIf([PaymentDate]>CVDate("5/4/" & Year([PaymentDate])),Year([Payment Date])+1,Year([Payment Date]))

However, whist that displays nicely in a query to give me a year, when I add a further column Gross Income : Sum([tRental]![GrossIncome])
it all fall apart with a error message Your query does not include the specified 'Date' as part of an aggregated function - erm help?

OR plagiarised from elsewhere;

Public Function fyear(rDate As Date) As Date

If Month(rDate) < 4 Or _
(Month(rDate) = 4 And _
Day(rDate) < 6) Then
fyear = Year(rDate) - 1
Else
fyear = Year(rDate)
End If
fyear = fyear & fyear + 1

End Function

I made a new query to 'rename' the [PaymentDate] to [rDate] {rDate : [PaymentDate]} and a second column in the query Tax Year : fyear([rDate]) to hopefully invoke the module of the same name fyear) and that promptly falls apart with an error message Unidentified function 'fyear' in expression - Okay - I'm out of my depth here...

So what am I trying to achieve?

an on the fly spreadsheet view which looks to the [tRental]![PaymentDate] decides which tax year it falls within and provide the sum of [Gross Income], [Expenditure], [Net Position] by tax year - like this

Year | Gross Income | Expenditure | Net Position
2019 | £2.50 | £2.00 | £0.50
2020 | £5.00 | £1.50 | £3.50


Second challenge is almost identical except to provide a spreadsheet view this time detailing the rental year, which looks to the same [tRental] table, finds the first (oldest) record with a +ve balance in the GrossIncome field (not £0.00) looks at that records date and adds a year to it. Now that's a year 1/1/2019 to 31/12/2020 not the anniversary yyyy+1 - example not literal year that! could be it's 30/10/2018 to 29/10/2019

I know, I don't want much do I? To be honest, I can live with the: Between [Enter Start Date] And [Enter End Date] query but am keen to learn more and as I say it feels a tad clunky and only returns one year at a time - which yes, I could cut and paste into another table for future reference, as it's most unlikely to change after the end of the year etc... but doesn't seem particularly 'smart'.

Any takers? Buy you a beer if you sort it (and you're passing in the Netherlands)
 
Last edited:

vba_php

Forum Troll
Local time
Today, 11:06
Joined
Oct 6, 2019
Messages
2,884
it all fall apart with a error message Your query does not include the specified 'Date' as part of an aggregated function - erm help?
that's because fields that are not aggregate in nature (e.g. - do not have functions like sum(), ave(), count(), etc... in them) require a GROUP BY clause to be associated with them. I've attempted to do what you're doing multiple times, and sometimes I can get away with a workaround, and sometimes i can't. But more or less, it seems like there's always an easier way to accomplish the goal. I don't have a concrete answer on this one, but i would assume if you put this:
Code:
GROUP BY IIf([Date]>CVDate("5/4/" & Year([Date])),Year([Date])+1,Year([Date]))
at the end of your sql statement when looking at it in *sql view*, it might possibly work. don't quote me though! I did not read through your entire post, as it's quite long. But hopefully this will shed some light on what's possible and what isn't. and by the way, I hate beer. I like alternative alcohol choices.
 

wonko

New member
Local time
Today, 17:06
Joined
Jan 5, 2020
Messages
6
Many thanks, apologies for the length of the post, I seemed to want to convey a lot of information there.

Tried the:
Code:
GROUP BY IIf([PaymentDate]>CVDate("5/4/" & Year([PaymentDate])),Year([PaymentDate])+1,Year([PaymentDate]))
! message: The expression you entered contains invalid syntax.You may have entered an operand without an operator :(



Other beverages are available ;)
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 12:06
Joined
May 21, 2018
Messages
8,463
Couple of things.
1.
Being in the EU I am formatted to use the dd/mm/yyyy

Does not matter how you are formatted, but sql uses the format "mm/dd/yyyy". So if you are using any literals in sql you will need to use that format.

2. Do not use the CVDate function ever, use CDate if needed
3. Use dateserial instead of either above to make a date
CVDate("5/4/" & Year([Date])) should be
dateSerial(Year(somedate),4,5) where 4 is month and 5 is day
4. Do not name fields with spaces or reserved words. "Date" is a reserved word. Give a better name PayDate or DueDate or SomeKindofDate
 

vba_php

Forum Troll
Local time
Today, 11:06
Joined
Oct 6, 2019
Messages
2,884
Many thanks, apologies for the length of the post, I seemed to want to convey a lot of information there.

Tried the:
Code:
GROUP BY IIf([Date]>CVDate("5/4/" & Year([Date])),Year([Date])+1,Year([Date]))
! message: The expression you entered contains invalid syntax.You may have entered an operand without an operator :(

Other beverages are available ;)
I have no idea my friend. more than likely other people will recommend other things here, but if you want me to give it a try, you would probably have to upload a file for review by us because your stuff is fairly complex, at least based on your description of the problem. In my mind, there no other better solution to problems than running test cases on them.
 

wonko

New member
Local time
Today, 17:06
Joined
Jan 5, 2020
Messages
6
vba_php - Thanks for trying :)

MajP -

1. Thanks, in 'trying' to find a solution, I noticed several posts where problems occurred in the result because of the format used - hence my mentioning it but again - thanks :)
2. Okay - don't read this thread :eek: showthread.php?t=232845 (not allowed to use the link feature as posts <10 :confused:)
3. Struggling a little with this - will do some more homework to see if I can make it work - you've probably guessed I'm no date manipulation expert here.
4. Got it - good tip much appreciated.

:)
 

vba_php

Forum Troll
Local time
Today, 11:06
Joined
Oct 6, 2019
Messages
2,884
2. Okay - don't read this thread :eek: showthread.php?t=232845
wonk,

it looks like the guy in that thread was trying to do almost the same thing you are trying to do.
 

wonko

New member
Local time
Today, 17:06
Joined
Jan 5, 2020
Messages
6
Almost - they wanted
to see what transaction has happened in which tax year
- that I took to be a list for any tax year.

Where as I want to go one step further and make a table/spreadsheet view to summarise the sum of the three fields - to, well, fill in the tax return but also have visibility, by Tax/Rental year situation as opposed to just the calendar year as I do at the moment (which BTW, is very sensibly, the Dutch tax year):D House is in the UK :rolleyes:
 

wonko

New member
Local time
Today, 17:06
Joined
Jan 5, 2020
Messages
6
I have a solution - albeit not the most elegant.

Okay, so after a lot of experimenting, can't use the iif statement in a calculated field in the table etc.

I added two new fields to the table:

TaxYearEnding | Date/Time

RentalYearEnding | Date/Time

I then noticed the problem with the IIF statement was that Access didn't like the fact only the yyyy was returned; as strictly speaking this isn't a date, it is merely the year.

So sneakily making it:
Code:
"05/04/" & IIf([PaymentDate]>CDate("5/4/" & Year([PaymentDate])),Year([PaymentDate])+1,Year([PaymentDate]))

solves the problem :D it is then merely a matter of making an update query to populate the two new fields in the table (modifying the Rental year date accordingly) added that to a little macro that runs after update in the table entry form and Ta-Daaa Two queries to group and summarise the years and amounts and bingo I have precisely what I set out to achieve and I don't need to 'do any maintenance' to the db in the future.

I'll play a little to see if I can get the wee beastie to make it make it totally on the fly as opposed to updating the table but for now, good enough.

Thanks to all for your time and suggestions.
 
Last edited:

vba_php

Forum Troll
Local time
Today, 11:06
Joined
Oct 6, 2019
Messages
2,884
thanks for providing the answer wonk. mark the thread solved if you have time.
 

Users who are viewing this thread

Top Bottom