Date and Time Formatting

[reidn]

I need access to read the date and times on records as an integer(Date) with a decimal(time). Is there anyway to do this?

[RuralGuy]

Perhaps this link will help: http://support.microsoft.com/kb/q130514/en-us

[rpeare]

give an example of the input and what you want the output to be.

[reidn]

Ok, so I have a table that is recording the records with a date/time of format DD/MM/YYY 00:00:00 AM and I need to be able to convert that to a Julian format so that I can organize the Records based on the month. I have thought of another way to do it but I am not entirely sure that it will work and it would take alot more work...

[rpeare]

assume your datefield is called OriginalDate

you can get the julian date like this

DateDiff("d",CDate("1/1/" & DatePart("yyyy",[originaldate])),[originaldate])+1

[reidn]

Thanks guys, the DateDiff worked and I figured it out.

[reidn]

Is there any way to get that datediff to include the year as well? As it is it only computes for the month eg. 1/4/2001=4 instead of some large number

[rpeare]

If you have the julian date you don't need the month to sort them, or is the month purely for a visual thing? You can get the month of any date by

datepart("<time portion>", [FieldName])

i.e. datepart("d",[OriginalDate])

would give you the day, some of your time portions are

d (day), m(month), n(minute), y or yyyy (year)

[reidn]

Its okay I got it to work. Thank you for all of your help, Then database turned out really well!