Solved

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

Posted on 2006-06-23
20
386 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 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 120

Expert Comment

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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