Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-06-2018, 05:20 AM   #1
MushroomKing
Newly Registered User
 
MushroomKing's Avatar
 
Join Date: Jun 2018
Location: The Netherlands
Posts: 88
Thanks: 55
Thanked 1 Time in 1 Post
MushroomKing is on a distinguished road
Exporting date fields to excel, but not received as a date field.

Hi guys!

I got a little issue with exporting.
When i export to an xls file, and one of the fields is a date field, excel will not format this as a date field.

Any ideas why or how i can solve it?

Help is much appriciated. Stuck here.

Code:
 
Sub XferData2XL()
 DoCmd.SetWarnings False
Dim sFile As String
Dim xl As Excel.Application
Dim rst As dao.Recordset, rst1 As dao.Recordset
Dim lngLast As Long
'strSQL = "SELECT KPICOLLECTIVE.* From KPICOLLECTIVE"
With CurrentDb.QueryDefs("KPICOLLECTIVE")
    'first parameter
   .Parameters(0) = [Forms]![stats_form]![startdate]
   .Parameters(1) = [Forms]![stats_form]![enddate]
    Set rst = .OpenRecordset
    ''''''rst1.Filter = "RegistrationDate=#" & Format(Me.startdate, "dd/mm/yyyy") & "#"
    ''''''Set rst = rst1.OpenRecordset
End With
sFile = "C:\filetest.xls"
'Set rst = CurrentDb.OpenRecordset(strSQL)
Set xl = CreateObject("excel.application")
With xl
    .Workbooks.Open sFile
    .Range("A1").Select
    '.Selection.End(xlDown).Select      'goto bottom of data
   lngLast = .Range("A" & xl.rows.Count).end(xlUp).Row
    If lngLast = 65536 Then
        MsgBox "Sheet is full"
        GoTo ExitSub
    End If
    Range("A" & lngLast + 1).Select
    '.ActiveCell.Offset(1, 0).Select    'next free row
    .ActiveCell.CopyFromRecordset rst  'paste data
    .ActiveWorkbook.Save
    .Quit
End With
ExitSub:
Set xl = Nothing
Set rst = Nothing
 DoCmd.SetWarnings True
 DoCmd.Close acQuery, "KPICOLLECTIVE"
End Sub

MushroomKing is offline   Reply With Quote
Old 08-06-2018, 06:17 AM   #2
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,217
Thanks: 83
Thanked 457 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Exporting date fields to excel, but not received as a date field.

As a rule, when something isn't working, we need to know exactly what is happening. You don't just tell a doctor that you don't feel well or a mechanic that your car isn't running right and leave it at that; the same applies here.

Are you just getting a number in Excel? Or is it treating it as text? Is your data itself saved in Access, SQL Server, or another database? How SPECIFICALLY is the data saved in that back end? (For example: in SQL Server, Date and DateTime are two completely different data types, and are handled differently by Access.)
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
Old 08-06-2018, 06:28 AM   #3
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,397
Thanks: 436
Thanked 805 Times in 780 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Exporting date fields to excel, but not received as a date field.

My question would be 'are they actually dates'? as I created the same code to help you in another thread and I get both date and times in both the expected columns of my excel sheet when I run the code

Quote:
Originally Posted by Frothingslosh View Post
As a rule, when something isn't working, we need to know exactly what is happening. You don't just tell a doctor that you don't feel well or a mechanic that your car isn't running right and leave it at that; the same applies here.

Are you just getting a number in Excel? Or is it treating it as text? Is your data itself saved in Access, SQL Server, or another database? How SPECIFICALLY is the data saved in that back end? (For example: in SQL Server, Date and DateTime are two completely different data types, and are handled differently by Access.)

__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
MushroomKing (08-06-2018)
Old 08-06-2018, 06:35 AM   #4
MushroomKing
Newly Registered User
 
MushroomKing's Avatar
 
Join Date: Jun 2018
Location: The Netherlands
Posts: 88
Thanks: 55
Thanked 1 Time in 1 Post
MushroomKing is on a distinguished road
Re: Exporting date fields to excel, but not received as a date field.

Hey Gasman! Thanks again.

Well, it displays it right in excel. It looks like a date.
But when i check the format it's "general".

The query that is being exported, takes the date from a form field.
In the query i formatted the field as dd/mm/yyyy
But it does not seem to export as a date format.

In my query i have

Date: ([Forms]![stats_form]![startdate])

They need it like that for further processing.
MushroomKing is offline   Reply With Quote
Old 08-06-2018, 06:51 AM   #5
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,050
Thanks: 20
Thanked 382 Times in 375 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Exporting date fields to excel, but not received as a date field.

What type of variable is the "Date" stored in for the table the the query is based on? Is it a Date/Time?
Mark_ is online now   Reply With Quote
Old 08-06-2018, 06:56 AM   #6
MushroomKing
Newly Registered User
 
MushroomKing's Avatar
 
Join Date: Jun 2018
Location: The Netherlands
Posts: 88
Thanks: 55
Thanked 1 Time in 1 Post
MushroomKing is on a distinguished road
Re: Exporting date fields to excel, but not received as a date field.

Hi Mark,

Its not from a table. The query takes the date from the form field.

Date: ([Forms]![stats_form]![startdate])
MushroomKing is offline   Reply With Quote
Old 08-06-2018, 07:05 AM   #7
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,050
Thanks: 20
Thanked 382 Times in 375 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Exporting date fields to excel, but not received as a date field.

It looks like what SHOULD be exported is RegistrationDate, correct? The only place you are referencing StartDate is in your code to set a filter.

I am just trying to work through what exactly you are putting into the field that is not coming across as a "Date".

Mark_ is online now   Reply With Quote
The Following User Says Thank You to Mark_ For This Useful Post:
MushroomKing (08-07-2018)
Old 08-06-2018, 07:08 AM   #8
Gasman
Enthusiastic Amateur
 
Gasman's Avatar
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 4,397
Thanks: 436
Thanked 805 Times in 780 Posts
Gasman is a jewel in the rough Gasman is a jewel in the rough Gasman is a jewel in the rough
Re: Exporting date fields to excel, but not received as a date field.

If you apply a format to it, it becomes a string.?
Try removing the format.
My table has a Date/Time fields and shows as Date format in Excel.

If you have to format it at all I would do it in Excel after the transfer.
A simple record macro will give you the code for that.


HTH

Quote:
Originally Posted by MushroomKing View Post
Hey Gasman! Thanks again.

Well, it displays it right in excel. It looks like a date.
But when i check the format it's "general".

The query that is being exported, takes the date from a form field.
In the query i formatted the field as dd/mm/yyyy
But it does not seem to export as a date format.

In my query i have

Date: ([Forms]![stats_form]![startdate])

They need it like that for further processing.
__________________
Access novice. Sometimes trying to give something back.
Access 2007

Please, please use code tag # when posting code snippets

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
MushroomKing (08-07-2018)
Old 08-06-2018, 08:27 AM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,581
Thanks: 92
Thanked 1,682 Times in 1,560 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Exporting date fields to excel, but not received as a date field.

Here is the issue with Excel.

The contents of a cell are closer to what Access calls a variant data type. Any cell. ALL cells. It is true that Excel has explicit data types such as LONG, BOOLEAN, DATE, STRING, etc. - but those are in VBA context, not in the cells of the worksheets.

Note also that you can "anchor" something to a cell, such as a chart or graphic or OLE object, but that is treated like a cell attachment, not an actual value. The value of a cell is cell-address.Value but a drawing object is in a different property of the same cell - AND they can co-exist.

Like Access controls that display values, it is sometimes possible to get the .Value of the control OR to get the .Text of a control - and they can be different, since .Value can be numeric but .Text is ... well, text.

The format-related properties of a cell will define how that variant is displayed but not what is actually stored. "General" format is actually the instruction to Excel to "make your best format guess." When you use the "format cells" option, you are supplying format information only. The cell values DON'T have to exactly match the format. (E.g. a number with multiple decimal places can be displayed as Currency, limiting itself to two decimal places. But if you revert that cell to General, the extra places come back (i.e. were never lost.)

Therefore, I would worry less about seeing format "General" on a column or in a cell as long as it displays correctly.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
MushroomKing (08-07-2018)
Old 08-06-2018, 08:42 AM   #10
Frothingslosh
Premier Pale Stale Ale
 
Frothingslosh's Avatar
 
Join Date: Oct 2012
Location: Flint, Michigan, USA
Posts: 3,217
Thanks: 83
Thanked 457 Times in 413 Posts
Frothingslosh will become famous soon enough Frothingslosh will become famous soon enough
Re: Exporting date fields to excel, but not received as a date field.

Yeah, Doc nailed it. There's nothing wrong with your export - the 'General' formatting option is Excel's default (open a new workbook and check if you don't believe me), and it's literally just instructions to let Excel display it in the format it thinks is best.

Like he said, as long is it displays fine, leave it be. If you need a SPECIFIC date format, then that can be done in your VBA code by setting the Format property of the specific columns, but in this case I think it's largely a waste of time, since you can do the same thing by hand after you create the worksheet in just a few seconds.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Frothingslosh is offline   Reply With Quote
The Following User Says Thank You to Frothingslosh For This Useful Post:
MushroomKing (08-07-2018)
Reply

Tags
date , excel file , export , field , formatting

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Validation of date field based on another table date fields. xhal99104 Theory and practice of database design 3 09-29-2014 05:06 AM
[SOLVED] Date Field to AutoPopulate other date fields to futrue date demicay2669 Modules & VBA 4 10-29-2013 09:32 AM
Update date in three different fields based on date in another field spudracer Modules & VBA 6 03-01-2010 12:03 PM
Total Qty Received <#DAte# Bert666 Reports 2 01-10-2003 08:05 AM
[SOLVED] Problem exporting DATE fields from Access to Excel SMG Queries 2 05-09-2002 10:35 AM




All times are GMT -8. The time now is 02:30 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World