Solved

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

Posted on 2006-06-23
20
363 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
  • 6
  • 4
  • 4
  • +3
20 Comments
 
LVL 65

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 119

Expert Comment

by:Rey Obrero
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
 

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 65

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 119

Expert Comment

by:Rey Obrero
ID: 16972215
jim, rocki
have a nice weekend...
0
 
LVL 65

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 65

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

911 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now