Solved

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

Posted on 2006-06-23
20
372 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
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…
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…
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 …

777 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