Solved

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

Posted on 2006-06-23
20
433 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 168 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 166 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 166 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

691 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