spdragon said:When I export a table from Access 2007 the dates that are exported
come over as both date and time. I have tried to use the resolutions
from the following article and neither has worked
http://support.microsoft.com/kb/291245. I only need the date to show
up.
Dirk Goldgar said:Inspdragon said:When I export a table from Access 2007 the dates that are exported
come over as both date and time. I have tried to use the resolutions
from the following article and neither has worked
http://support.microsoft.com/kb/291245. I only need the date to show
up.
The first method listed in that article, exporting a query that formats
the date field to a text string, is the one that I would have
recommended. In what way did it not work? What exactly did you do, and
how did it come out?
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
I took my table that has the data and created a query from that table. I used Datedone: Format([DateCompleted],"mm/dd/yyyy") for my date. I then exported the query to a text file and set up my fixed width columns creating a date column size of 10 ( using four digit years and leading zeroes in dates). The dates still come out looking like 04/12/2007 0 00 00
spdragon said:I took my table that has the data and created a query from that
table. I used Datedone: Format([DateCompleted],"mm/dd/yyyy") for
my date. I then exported the query to a text file and set up my
fixed width columns creating a date column size of 10 ( using four
digit years and leading zeroes in dates). The dates still come out
looking like 04/12/2007 0 00 00
FROM NapisADLIADLT;Dirk Goldgar said:Inspdragon said:I took my table that has the data and created a query from that
table. I used Datedone: Format([DateCompleted],"mm/dd/yyyy") for
my date. I then exported the query to a text file and set up my
fixed width columns creating a date column size of 10 ( using four
digit years and leading zeroes in dates). The dates still come out
looking like 04/12/2007 0 00 00
That's odd. You're formatting the date in a way that can't return
anything but month, day, and year (with separators). Is it possible the
"0 00 00" comes from the next field in your query? Did you export the
DateCompleted field twice, once formattted to text, once not? I'm
assuming that DateCompleted is really a date/time field, not a text
field that happens to be holding a date/time string. The calculated
field Datedone is not a date field at all, but rather a text field, so
the date attributes shouldn't even apply to it.
Would you care to post the complete SQL of the query you're exporting?
And then, if possible, describe step by step exactly what procedure you
follow to export the query. I can't see how you're getting the result
you report.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
SELECT NapisADLIADLT.CountyCode, NapisADLIADLT.SSN, NapisADLIADLT.Type, NapisADLIADLT.Status, NapisADLIADLT.Met, NapisADLIADLT.Formal, NapisADLIADLT.Informal, Format([DateCompleted],"mm/dd/yyyy") AS Datedone
spdragon said:SELECT NapisADLIADLT.CountyCode, NapisADLIADLT.SSN,
NapisADLIADLT.Type, NapisADLIADLT.Status, NapisADLIADLT.Met,
NapisADLIADLT.Formal, NapisADLIADLT.Informal,
Format([DateCompleted],"mm/dd/yyyy") AS Datedone FROM NapisADLIADLT;
Hi-light query from list. External data tab. Under export click text
file (brings up wizard). Specify the file to save it to. ok. Hit
advanced on the export text wizard. File format - fixed width. Four
digit years and leading zeros in dates are both checked. I have a
saved spec with field sizes. Datedone starts at 19 and has a width of
10 (it is the last field). ok. Next shows lines for field breaks
(does not show time here). Next shows the file it exports. Finish.
Close the wizard. Open file in notepad. Result - 25
AllJea0817091YNN02/08/2007 0 00 00
Dirk Goldgar said:Inspdragon said:SELECT NapisADLIADLT.CountyCode, NapisADLIADLT.SSN,
NapisADLIADLT.Type, NapisADLIADLT.Status, NapisADLIADLT.Met,
NapisADLIADLT.Formal, NapisADLIADLT.Informal,
Format([DateCompleted],"mm/dd/yyyy") AS Datedone FROM NapisADLIADLT;
Nothing wrong there ...
Hi-light query from list. External data tab. Under export click text
file (brings up wizard). Specify the file to save it to. ok. Hit
advanced on the export text wizard. File format - fixed width. Four
digit years and leading zeros in dates are both checked. I have a
saved spec with field sizes. Datedone starts at 19 and has a width of
10 (it is the last field). ok. Next shows lines for field breaks
(does not show time here). Next shows the file it exports. Finish.
Close the wizard. Open file in notepad. Result - 25
AllJea0817091YNN02/08/2007 0 00 00
Huh. Your steps all sound plausible to me, though I'm not using Access
2007. Have you tried it without using the saved export specification,
but making sure you set the field sizes appropriately?
Because I'm not using Access 2007, I can't be sure that this is not a
bug. I'll ask a colleague to test using A2007.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
This morning I tried running the export again but not use the saved specs. It worked. Have since deleted old specs and saved the one that works. Thanks for the help.