Access Date Help!

[saladinator]

I have created an Excel spreadsheet that has a lot of data. What I
want to do is import the spreedsheet to Access and create a form so
that I can print each row per page in a proffessional manner. The
problem is that whenever I import the data to access my dates show up
in 38478 instead of 05/06/05. How can I convert this number back to
the date format in access?

[fredg]

On Wed, 06 Jun 2007 08:42:19 -0700, sa*********@gma il.com wrote:

I have created an Excel spreadsheet that has a lot of data. What I
want to do is import the spreedsheet to Access and create a form so
that I can print each row per page in a proffessional manner. The
problem is that whenever I import the data to access my dates show up
in 38478 instead of 05/06/05. How can I convert this number back to
the date format in access?

That value of 38478 is a date value (it's 5/6/2005).
Access stores dates a Double datatype number, being a count of days
from 12/30/1899.

All you need do is set the format property of the field/control to
m/d/yyyy
to display the value as 5/6/2005.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[saladinator]

On Jun 6, 9:57 am, fredg <fgutk...@examp le.invalidwrote :

On Wed, 06 Jun 2007 08:42:19 -0700, saladina...@gma il.com wrote:

I have created an Excel spreadsheet that has a lot of data. What I
want to do is import the spreedsheet to Access and create a form so
that I can print each row per page in a proffessional manner. The
problem is that whenever I import the data to access my dates show up
in 38478 instead of 05/06/05. How can I convert this number back to
the date format in access?

That value of 38478 is a date value (it's 5/6/2005).
Access stores dates a Double datatype number, being a count of days
from 12/30/1899.

All you need do is set the format property of the field/control to
m/d/yyyy
to display the value as 5/6/2005.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Fred thanks for your help. I appologize but my Access knowledge is
just now blossoming. Where do I go to do what you told me? How do I
set the format property? Thanks so much for your help.

[fredg]

On Wed, 06 Jun 2007 09:09:14 -0700, sa*********@gma il.com wrote:

On Jun 6, 9:57 am, fredg <fgutk...@examp le.invalidwrote :

>On Wed, 06 Jun 2007 08:42:19 -0700, saladina...@gma il.com wrote:

>>I have created an Excel spreadsheet that has a lot of data. What I
want to do is import the spreedsheet to Access and create a form so
that I can print each row per page in a proffessional manner. The
problem is that whenever I import the data to access my dates show up
in 38478 instead of 05/06/05. How can I convert this number back to
the date format in access?

That value of 38478 is a date value (it's 5/6/2005).
Access stores dates a Double datatype number, being a count of days
from 12/30/1899.

All you need do is set the format property of the field/control to
m/d/yyyy
to display the value as 5/6/2005.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Fred thanks for your help. I appologize but my Access knowledge is
just now blossoming. Where do I go to do what you told me? How do I
set the format property? Thanks so much for your help.

In your Table?
Open the Table in Table Design View.
Select the Date field.
In the lower panel is the Format property line.
On that line, write:
m/d/yyyy
Save the changes.

On your form?
Open the Form in Design View.
Select the Date control.
Right-click and select Properties.
On the property sheet Format tab, find the Format line.
Write
m/d/yyyy
on that line.
Save the changes.

That's to show dates in the format of 6/5/2007.
Look up
Format + Format Property - Date/Time Data Type
in Access help to see the various different date formats available.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[saladinator]

On Jun 6, 11:20 am, fredg <fgutk...@examp le.invalidwrote :

On Wed, 06 Jun 2007 09:09:14 -0700, saladina...@gma il.com wrote:

On Jun 6, 9:57 am, fredg <fgutk...@examp le.invalidwrote :

On Wed, 06 Jun 2007 08:42:19 -0700, saladina...@gma il.com wrote:
I have created an Excel spreadsheet that has a lot of data. What I
want to do is import the spreedsheet to Access and create a form so
that I can print each row per page in a proffessional manner. The
problem is that whenever I import the data to access my dates show up
in 38478 instead of 05/06/05. How can I convert this number back to
the date format in access?

That value of 38478 is a date value (it's 5/6/2005).
Access stores dates a Double datatype number, being a count of days
from 12/30/1899.

All you need do is set the format property of the field/control to
m/d/yyyy
to display the value as 5/6/2005.

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Fred thanks for your help. I appologize but my Access knowledge is
just now blossoming. Where do I go to do what you told me? How do I
set the format property? Thanks so much for your help.

In your Table?
Open the Table in Table Design View.
Select the Date field.
In the lower panel is the Format property line.
On that line, write:
m/d/yyyy
Save the changes.

On your form?
Open the Form in Design View.
Select the Date control.
Right-click and select Properties.
On the property sheet Format tab, find the Format line.
Write
m/d/yyyy
on that line.
Save the changes.

That's to show dates in the format of 6/5/2007.
Look up
Format + Format Property - Date/Time Data Type
in Access help to see the various different date formats available.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -

AHH!! Thank you very much sir!
\

[saladinator]

On Jun 6, 11:57 am, saladina...@gma il.com wrote:

On Jun 6, 11:20 am, fredg <fgutk...@examp le.invalidwrote :

On Wed, 06 Jun 2007 09:09:14 -0700, saladina...@gma il.com wrote:

On Jun 6, 9:57 am, fredg <fgutk...@examp le.invalidwrote :
>On Wed, 06 Jun 2007 08:42:19 -0700, saladina...@gma il.com wrote:
>>I have created an Excel spreadsheet that has a lot of data. What I
>>want to do is import the spreedsheet to Access and create a form so
>>that I can print each row per page in a proffessional manner. The
>>problem is that whenever I import the data to access my dates show up
>>in 38478 instead of 05/06/05. How can I convert this number back to
>>the date format in access?

>That value of 38478 is a date value (it's 5/6/2005).
>Access stores dates a Double datatype number, being a count of days
>from 12/30/1899.

>All you need do is set the format property of the field/control to
>m/d/yyyy
>to display the value as 5/6/2005.

>--
>Fred
>Please respond only to this newsgroup.
>I do not reply to personal e-mail

Fred thanks for your help. I appologize but my Access knowledge is
just now blossoming. Where do I go to do what you told me? How do I
set the format property? Thanks so much for your help.

In your Table?
Open the Table in Table Design View.
Select the Date field.
In the lower panel is the Format property line.
On that line, write:
m/d/yyyy
Save the changes.

On your form?
Open the Form in Design View.
Select the Date control.
Right-click and select Properties.
On the property sheet Format tab, find the Format line.
Write
m/d/yyyy
on that line.
Save the changes.

That's to show dates in the format of 6/5/2007.
Look up
Format + Format Property - Date/Time Data Type
in Access help to see the various different date formats available.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail- Hide quoted text -

- Show quoted text -

AHH!! Thank you very much sir!
\- Hide quoted text -

- Show quoted text -

Ok so I did everything that you told me and when I try to save it it
deletes the data instead of converting it. What could be causing this
problem?

[Rich P]

What is happening is that your table is expecting a date value in
dateformat. You need to convert the Excel date number to a date value
before inserting it into the table:

Function ConvertNumericT oDate(s1 as String) As Date
ConvertNumericT oDate = Format(s1, "mm/dd/yyyy")
End Function

Here is a way to call your function:

Sub CallDateFuction ()
Dim str1 As string
str1 = 38478
Debug.Print ConvertNumericT oDate(str1)
End Sub

and this will display 5/6/2005 in the Immediate window.

Now Add this function to a standard code module in Access (not a form
code module)

So When you import your data from Excel you can add a date column to the
Import Table and use an Update Query and apply the function above like
this:

In the Update Query Design View add your import table and then select
your new date field to be updated. In the Update To: cell enter this:

Update To: ConvertNumericT oDate(yourTbl.Y ourNumericDatef ld)

(change yourTbl to the actual name of your import table and
YourNumericDate fld to the actual name of the field that contains the
numeric dates). Then run the query. Now all the numeric date values in
the numeric column will appear as the correct date values in the new
date column (they will still be numeric in the numeric import column).

Rich

*** Sent via Developersdex http://www.developersdex.com ***

[saladinator]

On Jun 6, 4:03 pm, Rich P <[e-mail] mwrote:

What is happening is that your table is expecting a date value in
dateformat. You need to convert the Excel date number to a date value
before inserting it into the table:

Function ConvertNumericT oDate(s1 as String) As Date
ConvertNumericT oDate = Format(s1, "mm/dd/yyyy")
End Function

Here is a way to call your function:

Sub CallDateFuction ()
Dim str1 As string
str1 = 38478
Debug.Print ConvertNumericT oDate(str1)
End Sub

and this will display 5/6/2005 in the Immediate window.

Now Add this function to a standard code module in Access (not a form
code module)

So When you import your data from Excel you can add a date column to the
Import Table and use an Update Query and apply the function above like
this:

In the Update Query Design View add your import table and then select
your new date field to be updated. In the Update To: cell enter this:

Update To: ConvertNumericT oDate(yourTbl.Y ourNumericDatef ld)

(change yourTbl to the actual name of your import table and
YourNumericDate fld to the actual name of the field that contains the
numeric dates). Then run the query. Now all the numeric date values in
the numeric column will appear as the correct date values in the new
date column (they will still be numeric in the numeric import column).

Rich

*** Sent via Developersdexht tp://www.developersd ex.com***

Rich thanks for your help. If you could can you explain to me how and
where I need to call this function. I am still trying to pick all of
this excel and access stuff up.

[Rich P]

Go to the module section. Create a new module and just paste this code
in there and save it - you can save the module as any name.

Function ConvertNumericT oDate(s1 as String) As Date
ConvertNumericT oDate = Format(s1, "mm/dd/yyyy")
End Function

Now go to the table you just imported from Excel. Add a new column and
make it a DateTime type. This column will be empty, but you are going
to update that column with the data from the Numeric Date column from
Excel.

Then go to the Queries section and open a new query in design view. Add
the table you just imported from Excel. Now go to the Query Menu at the
top of the Query Design view. Select "Update Query". Now, from the
Table in the Query Design view - scroll that table till you see your new
Date Column and drag your new Date column to the Field section in the
Query Design view. Place the Column in the cell next to "Field:" This
should automatically add the name of the Table in the "Table:" cell just
below the "Field:" cell.

Now go to the cell next to "Update To:" which is immediately below the
"Table:" cell

Here is where you type this:

ConvertNumericT oDate(theTableN ame.yourDateFie ldName)

replace theTableName with the actual table Name and replace
yourDateFieldNa me with the name of the actual Date field. Make sure you
type the tableName - period - DateFieldName

tblWhatever.Dat eFieldWhatever

Make sure this is inside parentheses after the Function Name

ConvertNumericT oDate(tblWhatev er.DateFieldWha tever)

That is what you type in the "Update To:" cell

Save the query and run it. This will update your new Date Column with
the Date value of the Numeric Dates.

Rich

*** Sent via Developersdex http://www.developersdex.com ***