Using Julian Dates in Access Query

[51Sqn]

Hi

This is my first post and I'm pretty sure that there is a straightforward answer .... but I can't find it on Google or anywhere else !
I have a table that has Julian Dates (ie Integer number of days since 01/01/1900). The system it relates to posts transactions as the current day minus 1 -
don't ask me why

So users need to be able to query a column (ldg_posting_date which is the Julian Date value) as a single date minus 1 day - except that this will not work in a query directly on the column. Access tells me the query is too complex. I can set up a separate column and input a date in the format dd/mm/yyyy using CDate (I've also tried (CDbl)) but again using this in the criteria for ldg_posting_date results in the same error.

This must have been encountered at some point since Access first landed, so could anyone give me pointer please ?
I've attached some screenshots that give a bit of context, I hope.

 

[Gasman]

Date would need to be mm/dd/yyyy or yyyy-mm-dd format ?
If you enter a dmy date and then inspect the query, you will see Access has swapped it to mdy ?

Dates are held as number anyway in Access.
Try ? cdate(1) in the immediate window and adjust to suit.?

Again in the immediate window

? cdate(45011)
26/03/2023

Use Clng() on your input date and adjust again to suit?

Edit: I would probably have a form that took a user friendly date and converted that to the actual number in access and adjusted as I mentioned then use that as criteria for the query for that Julian date field.

 

[Gasman]

I do not have Julian dates in my DB, so had to make them Julian (well numeric) then convert back.
It does actually take the dd/mm/yyyy input and produce data as I use that format as in the UK.

SELECT TestTransactions.ID, TestTransactions.TransactionDate, CLng([TransactionDate]) AS Expr1, CDate(CLng([TransactionDate])) AS JulianOut
FROM TestTransactions
WHERE (((CDate(CLng([TransactionDate])))=[Enter Date]));

You still need to adjust for your Julian date calculation.

 

[theDBguy]

I'm not sure I understand the question, but couldn't you just use Int() instead?

SELECT ldg_posting_date FROM DateTest WHERE ldg_posting_date=Int([Enter Date])-1

PS. Or maybe you still need CDate()?

SELECT ldg_posting_date FROM DateTest WHERE ldg_posting_date=Int(CDate([Enter Date]))-1

 

[51Sqn]

I'm not sure I understand the question, but couldn't you just use Int() instead?

SELECT ldg_posting_date FROM DateTest WHERE ldg_posting_date=Int([Enter Date])-1

PS. Or maybe you still need CDate()?

SELECT ldg_posting_date FROM DateTest WHERE ldg_posting_date=Int(CDate([Enter Date]))-1

Hi - the first statement results in this :

Your second one works perfectly on test, however when I apply it to the *real* query it falls over with on ODBC call error - I'm using ODBC to link to
an ancient Cobol based system ( something called cTreeSQL !!)

But thanks for trying and for pointing me in the right direction !

 

[The_Doc_Man]

I have a table that has Julian Dates (ie Integer number of days since 01/01/1900). The system it relates to posts transactions as the current day minus 1 -
don't ask me why

Saying "don't ask me why" is actually not needed. Almost every O/S these days uses a somewhat similar method for dates - using a "linear time scale" and thus allowing you to compute "days between two dates" with simple subtraction. Access makes day 1 = Dec 31, 1899. Excel and Widnows both make that same date as day 0. UNIX uses a date in 1970 as its "epoch" date. Open VMS uses a date in November of 1867. The biggest trick therefore is knowing the epoch date for your environment and knowing the epoch date for your data source. Then add or subtract the "epoch offset" as needed BEFORE you convert anything to a more traditional date format.