Converting Date to Number 1

[databuilt]

Is there a formula function available that will convert a date (say, 4/14/2003) to it's Julian number (37723)? I am using an unput parameter of Date as a condition for a Julian date field. In SQL I can use CAST({?Date} as int). Does Access have an equivalent function?

I am using Access 2000.

 

[scriverb]

Not exactly but you can pick off the Date portion of the date/time field by using the following:
Dim vJulian as long
vJulian = CLng(me.DateControl)

Problem is 04/14/2003 converts to 37725 rather than what you posted. I am not sure of what the difference is here. I wrote a Julian Function converter but it isn't working yet as I get a totally different number. I have to perfect it a little but this should get you close to something you could use.

Bob Scriver

 

[databuilt]

Thank you. I'll try to work with this. I noticed the same discrepency in the conversion. If I format an Excel date as a number I get 37725. If I use the CAST({?DATE} as int) I get 37723. Why??? dunno. Either way I can compensate with a constant.

Where would I put

Dim vJulian as long
vJulian = CLng(me.DateControl)

?? (module?)

I am using the query wizard and adding a parameter Date as the "Criteria" ([Date]).

 

[scriverb]

What I gave you would be if you were converting on a form. You could get the same number in a query with the following as a guide:
Select A.DateField, CLng(A.DateField) as Julian
FROM tblYourTable
WHERE (A.DateField = ([Date Prompt]));

I suggest that you not use the reserve words like Date as a criteria parameter. ACCESS gets confused at times.

Bob Scriver

 

[databuilt]

That did the trick... a little in reverse. I used the following:

PARAMETERS startDate DateTime;

SELECT
PRODDTA_F0101.ABUPMJ
, PRODDTA_F0101.ABAN8
, PRODDTA_F0101.ABALPH

FROM PRODDTA_F0101

WHERE (((PRODDTA_F0101.ABUPMJ)=CLng([startDate])+65379));

The application is for a JD Edwards One World database. The constant is only good for 20003 dates.

Thanks again!

 

[scriverb]

Hey great. Glad to be of assistance.

Bob Scriver