?
Solved

How to export date from Access without time (need space delimited fields)?

Posted on 2006-06-23
20
Medium Priority
?
469 Views
Last Modified: 2012-08-13
I need to export data from Access with spaces to delimit the fields and quotes (“) as the text qualifier.  I have a problem with the date field. I get date + time.  I only need the date.  How do I get Access to export just the date?  If I convert it to text, then I get quotes around the date (which won't work when I import it into my accounting system).  I am going to file, export, text, delimited. I then choose space for delimited and " for the text qualifier.  I need this to fix payroll for a retro union wage change.
0
Comment
Question by:fredcook
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 4
  • +3
20 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16972138
> I have a problem with the date field. I get date + time.  I only need the date.
In your query you use to export this data, instead of YourColumnName, use Format(YourColumnName, "Short Date")
{Requires a little editing}
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16972155
or simply use

           DateValue([DateField])

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16972182
Are you exporting a table or query? If a table, does it work with changing design Format view

In the import/export spec where u specified text qualifer, u can list the fields below
Have u tried forcing your field for the date to be shorter?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:fredcook
ID: 16972187
I am not using a query to export.  Maybe that is my problem.  The data is in a table.  I am going to file, export, text, delimited. I then choose space for delimited and " for the text qualifier.  How do I export from a query?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16972204
afaik you can't export data from a table, and impliment any special formatting, without using a query that does the format.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16972209
well u just select all the fields

select field1, field2, field3 etc
from table

save and export that, but remember to format the date like Jim has said

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 16972215
jim, rocki
have a nice weekend...
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16972223
>How do I export from a query?
There is no difference between exporting from a table or a query, whether you are doing it manually or in VBA code via DoCmd.TransferText, you will be able to choose among all saved tables and queries as the source of your export.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16972261
You also Rey
I certainly will - wicked weekend of world cup footy
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 16972303
You too
0
 

Author Comment

by:fredcook
ID: 16972352
I guess I am exporting the query wrong because I get results in a box like below.  I am doing this manually.  How do I tell it that it needs to be space delimited and to use quotes as the text delimiter?  I am sorry that I can't figure it out yet.

-------------------------------------------
|        F30         |       Expr1        |
-------------------------------------------
|           6/1/2006 |           6/1/2006 |
-------------------------------------------
|           6/1/2006 |           6/1/2006 |
-------------------------------------------
|           6/1/2006 |           6/1/2006 |
-------------------------------------------
|           6/1/2006 |           6/1/2006 |
-------------------------------------------
|           6/1/2006 |           6/1/2006 |
-------------------------------------------
|           6/1/2006 |           6/1/2006 |
-------------------------------------------
|           6/1/2006 |           6/1/2006 |
-------------------------------------------
|           6/1/2006 |           6/1/2006 |
-------------------------------------------
|           6/1/2006 |           6/1/2006 |
-------------------------------------------
|           6/1/2006 |           6/1/2006 |
-------------------------------------------
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16972388
that is not text format output

what did u do?

u have a query which extracts the fields,
save it

then do as u do table, right click and export

0
 

Author Comment

by:fredcook
ID: 16972470
Here is the SQL:  SELECT negdale.F30, DateValue([f30]) AS Expr1, Format([f30],"Short Date") AS Expr2
FROM negdale;

This is the result in the query:
F30      Expr1      Expr2
6/1/2006      6/1/2006      6/1/2006
6/1/2006      6/1/2006      6/1/2006
6/1/2006      6/1/2006      6/1/2006
6/1/2006      6/1/2006      6/1/2006
6/1/2006      6/1/2006      6/1/2006
6/1/2006      6/1/2006      6/1/2006
6/1/2006      6/1/2006      6/1/2006
6/1/2006      6/1/2006      6/1/2006
6/1/2006      6/1/2006      6/1/2006
6/1/2006      6/1/2006      6/1/2006

This is the result in the export:
6/1/2006 0:00:00 6/1/2006 0:00:00 "6/1/2006"
6/1/2006 0:00:00 6/1/2006 0:00:00 "6/1/2006"
6/1/2006 0:00:00 6/1/2006 0:00:00 "6/1/2006"
6/1/2006 0:00:00 6/1/2006 0:00:00 "6/1/2006"
6/1/2006 0:00:00 6/1/2006 0:00:00 "6/1/2006"
6/1/2006 0:00:00 6/1/2006 0:00:00 "6/1/2006"
6/1/2006 0:00:00 6/1/2006 0:00:00 "6/1/2006"
6/1/2006 0:00:00 6/1/2006 0:00:00 "6/1/2006"
6/1/2006 0:00:00 6/1/2006 0:00:00 "6/1/2006"
6/1/2006 0:00:00 6/1/2006 0:00:00 "6/1/2006"

I need 6/1/2006 without the quotes.  I think I will just have to open in notepad and replace all the instances of 0:00:00 to get it fixed.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16972556
try this

SELECT negdale.F30, dateserial(year([f30]),month([f30]),day([f30])) as somedate
FROM negdale;


0
 

Author Comment

by:fredcook
ID: 16972598
Still get 0:00:00 on both columns.
0
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 672 total points
ID: 16972674
This was discussed here.
http://www.experts-exchange.com/Databases/MS_Access/Q_21879741.html

I felt that you're always on to a hiding to nothing trying to remove it (with various function calls).
You'll always end up with either text delimiters (as the data is text then after formatting) or export a date and you'll get the seconds portion put back in.
I disagreed with the eventually accepted solution - but to each their own. :-)

Can elaborate on what I had in mind if so required.
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 664 total points
ID: 16972722
hmmm

oh well, try this then - do your own export?


Public Sub ExportMyFile()

    Dim iHandle As Integer
    Dim rs As dao.Recordset
   
    iHandle = FreeFile
    Open "C:\myexports\abc.txt" For Output As iHandle
    Set rs = CurrentDb.OpenRecordset("negdale")
    Do While Not rs.EOF = True
        Print #iHandle, Format(rs!DateFld, "Short Date")
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Close iHandle

End Sub



Basically add in your other fields with double quotes where need be e.g.

print #iHandle, chr$(34) & rs!Field1 & chr$(34) & "," & Format(rs!DateFld, "Short Date")



remember to check DAO Object library in your references (Tools/References)
0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 664 total points
ID: 17048498
fredcook, rockiroads

Regarding the output:
-------------------------------------------
|        F30         |       Expr1        |
-------------------------------------------
|           6/1/2006 |           6/1/2006 |
-------------------------------------------
|           6/1/2006 |           6/1/2006 |
-------------------------------------------
|           6/1/2006 |           6/1/2006 |
-------------------------------------------

Seems like you used:
    DoCmd.OutputTo
Instead of:
    DoCmd.TransferText

Hope this helps clears things up a bit
 
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question