How to change the date so that the year will update to the current year

phantom911 · July 24, 2022, 12:37am

SELECT JJ.Account, JJ.DrCr, SUM( JJ.Amount ) FROM JJ, Account WHERE JJ.Account = Account.Account AND JJ.Date >= {d ‘2022-01-01’ } AND ( Account.Type = ‘expense’ OR Account.Type = ‘revenue’ ) GROUP BY JJ.Account, JJ.DrCr ORDER BY JJ.Account ASC, JJ.DrCr DESC

The date is being used as a filter. I only want the stuff in the current year. I would like to replace the “2022-1-1” with the solution. I am in the process of switching from access to libre.

Ratslinger · July 24, 2022, 12:47am

Hello,
.
I understand you are converting from Access to Libre? (s/b Base) but Base is just a front end. It handles many databases. Which database are you using in Base? This matters as SQL varies.
.
What is wrong with the code you have? Did you try it? If error, what exactly is the error?
.
Edit:
.
Quick test using HSQLDB embedded database shows the date comparison you have works. Only problem I see is mixed case table and column names need to be surrounded with quotes such as:

SELECT "JJ"."Account","JJ"."DrCr", SUM( "JJ"."Amount" )  etc......

Make certain the date value is surrounded by apostrophes:

{d '2022-01-01' }

not back ticks.

phantom911 · July 24, 2022, 1:03am

The code runs as is. I believe the sql is in OBDC.
I am looking to make the change so I don’t have to change the filter in future years.
edit: I want to change “2022-1-1” with a formula that gets the 1/1/(Current year).

This is the code in access that does what I want:
SELECT JJ.Account, JJ.DrCr, Sum(JJ.Amount) AS SumOfAmount
FROM JJ INNER JOIN Account ON JJ.Account = Account.Account
WHERE (((Account.Type) Like “revenue*” Or (Account.Type)=“expense”) AND ((JJ.Date)>=DateSerial(Year(Date$()),1,1) Or (JJ.Date)>=DateSerial(Year(Date$()),1,1)))
GROUP BY JJ.Account, JJ.DrCr;

Ratslinger · July 24, 2022, 1:10am

ODBC is a connector for databases. Still need to know what database you are using.
.
You comment provides a better explanation. It st a matter of extracting the year from the current date. Bu again SQL is different from database to database. Need that info.
.
Should be on the bottom line of the Base screen in the center.

phantom911 · July 24, 2022, 1:15am

Its saying Access which . I’m still very new to libreoffice.

Ratslinger · July 24, 2022, 1:21am

So all you have is a connection from Base to Access. Your SQL works because you are still using the access database.
.
If you are switching, you need to create a new Base file using the database you want instead of Access. Then copy From the Base file with Access, the tables and paste in the new Base file - one by one.
.
Only tables will transfer.
.
Queries will need modification depending upon the new database used. Reports and forms will not transfer.
.
BTW, testing with HSQLDB embedded database this works as you requested:

 Year("JJ"."Date") = Year(Current_Date)

phantom911 · July 24, 2022, 1:42am

I kind of figured that was what what happening. I handbuilt a program that inserted items within the access db and deleted them as well. That will be fun. I tried inputing the code you gave me, and the left screen is base. It gave me an error. Ideas?

Ratslinger · July 24, 2022, 1:47am

The code I presented was not for the Access database you are using:

.
If you are going to continue using the Access connection, use the Access SQL.

Ratslinger · July 24, 2022, 2:08am

Found an old Base file with an Access connection. This is may be what you need using Access in Base:

Year(JJ.Date) = Year(Now())

phantom911 · July 24, 2022, 5:02am

Figured it out. Basicly the Date$ is replaced by Now

Code I was looking for was:
SELECT JJ.Account, JJ.DrCr, SUM( JJ.Amount ) FROM JJ, Account WHERE JJ.Account = Account.Account AND JJ.Date >= DateSerial(Year(Now()),1,1) AND ( Account.Type = ‘expense’ OR Account.Type = ‘revenue’ ) GROUP BY JJ.Account, JJ.DrCr ORDER BY JJ.Account ASC, JJ.DrCr DESC