Converting an Integer into a Date

[John]

Dear All

I am using Access 97 to connect to our database package we are using called Helpdesk or Touchpaper. Now it outputs the date as an integer. Below is the formula it uses to convert this integer into a date field for its seperate reporting package:

NumberVar Yyyy
NumberVar Mm
NumberVar Dd
Yyyy:=Truncate ({DATE FIELD AS AN INTEGER}/65536)
Mm:=Truncate(({DATE FIELD AS AN INTEGER}-(Yyyy*65536))/256)
Dd:={DATE FIELD AS AN INTEGER}-(Yyyy*65536)-(Mm*256)
Date(Yyyy,Mm,Dd

Now I want to report from our database straight into access however I need this date field. Would anyone know the formula to convert the integer into a date field i.,e dd/mm/yyyy

Many Thank

John

 

[John]

To clarify something the DATE FIELD AS AN INTEGER is the name of the field I want to convert into dd/mm/yyyy format.

 

[Wayne Morgan]

I don't fully follow what you are trying to do, but I suspect there is an
easier way.

Let's start with how Access/VBA stores dates. The base date/time is
midnight, 30 December 1899. When you see a date in Access, it is stored as
the number of days since this date. The integer portion is the date and the
decimal portion is the fraction of a day (i.e. time of day), so 6:00 AM on
24 May 2004 would be 38131.25. To test this, go to the immediate window
(Ctrl+G) and type

?CDate(38131.25)

then press Enter and see what is returned.

Now, for some of what you are trying to do, there are some built in
functions that should be able to do this for you. First, you could simply
use Format to format the date as desired (year only, year and month, date
only, etc). The Format function will return a string data type but the value
will be a number.

You can also use functions such as (assuming today's date 5/24/2004)
Year(Date()) to get 2004
Month(Date()) to get 5
Day(Date()) to get 24

To get back to a date, you can use the DateSerial function, DateSerial(2004,
5, 24) will give 5/24/2004. You can also use expressions in the function,
DateSerial(2004, 5, 24-1) will give 5/23/2004.

Some other functions you may want to check out in the help file are DateAdd,
DateDiff, Weekday, WeekdayName, and Now. To refer to dates, you use a
delimiter character (#), just as you use a (") to delimit strings. Using the
examples above, if I typed in the date instead of using Date() I would need
the delimiter.

Year(#5/24/2004#) to get 2004. If I didn't use the delimiter, Access would
try to divide 5 by 24 then by 2004 and use that number to get the Year
value. As you can see from the initial discussion, dates are actually stored
as decimal numbers so the year that is representative of
1.0395874916833000665335994677312e-4 (the value you get from the division
problem) is the year that will be returned. Since this value is almost zero,
the year that will be return is 1899. You can specify the full date and time
inside the delimiters (ex. #5/24/2004 6:00 AM#). Also, in queries, the date
should be formatted in US format (mm/dd/yyyy), so you may find using the
Format function the easies thing to do if you need to reformat the date
anyway.

For more information on this, try this link. You will find various examples
of date manipulations already created.
http://www.mvps.org/access/datetime/index.html


--
Wayne Morgan
MS Access MVP

Dear All,

I am using Access 97 to connect to our database package we are using

called Helpdesk or Touchpaper. Now it outputs the date as an integer. Below
is the formula it uses to convert this integer into a date field for its
seperate reporting package:

NumberVar Yyyy;
NumberVar Mm;
NumberVar Dd;
Yyyy:=Truncate ({DATE FIELD AS AN INTEGER}/65536);
Mm:=Truncate(({DATE FIELD AS AN INTEGER}-(Yyyy*65536))/256);
Dd:={DATE FIELD AS AN INTEGER}-(Yyyy*65536)-(Mm*256);
Date(Yyyy,Mm,Dd)

Now I want to report from our database straight into access however I need

this date field. Would anyone know the formula to convert the integer into a
date field i.,e dd/mm/yyyy ?

 

[John Vinson]

Dear All,

I am using Access 97 to connect to our database package we are using called Helpdesk or Touchpaper. Now it outputs the date as an integer. Below is the formula it uses to convert this integer into a date field for its seperate reporting package:

NumberVar Yyyy;
NumberVar Mm;
NumberVar Dd;
Yyyy:=Truncate ({DATE FIELD AS AN INTEGER}/65536);
Mm:=Truncate(({DATE FIELD AS AN INTEGER}-(Yyyy*65536))/256);
Dd:={DATE FIELD AS AN INTEGER}-(Yyyy*65536)-(Mm*256);
Date(Yyyy,Mm,Dd)

Now I want to report from our database straight into access however I need this date field. Would anyone know the formula to convert the integer into a date field i.,e dd/mm/yyyy ?

Try:

DateSerial([DFAAI] \ 65536, [DFAAI] \ 256 MOD 256, [DFAAI] MOD 256)

\ is an Integer Divide operator, MOD the arithmetic modulo function
(remainder).