Dynamic table, based on date varable

[wonko]

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
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)

 

[vba_php]

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:

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]

Many thanks, apologies for the length of the post, I seemed to want to convey a lot of information there.

Tried the:

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

 

[MajP]

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]

Many thanks, apologies for the length of the post, I seemed to want to convey a lot of information there.

Tried the:

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]

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 showthread.php?t=232845 (not allowed to use the link feature as posts <10 )
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]

2. Okay - don't read this thread 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]

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) House is in the UK

 

[wonko]

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:

"05/04/" & IIf([PaymentDate]>CDate("5/4/" & Year([PaymentDate])),Year([PaymentDate])+1,Year([PaymentDate]))

solves the problem 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.

 

[vba_php]

thanks for providing the answer wonk. mark the thread solved if you have time.